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

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]

	Autogrowth = 'Autogrowth: '
			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'
			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'
FROM dbo.sysfiles s

Posted November 15, 2010 by Norm Enger in Microsoft SQL Server

One response to “SQL 2000 and 2005 – Do You Know What Your Autogrowth Settings Are?

Subscribe to comments with RSS.

  1. Hi Norm,

    Good aricle

    jayant Das

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: