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

Script


select
 
		  [server]
			= cast(
						SERVERPROPERTY('servername')
							as sysname
				  )	

        , [database]    
            = tblSD.[name]
 
        , [datafileSymbol] 
            = tblMF.[name]
 
        , [datafileSymbol] 
            = tblMF.[physical_name]
 
        , [fileID]  
            = tblMF.[file_id]
 
        , [fileType]
            = tblMF.[type_desc]

		, [fileSizeMB]
			= (tblMF.[size] * 8 )
				/
				(
					1024 
				)

        , [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]

Output

Output – HPALM – 2017Jan20th

databasefilesizeandgrowth_hp_alm_20170120_0957am

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