MCITP

MCITP

Tuesday, September 11, 2012

Script to change the autogrowth setting of all database by 100 MB.



Below is the script that will change the autogrowth  setting of all database except master and msdb database by 100 MB.

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' ,'msdb' )  
--AND vGrowthOption  = 'Percentage' or 'Mb'
 
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 = 100MB)' 
    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 
  DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SET @SQL=' USE ?
SELECT ''?'' [Dbname]
,[name] [Filename]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM ?.sys.database_files'

IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE  ##Fdetails (Dbname VARCHAR(50),Filename VARCHAR(50),Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_msforeachdb @SQL
SELECT * FROM ##Fdetails  ORDER BY Dbname
 
--Dropping the staging table 
DROP TABLE ConfigAutoGrowth  
GO

2 comments:

  1. This is really very useful script when you have to change the database 'autogrowth' option for multiple databases on a SQL Server.....great script written Shashank....keep it up.

    ReplyDelete
  2. It was a great help to find the script and it saved my lot of time. Thanks a million.

    ReplyDelete

Followers