Database Option Queries

Here are a few queries that I use regularly to check database options on a server:

Compatibility (100 = 2008, 90 = 2005, 80 = 2000):

select name, compatibility_level 
from sys.databases 
where compatibility_level != 100 -- or 90, or 80 as appropriate

Recovery Model:

select name, recovery_model_desc 
from sys.databases

Statistics, auto shrink & auto close:

SELECT [name]
	,state_desc
	,is_auto_close_on
	, is_auto_shrink_on,state_desc
	,is_auto_create_stats_on
	,is_auto_update_stats_on
FROM master.sys.databases
WHERE  (is_auto_close_on = 1)
OR (is_auto_shrink_on = 1)
OR (state_desc = 'OFFLINE')
OR (is_auto_create_stats_on = 0)
OR (is_auto_update_stats_on = 0)

Page verification:

SELECT [name], page_verify_option_desc
FROM master.sys.databases
WHERE (state_desc = 'ONLINE' AND page_verify_option_desc <> 'CHECKSUM')

Owners:

SELECT suser_sname(owner_sid),name,database_id FROM master.sys.databases

Autogrowth values:

select DB_NAME(database_id)
	, growth_MB = 
		case is_percent_growth
			when 1 then STR(growth)
			when 0 then LTRIM(STR(growth * 8.0 / 1024,10,1))
		end
	, growth_type = 
		case is_percent_growth
			when 1 then 'Percent growth'
			when 0 then 'MB'
		end
	,size_limit = 
	CASE max_size
        WHEN -1 THEN 'unrestricted growth'
        ELSE 'restricted growth to ' + 
            LTRIM(STR(max_size * 8.0 / 1024,10,1)) + ' MB' 
    END
	,physical_name 
 from sys.master_files;

Tweak and filter to your heart’s content and feel free to share any of your favourites in the comments…

Advertisements
This entry was posted in Database options, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s