SQL 2000 and 2005 – Do You Know What Your Autogrowth Settings Are?

Originally published Saturday, December 29, 2007 at http://www.biblereadingschedule.com/nucleus?itemid=75&catid=6

Are the Autogrowth settings on your SQL files for all databases appropriate? Here is a script that shows you the Autogrowth settings for all files in the current SQL 2000 or 2005 database. Yes, I know the goal is always to manage your file sizes proactively so that Autogrowth is not invoked, but most of us still choose to leave Autogrowth on as a fail-safe.

I patterned this script after some others I found by Googling, but none of the ones I tried turned out to work 100% accurately for all databases in both SQL 2000 and 2005. After some tinkering, I believe I have attained 100% accuracy for all databases…at least I have not found any errors so far checking against several hundred databases. Also I have designed the output to match, exactly, the output you would see in the Autogrowth field when you use SQL 2005 SQL Server Management Studio (SSMS) to right-click on a database, select “Properties,” and click on the “Files” settings page.

In my second post today I will provide you with a SQL 2005 Reporting Services report, and accompanying stored procedure, that you can use to view Taskpad-like info about all your databases on all of your SQL servers, and which also displays the Autogrowth info from this script next to each file. I believe you will find this to be a handy tool you can use to help you accomplish that “proactive” file size/usage monitoring I mentioned above.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

USE [myDatabaseName]

SELECT
	s.name,
	s.filename,
	Autogrowth = 'Autogrowth: '
		+
		CASE
			WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR s.growth = 0 THEN 'None'
			WHEN s.status & 0x100000 = 0 THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0) / (1024.0 * 1024.0))) + ' MB'
			ELSE 'By ' + CONVERT(VARCHAR,s.growth) + ' percent'
		END
		+
		CASE
			WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR s.growth = 0 THEN ''
			WHEN CAST([maxsize] * 8.0 / 1024 AS DEC(20,2)) <= 0.00 THEN ', unrestricted growth'
			ELSE ', restricted growth to ' + CAST(CAST([maxsize] * 8.0 / 1024 AS DEC(20)) AS VARCHAR) + ' MB'
		END
FROM dbo.sysfiles s