• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Tag Archives: enterprise only features

find Enterprise-only features in your database

29 Friday Nov 2013

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

Tags

DMV, enterprise edition, Enterprise feature, enterprise license, enterprise only features, sys.dm_db_persisted_sku_features

I have SQL Server that was installed as enterprise edition, we have never utilized the enterprise-only features on this particular server and want to downgrade to Standard edition so that we can make use of this enterprise license in some other server.
There’s an easy way to tell whether the database contains enterprise-only features. In SQL Server 2008 onwards a new DMV sys.dm_db_persisted_sku_features has been added that will report you which enterprise only features are present in a database. Let’s check it out.

Method 1:
You need to run below query on every database to check for enterprise-only features.

SELECT * FROM sys.dm_db_persisted_sku_features

Method 2:
The following query will report all enterprise-only features from all databases.

Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255))
INSERT INTO @EntFeatures
exec sp_msforeachdb 'select "?" AS DatabaseNames,feature_name from [?].sys.dm_db_persisted_sku_features'
SELECT * FROM @EntFeatures

Method 3:
The following query makes use of cursor to achieve the same result as in method 2.

Declare @dbid Int
Declare @STR Varchar(100)
Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255))
Declare rs scroll cursor for
Select database_id From sys.databases
open rs
fetch first from rs into @dbid
while @@fetch_status= 0
Begin
Select @STR = 'use ' + db_name (@dbid) +
CHAR(13) + ' SELECT db_name(),feature_name FROM sys.dm_db_persisted_sku_features'
Insert Into @EntFeatures
EXEC ( @STR )
fetch next from rs into @dbid 
End 
Close rs 
Deallocate rs 
Select * from @EntFeatures

sys.dm_db_persisted_sku_features DMV only reports below four features.

  • Data compression
  • Partitioning
  • Transparent data encryption
  • Change data capture

As per MSDN Library even Database Snapshot & Online Indexing are enterprise-only features , if you have enabled these features in your database it will not be reported by this DMV.
To make sure I created a database snapshot and ran this DMV but it did not report database snapshot as enterprise-only feature same is with online indexing which I enabled for one of my maintenance plans but DMV again missed out this as well.

Advertisement

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • sqlerrors
    • Already have a WordPress.com account? Log in now.
    • sqlerrors
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
%d bloggers like this: