SQL Server – Database – File Growth

Background

Checking file growth settings and patterns is always good to determine if basic best practices are being followed.

Code


select 

		  [database]	
			= tblSD.[name]

		, [datafileSymbol] 
			= tblMF.[name]

		, [datafileSymbol] 
			= tblMF.[physical_name]

		, [fileID]	
			= tblMF.[file_id]

		, [fileType]
			= tblMF.[type_desc]

		, [isPercentGrowth] 
			= case
				when tblMF.[is_percent_growth] = 1 then 'Yes'
				else 'No'
			  end

		, [growth]
			= CASE tblMF.is_percent_growth 
					WHEN 1 THEN CONVERT(VARCHAR(10),tblMF.growth) +'%' 
					ELSE Convert(VARCHAR(10), tblMF.growth*8/1024) + ' MB' 
			  END


from   sys.databases tblSD

inner join  sys.master_files tblMF

	on tblSD.[database_id] = tblMF.database_id

order by

		cast
		(
			case 
				when tblSD.[name] in ('master', 'model', 'msdb', 'tempdb', 'tempdb') 
						then cast( tblSD.[database_id] as sysname)
						
				else tblSD.[name]
			end
				as sysname
		)
		, tblMF.[name]
	


Leave a Reply

Please log in using one of these methods to post your comment:

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