Script to Configuring Auto Growth to Fix MB

Few month ago I have written a post about what is Auto Growth and what is the best practice for Auto Growth. In this post I have discussed about why should we have Auto Growth for databases set in a fixed MB instead in percentage and how it will impact us.


In this post, I am going to give you a script that will help you find the database files which have Auto Growth option set in percentage and alter them all to fixed MB in one go.

/* 
-- Created By: Hemantgiri S. Goswami
-- Date: 29th April 2011
-- Version: 1.0

*/

-- Creating the table to capture temporary data
IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
	DROP TABLE ConfigAutoGrowth
GO	
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID		INT,
sDBName		SYSNAME,
vFileName	VARCHAR(max),
vGrowthOption	VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT 
	SD.database_id, 
	SD.name,
	SF.name,
	--sf.fileid, 
	--SUSER_NAME(owner_sid),
	--recovery_model_desc,
	CASE SF.status & 0x100000
	WHEN 1048576 THEN 'Percentage'
	WHEN 0 THEN 'MB'
	END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb 
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql


DECLARE db_cursor CURSOR FOR
SELECT 
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
WHERE sdbname NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' ) 
AND vGrowthOption  = 'Percentage'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)
	SET @Query  = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 500MB)'
	EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor  -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
SELECT 
SD.database_id, 
SD.name,
SF.name,
--sf.fileid, 
--SUSER_NAME(owner_sid),
--recovery_model_desc,
CASE SF.STATUS 
& 0x100000
WHEN 1048576 THEN 
'Percentage'
WHEN 0 THEN 'MB'
END AS 'Growth_Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

--Dropping the staging table
DROP TABLE ConfigAutoGrowth 
GO

Hope this helps!!


-- Hemantgiri S. Goswami

Read More

Common Mistakes in SQL Server - Part 5

Last to Last week we discussed how Null Values can cause a trouble in Common Mistakes in SQL Server – Part 4. This I will discuss about Auto Growth feature of SQL Server, and how it can impacts us.

What is Auto Growth?

Auto Growth is a feature that allowed database files (primary, secondary and log) to expand when database file becomes full - without manual intervention.

Auto Growth feature is really useful when we do not want to increase the size of database files manually. There are two ways you can set auto growth – using SQL Server Management Studio (SSMS here after) and T-SQL. Auto Growth can be configured – In Percent and In Megabytes.

How Auto Growth can impact us?

Now that we know what Auto Growth feature in SQL Server is; I’ll describe how this feature can impact us badly if we do not  configured this option carefully . Below is the table that will give you an idea what would happen if database file reaches to its threshold value and it needs to expand!!

See the calculation below when Auto Growth option is configured with in Percentage:

Configured to grow "In Percentage"
	File Size (before growth)	Growth - 10%	File Size (after growth)
1st time	5000	500	5500
2nd time	5500	550	6050
3rd time	6050	605	6655
4th time	6655	665.5	7320.5
5th time	7320.5	732.05	8052.55
6th time	8052.55	805.255	8857.805
7th time	8857.805	885.7805	9743.5855
8th time	9743.5855	974.35855	10717.94405
9th time	10717.94405	1071.794405	11789.73846
10th time	11789.73846	1178.973846	12968.7123

You will notice that the database growth is exponential when it is configured with Percentage i.e. 10%.  This is because this is calculated in cumulative manner; the value I have used here is 20 times lower than what we used to work on production systems generally.  If we set Auto Growth in Percentage it will occupy our HD space unnecessarily and creates fragmentation. Moreover when database files are expanding you will notice high volume in CPU spikes and I/O cycle.   

What is the best practice for Auto Growth option?

Best practice is to configure Auto Growth option in static value. See the sample calculation below:

	File Size (before growth) MB	Growth - 500MB	File Size (after growth) MB
1st Time	5000	500	5500
2nd Time	5500	500	6000
3rd Time	6000	500	6500
4th Time	6500	500	7000
5th Time	7000	500	7500
6th Time	7500	500	8000
7th Time	8000	500	8500
8th Time	8500	500	9000
9th Time	9000	500	9500
10th Time	9500	500	10000

Database grows in a controlled manner when Auto Growth is configured with the static/fix value in comparison to Percentage i.e. 10%.  The value I have used here is 20 times lower than what we used to work on production systems generally.   
 

It is true that now a day’s storage costs are lower down for desktops or home systems but it is really very costly when it comes to servers and data centers and this scenario will just add overhead to costing for storage solutions. So, the best bet is to configure Auto Growth with static value.

I would advise to configure Auto Growth option to static value only.

Read More