Menu
–By Lori Brown
I am going to start up a thread with some queries that I consider to be important to DBA’s. As a consultant, many times we have to start managing SQL instances without any knowledge of why it has been configured in various ways. We usually try to work with our clients to figure things out but many times even they don’t know what has been done to the SQL configuration and database options or features. This can happen when a software vendor is allowed to set up the SQL instance or when a developer or DBA enables certain things so that work can be done. I have some scripts that I keep in my Toolkit and can use when I need to better understand the environment. At least armed with a little knowledge, I can ask questions and help my clients better understand things.
Here is a query that I use to find out what database options have been set as well as what advanced features maybe enabled. I tried to make the output display in plain English so that if I have to pass the info on to a client, they can understand it as well. Please note that at the end of each column, I have put a comment indicating if the feature is version specific. If you don’t have that version of SQL, simply comment out the lines that don’t apply to you. As new versions of SQL come out, I’ll try to test and keep things in the Toolkit up to date.
— get a list of interesting database settings
SELECT d.name AS DBName
,(CASE d.compatibility_level WHEN 80 THEN ‘SQL 2000’
WHEN 90 THEN ‘SQL 2005’
WHEN 100 THEN ‘SQL 2008’
WHEN 110 THEN ‘SQL 2012’
WHEN 120 THEN ‘SQL 2014’
WHEN 130 THEN ‘SQL 2016’ END) AS Compatibility
,d.user_access_desc AS UserAccessSetting
,(CASE d.is_auto_close_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoCloseSetting
,(CASE d.is_auto_shrink_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoShrinkSetting
,d.recovery_model_desc AS RecoveryModel
,d.page_verify_option_desc AS PageVerifySetting
,(CASE d.is_auto_create_stats_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoCreateStats
,(CASE d.is_auto_update_stats_on WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS AutoUpdateStats
,(CASE d.is_cdc_enabled WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS ChangeDataCaptureSetting
,(CASE d.is_encrypted WHEN 0 THEN ‘OFF’ ELSE ‘ON’ END) AS TDESetting
— Always On info
,ag.name AS AGName
,rs.role_desc AS AGRole
,UPPER(ag.automated_backup_preference_desc) AS AGBkupPref
— Other interesting stuff
,d.containment_desc AS ContainmentSetting — SQL 2012 +
,d.delayed_durability_desc AS DelDurabilitySetting — SQL 2014 +
,(CASE d.is_remote_data_archive_enabled WHEN 0 THEN ‘Stretch-disabled’ ELSE ‘Stretch-disabled’ END) AS StretchDBSetting — SQL 2016 +
,(CASE d.is_query_store_on WHEN 0 THEN ‘Disabled’ ELSE ‘Enabled’ END) AS QueryStoreSetting — SQL 2016 +
FROM sys.databases d
LEFT JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
LEFT JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!