THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Watch out for that autogrow bug

Under some circumstances, autogrow for database files can be set to some 12000 percent. I think this is limited to SQL Server 2005 and for databases upgraded from SQL Server 2000 (I didn't bother to search - feel free to comment if you know). So, if you have a reasonably sized database and autogrow kicks in, you can do the maths and realize that pretty soon you are out of disk space.

I wrote a proc that I schedule that check for out-of-bounds values in sys.database files. The proc generates a bunch of messages it prints (handy if you have as Agent job with output file) and also constructs an error message and does RAISERROR (handy if you implemented alerting, for instance according to http://www.karaszi.com/SQLServer/util_agent_alerts.asp).

I prefer to schedule below as Agent job and use Agent alerts to notify me if we do have db with autogrow out-of-whack. As always, don't use code if you don't understand it.

USE maint
GO

IF OBJECT_ID('check_autogrow_not_percent'IS NOT NULL DROP PROC check_autogrow_not_percent
GO

CREATE PROC check_autogrow_not_percent
AS
DECLARE 
 
@db sysname
,@sql NVARCHAR(2000)
,
@file_logical_name sysname
,@file_phyname NVARCHAR(260
,
@growth VARCHAR(20)
,
@did_exist bit
,@msg NVARCHAR(1800)
,
@database_list NVARCHAR(1000)

SET @did_exist CAST(AS bit)
SET @database_list ''

--For each database
DECLARE dbs CURSOR FOR
 SELECT 
name FROM sys.databases
OPEN dbs
WHILE 1
BEGIN
  FETCH 
NEXT FROM dbs INTO @db
  
IF @@FETCH_STATUS <> BREAK

  SET 
@sql 'DECLARE files CURSOR FOR
 SELECT CAST(growth AS varchar(20)), physical_name, name
 FROM ' 
QUOTENAME(@db) + '.sys.database_files
 WHERE is_percent_growth = 1
 AND growth > 20'
  
EXEC(@sql)
  
OPEN files
  
WHILE 1
  
BEGIN
    FETCH 
NEXT FROM files INTO @growth@file_phyname@file_logical_name
    
IF @@FETCH_STATUS <> BREAK
    SET 
@did_exist CAST(AS bit)
    
SET @database_list @database_list '["' @db '": "' @file_logical_name '"]' CHAR(13) + CHAR(10)
   
SET @msg 'Out-of-band autogrow in database "' @db '"' 
   
' with growth of ' @growth +
   
', logical file name "' @file_logical_name '"' 
   
', physical file name "' @file_phyname '"' 
   
'.'
   
RAISERROR(@msg101WITH NOWAIT
  
END
  CLOSE 
files
  
DEALLOCATE files
END
CLOSE 
dbs
DEALLOCATE dbs
IF @did_exist CAST(AS bit)
  
BEGIN
   SET 
@msg 'Databases with out-of-control autogrow in databases: ' CHAR(13) + CHAR(10) + @database_list
   
RAISERROR(@msg161WITH LOG
  
END
GO
Published Wednesday, August 05, 2009 3:14 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kevin3NF said:

August 5, 2009 8:59 AM
 

AaronBertrand said:

August 5, 2009 9:02 AM
 

noeldr said:

I have been bitten by this bug repeatedly even after SP2. :(

August 5, 2009 10:02 AM
 

merrillaldrich said:

I've been burned by that one. Evil.

August 5, 2009 11:52 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement