SQL Server – Review Database File Growth

Prelude

Database Administrators ( DBAs) who grew up on mainframe, mini, and Unix databases like to tend to their database and grow it during carefully planned schedules.

SQL Server Admins might not have that luxury as their database is supposed to be auto-managed.

 

Metadata

Let us determine whether the data and log files are left at their default, or have been adjusted to grow at tuned values.

 

Function

dbo.itvf_DatabaseFileGrowth

Code


use [master]
go

/*
	drop function [dbo].[itvf_DataDatabaseFileGrowth]
*/

if object_id('[dbo].[itvf_DatabaseFileGrowth]') is null
begin

	exec('create function itvf_DatabaseFileGrowth() returns table return select [shell] = 1/0 ')

end
go

alter function [dbo].[itvf_DatabaseFileGrowth]
(
	  @fileGrowthMBData int = 200 
	, @fileGrowthMBLog  int = 200 
)
returns table

return
	(

	/*
		sys.databases
		https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql

		Note: A database that has just come online is not necessarily ready to accept connections. 
		To identify when a database can accept connections, query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. 
		The database can accept connections when the database collation returns a non-null value. 
		For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.

	*/
	with cteDatabase
	(
		  [dbid]
		, [dbname]
		, [collationName]
	)
	as
	(
		select 
				  tblSD.[database_id]
				, tblSD.[name]
				, tblSD.[collation_name]

		from   sys.databases tblSD
		where  (
						(tblSD.[is_read_only] = 0)

					and (tblSD.collation_name is not null)

			   )
	)

	select 
			  cteD.[dbname]

			, [fileSymbolicName]
				= tblMF.[name]

			, [filePhysicalName]
				= tblMF.[physical_name]

			, [fileType]
				= tblMF.[type_desc]

			, [growthIsInPercent]
				= case (is_percent_growth)
						when 1 then 'Y'
						else 'N'
				  end

			, [growth%] = 
					case
						when ( tblMF.is_percent_growth = 1) 
							then cast( tblMF.[growth] as varchar(10)) + '%'
						else null
					end

			, [growthKB] = 
					case
						when ( tblMF.is_percent_growth = 1) then null
						else (tblMF.[growth] * 8)
					end

			, [growthMB] = 
					case
						when ( tblMF.is_percent_growth = 1) then null
						else (tblMF.[growth] * 8) / ( 1024)
					end

			, [script]
				= 'ALTER DATABASE '
					+ quoteName( cteD.dbname )
					+ ' MODIFY FILE '
					+ ' ( '
					+	'	  NAME = ' + cast( tblMF.[name] as varchar(60))
					+	'	, FILEGROWTH = ' 
					+ case tblMF.[type_desc]

							when 'ROWS' 
								then cast(@fileGrowthMBData as varchar(10)) + 'MB'

							when 'LOG' 
								then cast(@fileGrowthMBLog as varchar(10)) + 'MB'
					  end
					+ ' ) '
					+ ';'

	from   cteDatabase  cteD

	inner join sys.master_files tblMF

		on cteD.[dbid] = tblMF.[database_id]

	where  tblMF.[state_desc] = 'ONLINE'

	and    tblMF.[is_read_only] = 0

	and
			(

				   -- is percent growth
				   ( tblMF.is_percent_growth = 1)


				-- Data Files
				or(
				
					 (

						/*
							If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB
						*/
						(
							    ( tblMF.[type_desc] = 'ROWS')
							and	( tblMF.is_percent_growth = 0)
							and ( 
									(tblMF.[growth] * 8) 
										/ 1024
								) != @fileGrowthMBData
						)

				    ) -- ROWS

				-- Log Files
				or (

						/*
							If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB
						*/
						(
							    ( tblMF.[type_desc] = 'LOG')
							and	( tblMF.is_percent_growth = 0)
							and ( 
									(tblMF.[growth] * 8) 
										/ 1024
								) != @fileGrowthMBLog
						)

				   ) -- LOG

				)  -- DATA OR LOG
			
			) -- end where clause

	) -- end function

go


Execute Select Against Function


	declare @fileGrowthMBData int = 200 
	declare @fileGrowthMBLog int = 200 

	set @fileGrowthMBData = 200 
	set @fileGrowthMBLog = 200

	select *
	from   [dbo].[itvf_DatabaseFileGrowth]
	(
		      @fileGrowthMBData
			, @fileGrowthMBLog
	)
	order by 
			  [dbname]
			, [fileSymbolicName]
			, [script]


Output

 

Explanation

  1. The requested size is 200MB
    • If a file is not set at that value, the script column will reflect the SQL that should be ran to align

 

Review

Review Log File

Let us review how our files have been growing thus far.

Detail

dbo.itvf_TraceFileParseDatabaseFileGrowthDetail

Code


use [master]
go

if object_id('[dbo].[itvf_TraceFileParseDatabaseFileGrowthDetail]') is null
begin

	exec('create function [dbo].[itvf_TraceFileParseDatabaseFileGrowthDetail]() returns table return select [shell] = 1/0 ')

end
go

alter function [dbo].[itvf_TraceFileParseDatabaseFileGrowthDetail]
(
)
returns table

return
	(
		SELECT TOP 100 PERCENT

			  [startTime] 
					= convert(varchar(30), ftg.StartTime, 100)

			, [eventName]
				 = te.name

			, [databaseName] = DB_NAME(ftg.databaseid)

			, [filename] 
				= ftg.[filename]

			, [growthMB]
				 = (ftg.IntegerData*8)/1024.0 

			, [durationMillisecond]
				 = (ftg.duration/1000)


	FROM ::fn_trace_gettable
		(
			  REPLACE(CONVERT(VARCHAR(1000),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc')
			, DEFAULT
		) AS ftg 

	INNER JOIN sys.trace_events AS te 

		ON ftg.EventClass = te.trace_event_id  

	WHERE 
		(
			   ( ftg.EventClass = 92)  -- Date File Auto-grow
			OR ( ftg.EventClass = 93) -- Log File Auto-grow
		)

	order by
			ftg.[StartTime] desc

	) -- end function

go


 

Output

 

Explanation

  1. Because of Instant File Initialization data file growths will take less time relative to log file growths
    • Logs are cyclical and have to be zeroed

 

dbo.itvf_TraceFileParseDatabaseFileGrowthSummary

Code


use [master]
go

if object_id('[dbo].[itvf_TraceFileParseDatabaseFileGrowthSummary]') is null
begin

	exec('create function [dbo].[itvf_TraceFileParseDatabaseFileGrowthSummary]() returns table return select [shell] = 1/0 ')

end
go

alter function [dbo].[itvf_TraceFileParseDatabaseFileGrowthSummary]
(
)
returns table

return
	(

		with cte
		(
			  [startTime] 
			, [eventName]
			, [databaseName] 
			, [filename] 
			, [growthMB]
			, [durationMillisecond]

		)
		as
		(

			SELECT 
					  [startTime] = ftg.StartTime

					, [eventName]
						 = te.name

					, [databaseName] 
						= DB_NAME(ftg.databaseid)

					, [filename] 
						= ftg.[filename]

					, [growthMB]
						 = (ftg.IntegerData*8)/1024.0 

					, [durationMillisecond]
						 = (ftg.duration/1000)


			FROM ::fn_trace_gettable
			(
				  REPLACE(CONVERT(VARCHAR(1000),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc')
				, DEFAULT
			) AS ftg 


			INNER JOIN sys.trace_events AS te 

				ON ftg.EventClass = te.trace_event_id  


			WHERE 
				(
					   ( ftg.EventClass = 92)  -- Date File Auto-grow
					OR ( ftg.EventClass = 93) -- Log File Auto-grow
				)

		)
		select 
				  TOP 100 PERCENT

				  [databaseName] 

				, [eventName]

				, [numberofIncrements]	
					= count(*)

				, [growthMB]
					= cast
						(
							sum([growthMB])
							as decimal(12, 2)
						)

				, [durationMillisecondAvg]
					= avg([durationMillisecond])

				, [durationMillisecondSum]
					= sum([durationMillisecond])

				, [durationSecondSum]
					= (sum([durationMillisecond]) / 1000)

				, [startTimeMin] 
					= convert
						(
							  varchar(30)
							, min([startTime])
							, 100
						)

				, [startTimeMax] 
					= convert
					  (
							  varchar(30)
							, max([startTime])
							, 100
						)

		from   cte

		group by

			  [eventName]
			, [databaseName] 

		order by
				  [growthMB] desc

	)
go


 

Output

 

Credits

Crediting Greg Larsen for the code on how to query the default trace file to identify database file growths.

 

Summary

Please review your data and log file growth settings.

It is best to have a scheduled script that checks and grows them as they near your high water marks.

Unless you are stressed for storage, please do not have AutoShrink enabled.

 

References

  1. Greg Larsen
    • SQL Server Database Growth and Autogrowth Settings
      Link

 

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