SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Pesky Percent File Growth

As DBAs we all know setting your file growth to grow by percent is not optimal.  It can cause all kinds of issues, which rear their ugly heads as performance problems (see these articles by Brent Ozar, & Tim Ford).  So, when I have to support a third party application that automatically adds data files using percent instead of fixed size, it really irritates me.  I got tired of seeing these new files show up on my daily exceptions report, so I decided to do something about it.  This post explains what I did.

I have a home grown process that goes out and collects all kinds of information about my servers on a daily basis.  Once that process is complete it sends reports, via email, so I can get a quick look at things when I first arrive at work in the morning.  One of those reports is my file exception report.  It reports things like excessive data/log file growth, data/log files that are almost full, data/log files that use the percent file growth, etc.  The first time I had a file show up on my exceptions report with a percent file growth, I decided I needed to be notified before the report landed in my inbox, so I created a server level trigger that is triggered by the ALTER DATABASE command.  This trigger captures all the relevant information and sends me an email.  Here’s the code I used for my trigger:

CREATE TRIGGER [ddl_trig_alterdatabase]
ON ALL SERVER
FOR ALTER_DATABASE
AS
   DECLARE @Subject nvarchar(255)
      , @Body nvarchar(MAX)
   SELECT @Subject = N’A database was altered on ‘ + @@Servername
      , @Body = EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
   exec msdb.dbo.sp_send_dbmail
      @recipients = ‘myemail@emaildomain.com’, — varchar(max)
      @subject = @Subject, — nvarchar(255)
      @body = @Body
GO
ENABLE TRIGGER [ddl_trig_alterdatabase] ON ALL SERVER
GO

 

This worked great, I found out before my report showed up and I could address the issue when it happened.  Unfortunately I discovered that one of the applications was making this change in the middle of the night.  I certainly didn’t want to have to wake up in the middle of the night to address this issue, since it really isn’t a “production down” type of problem (and let’s face it, no DBA wants to be woken in the middle of the night for anything, let alone something that is not production down).

I decided I needed to do something else other than just send an email notification, I needed to take corrective action when it occurred.  So I wrote a little stored procedure that will take the ALTER DATABASE statement as a parameter, parse it and take the appropriate corrective action. 

Simple enough, right?  Now I just need to add the call to my newly created stored procedure in my server level trigger and we are good to go.  But wait, you can’t ALTER a database within an ALTER DATABASE statement (don’t believe me? Use this as a learning exercise to see what happens when you try).  So what could I do?  There are several things you could do, but I chose to create a table that could hold this newly created ALTER DATABASE statement and insert the record there.  Then I created a SQL Agent job that runs once every hour and reads that table and executes any entries it finds, then deletes them after successfully executing.

Here’s the code for my stored procedure:

CREATE PROCEDURE [dbo].[ChangePercentGrowthMaxSizeUnlimited]
@SQLText nvarchar(max)
AS
SET NOCOUNT ON

/* We start with something like this
ALTER DATABASE [DatabaseName]
ADD FILE (NAME = N’DataLogFileName’
         ,FILENAME = N’X:\DataLogFileName.ndf’
         , SIZE = 20
         , FILEGROWTH = 5%
         , MAXSIZE = UNLIMITED)
*/

/*  We want to produce something like this
ALTER DATABASE [DatabaseName]
MODIFY FILE ( NAME = N’DataLogFileName’
            , MAXSIZE = 102400KB
            , FILEGROWTH = 10240KB )
*/

— Local Vars
DECLARE @AddFileText VARCHAR(8) = ‘ADD FILE’
   , @ContainsAddFileText BIT = 0
   , @AddFileStartPosition BIGINT
   , @FileGrowthPercentText VARCHAR(13) = ‘FILEGROWTH = %!%’
   , @ContainsFileGrowthPercent BIT = 0
   , @ContainsMaxSizeUnlmitedText BIT = 0
   , @StartPosition INT
   , @EndPosition INT
   , @Length INT
   , @DatabaseName VARCHAR(128)
   , @FileName VARCHAR(128)
   , @AlterDatabaseLength INT = LEN(‘ALTER DATABASE ‘)
   , @AlterDatabaseSQL NVARCHAR(MAX)

   — Is it an ADD File operation?
   SELECT @AddFileStartPosition = PATINDEX(‘%’ + @AddFileText + ‘%’, @SQLText)
  
   IF @AddFileStartPosition > 0
   BEGIN
      –It’s an ADD File operation
      SET @ContainsAddFileText = 1

      IF @SQLText LIKE ‘%’ + @FileGrowthPercentText + ‘%’ ESCAPE ‘!’
      BEGIN
         — it’s adding a file using percent file growth
         SET @ContainsFileGrowthPercent = 1
     
         — Is it setting MAXSIZE to UNLIMITED?
         IF PATINDEX(‘%’ + @MaxSizeUnlimitedText + ‘%’, @SQLText) > 0
         BEGIN
            SET @ContainsMaxSizeUnlmitedText = 1
         END
        
         — Now we need to parse the ADD FILE expression and build a MODIFY FILE operation from the parts
         — Get database name
         SELECT @StartPosition = @AlterDatabaseLength + 1
         SELECT @Length = CHARINDEX(‘ADD FILE’, @SQLText, @AlterDatabaseLength) – @AlterDatabaseLength
         SELECT @DatabaseName = LTRIM(RTRIM(SUBSTRING(@SQLText, @AlterDatabaseLength + 2, @AddFileStartPosition – 1 – @AlterDatabaseLength – 2)))

         — Get filename
         — Start by finding the start of the logical file name
         SELECT @StartPosition = CHARINDEX(””, @SQLText, PATINDEX(‘%’ + ‘[^FILE]NAME %”’ + ‘%’, @SQLText)) + 1
         SELECT @Length = CHARINDEX(””, @SQLText, @StartPosition) – @StartPosition
         SELECT @FileName = SUBSTRING(@SQLText, @StartPosition, @Length)

         — Now Create the alter database operation
         SELECT @AlterDatabaseSQL = N’ALTER DATABASE ‘ + @DatabaseName + N’ MODIFY FILE ( NAME = N”’ + @FileName + N”’, ‘
         IF @ContainsFileGrowthPercent = 1
            SELECT @AlterDatabaseSQL = @AlterDatabaseSQL + N’FILEGROWTH = 10240KB’

         IF @ContainsMaxSizeUnlmitedText = 1 AND @ContainsFileGrowthPercent = 1
            SELECT @AlterDatabaseSQL = @AlterDatabaseSQL  + ‘, MAXSIZE = 102400KB’
         ELSE
            IF @ContainsMaxSizeUnlmitedText = 1 AND @ContainsFileGrowthPercent = 0
               SELECT @AlterDatabaseSQL = @AlterDatabaseSQL + N’MAXSIZE = 102400KB’
           
         SELECT @AlterDatabaseSQL = @AlterDatabaseSQL + N’ )’

         INSERT dbo.DBAAlterDatabase
         (SQLText)
         VALUES
         (@AlterDatabaseSQL)
      END
   END
   ELSE
   BEGIN
      — It’s not an ADD FILE operation
      PRINT ‘It”s not an ADD FILE operation, no work to do.’
   END

RETURN 0

 

Here’s the code for my modified server level trigger:

CREATE TRIGGER [ddl_trig_alterdatabase]
ON ALL SERVER
FOR ALTER_DATABASE
AS
   DECLARE @Subject nvarchar(255)
      , @Body nvarchar(MAX)
   SELECT @Subject = N’A database was altered on ‘ + @@Servername
      , @Body = EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
   exec msdb.dbo.sp_send_dbmail
      @recipients = ‘myemail@emaildomain.com’, — varchar(max)
      @subject = @Subject, — nvarchar(255)
      @body = @Body 
   EXEC dbo.ChangePercentGrowthMaxSizeUnlimited @SQLText = @Body
GO
ENABLE TRIGGER [ddl_trig_alterdatabase] ON ALL SERVER
GO

Works like a charm!  But wait, you might notice that I’m making more than a few assumptions in my stored procedure, and you would be correct.  I feel like I need to add a disclaimer to this post, the same way they add disclaimers to pharmaceutical commercials.

Here are my assumptions: 

  1. Only one file is being created at a time. 
  2. We always want to change our file growth to 10MB. 
  3. We don’t want a max file size of unlimited and we always want to set our max file size to 100MB. 
  4. There will be no errors.
  5. I am in NO way responsible if this code breaks something on your server.

Explanation of assumptions:

  1. What fun would it be if I did all the hard work for you?  This can easily be adapted to work with multiple files being created at the same time.  You can do it, I have faith in you.
  2. For my particular instance, I know the model database settings for this server and I hard coded them, because that’s what I wanted.  You could easily adapt the code to use your model database settings or any other value for that matter (HINT: think sys.sysfiles).
  3. See explanation of assumption 2 above.
  4. I removed all my standard stored procedure framework code (which includes error checking) for brevity.  You should ALWAYS have error checking in your stored procedures!
  5. You should NEVER assume code is not malicious in nature and add it to production without a thorough understanding of what it’s doing.  Shame on you if you did.

 


SQL Swimmer

I started out as a software developer back in 1996 in Denver, CO, doing Client/Server development in PowerBuilder. I am now a Data Architect, living in High Point, NC and I love what I do. I’ve worked with all versions of SQL Server since the infamous split from the Sybase code (a.k.a. version 4.21a). I’ve worn all the hats that come with dealing with SQL Server, developer to data architect and everything in between. Twitter handle: @SQLSwimmer

Comments

Leave a comment on the original post [sqlswimmer.wordpress.com, opens in a new window]

Loading comments...