How to check your SQL Server Database Autogrowth
Autogrowth is a great thing on Databases, it allows you to set how big a file can get and how it growth to get there. This control is one feature that very few DBAs I know actually utilise.
For most of us this is just a “oh yes, that thing” type of feature and we do not look at the impact this has on your database logging.
As always there is a lovely article from Microsoft http://support.microsoft.com/kb/315512 that explains all of this very nicely and why it is a good idea to do it right.
Here is the summary of best practices.
Best Practices
For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.
You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.
Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.
Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
Keep the size of your transactions as small as possible to prevent unplanned file growth.
This is all well and good, but wouldn’t it be nice to have some simple code to give you a quick look at your database?
exec sp_MSforeachdb @command1 = 'use [?]; EXEC sp_helpfile'
Simple, well here is the slightly bigger version which gives you a nice table output.
DECLARE @i_Max INT, @i_Count INT, @MAX_Count INT, @sql NVARCHAR(4000),@Database VARCHAR(255)
DECLARE @Databases TABLE(id INT IDENTITY(1,1), databasename VARCHAR(250))
INSERT INTO @Databases
SELECT name FROM sys.databases db
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
AND db.state <> 6 AND db.user_access <> 1 ORDER BY 1
CREATE TABLE #AutoGrow123 (DB VARCHAR(500),name VARCHAR(250), fileid INT, [filename] VARCHAR(500), [filegroup] VARCHAR(50), [size] VARCHAR(50),[maxsize] VARCHAR(50),[growth] VARCHAR(50),[usage] VARCHAR(50))
SET @i_Max = (SELECT MAX(id) FROM @Databases )
SET @i_Count = 1
WHILE @i_Count <= @i_Max
BEGIN
SET @Database = (SELECT databasename FROM @Databases WHERE id = @i_Count)
SET @sql = '
USE ['+@Database+']
DECLARE @Table TABLE(name VARCHAR(250), fileid INT, [filename] VARCHAR(500), [filegroup] VARCHAR(50), [size] VARCHAR(50),[maxsize] VARCHAR(50),[growth] VARCHAR(50),[usage] VARCHAR(50))
INSERT INTO @Table
EXEC sp_helpfile
INSERT INTO #AutoGrow123
SELECT '''+@Database+'''
, T1.*
FROM @Table T1'
EXEC sp_executesql @sql
SET @i_Count = @i_Count + 1
END
SELECT * FROM #AutoGrow123
IF OBJECT_ID(N'tempdb..#AutoGrow123', N'U') IS NOT NULL BEGIN DROP TABLE #AutoGrow123 END
Hope you can get a clearer view of our autogrowth now.Adrian