Facility9

Ever have a database log file grow to epic proportions on the weekend? I have. Ever forget to set up job monitoring on that server? I have. Ever get so pissed off that you decided to write a job to automatically shrink the log files whenever they grow? I have.

I used my rudimentary knowledge of Service Broker, Event Notifications, and hackery to create a solution to my problem. For starters, I added the following stored procedure to every database:

CREATE SCHEMA dba ;
GO

IF OBJECT_ID(N'dba.ShrinkLog', N'P') IS NOT NULL
  DROP PROCEDURE dba.ShrinkLog ;
GO

CREATE PROCEDURE dba.ShrinkLog
  WITH EXECUTE AS OWNER
AS
  BEGIN
    DECLARE @error AS VARCHAR(2000) = 'LOG_FILE_AUTO_GROW encountered' ;
    RAISERROR (@error, 10, 1) WITH LOG

    DECLARE @size_in_mb AS DECIMAL(18, 5) ;
    DECLARE @max_log_size_in_mb AS DECIMAL(18, 5) = 10240 ;

    SELECT  @size_in_mb = ( size * 8.0 ) / 1024.0
    FROM    sys.database_files
    WHERE   data_space_id = 0 ;

    IF @size_in_mb > @max_log_size_in_mb
      BEGIN

        SET @error = 'log shrink occured on ' + DB_NAME() + ' at '
          + CONVERT(VARCHAR(50), GETDATE(), 101) + '. log file was '
          + CAST(@size_in_mb AS VARCHAR(50)) + ' mb in size.' ;

        RAISERROR (@error, 10, 1) WITH LOG

        DBCC SHRINKFILE (2, 1024) ;
      END
  END
GO  

Once I had my procedure in place, I needed a way to fire this procedure. Rather than mindlessly attempt to shrink the log files every X minutes, I decided that I should only shrink the file after a log growth has occurred. This means that, in theory, I could stop the log from growing… because, you know, I’m a bastard. We don’t want the log to NEVER grow, just shrink when it gets too big – which is possible because this might be a problem between log backups. That’s why there’s a check to see if the log file is greater than an arbitrary maximum size.

The next step was to actually figure out how to make this procedure fire. I wasn’t sure about it, but I thought that there was a SQL Server Event Notification that would fire whenever the log file grew. Turns out that I was right: LOG_FILE_AUTO_GROW.

Now, I knew from reading Mladen Prajdić’s blog that I could use the SQL Server Service Broker to respond to system events and, so, I did just that.

USE SystemEvents ;
GO

IF OBJECT_ID(N'dbo.LogShrinkResponse', N'P') IS NOT NULL
  DROP PROCEDURE dbo.LogShrinkResponse ;
GO

CREATE PROCEDURE dbo.LogShrinkResponse
AS
  BEGIN
    DECLARE @msgBody XML
    DECLARE @dlgId UNIQUEIDENTIFIER

  -- you can change this to get all messages at once
    WHILE ( 1 = 1 )
      BEGIN

        BEGIN TRANSACTION ;

        BEGIN TRY 

            ;
          RECEIVE TOP (1)
                @msgBody = message_body,
                @dlgId = conversation_handle FROM LogShrinkNotificationQueue ;

          IF @@ROWCOUNT = 0
            BEGIN
              IF @@TRANCOUNT > 0
                BEGIN
                  ROLLBACK ;
                END

              BREAK ;
            END

          DECLARE @database_name AS SYSNAME ;
          DECLARE @data AS XML ;
          DECLARE @statement AS NVARCHAR(2000) ;

          SET @data = EVENTDATA() ;

          SET @database_name = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                           'NVARCHAR(128)') ;

          SET @statement = N'USE ' + @database_name
            + '; EXEC dba.ShrinkLog ; '

          EXEC sp_executesql @statement ;

          IF @@TRANCOUNT > 0
            BEGIN
              COMMIT ;
            END
        END TRY

        BEGIN CATCH
          IF @@TRANCOUNT > 0
            BEGIN
              ROLLBACK ;
            END

          -- write any error in to the event log
          DECLARE @errorNumber BIGINT ,
            @errorMessage NVARCHAR(2048) ,
            @dbName NVARCHAR(128)
          SELECT  @errorNumber = ERROR_NUMBER() ,
                  @errorMessage = ERROR_MESSAGE() ,
                  @dbName = DB_NAME()

          RAISERROR (N'Error WHILE receiving Service Broker message FROM queue LogShrinkNotificationQueue.
                        DATABASE Name: %s; Error number: %I64d; Error Message: %s',
            16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG ;
        END CATCH ;
      END
  END
GO

CREATE QUEUE LogShrinkNotificationQueue
WITH STATUS = ON,
ACTIVATION ( PROCEDURE_NAME = dbo.LogShrinkResponse, MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo'
) ;
GO

CREATE SERVICE LogShrinkNotificationService ON QUEUE LogShrinkNotificationQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) ;
GO

CREATE ROUTE LogShrinkNotificationRoute
WITH SERVICE_NAME = 'LogShrinkNotificationService',
ADDRESS = 'LOCAL' ;

CREATE EVENT NOTIFICATION LogShrinkNotificationEvent ON SERVER FOR LOG_FILE_AUTO_GROW
TO SERVICE 'LogShrinkNotificationService', 'current database' ;
GO

/*
DROP PROCEDURE dba.ShrinkLog
DROP EVENT NOTIFICATION LogShrinkNotificationEvent ON SERVER
DROP ROUTE LogShrinkNotificationRoute
DROP SERVICE LogShrinkNotificationService
DROP QUEUE LogShrinkNotificationQueue
*/

Here’s what’s going on up there.

We create a procedure that will be called when the log shrink notification is caught. It loops through our queue and grabs the top message off each time and processes it. If there’s a message, it will pull the name of the database out of the XML from the event notification. We use the database name to build dynamic SQL that we execute to fire off the log shrink procedure.

The rest of the code just sets up the service broker queue, notification, routing, and event handler. It’s basic, boiler plate, code that tells SQL Server to set up service broker on the current database and listen for th LOG_FILE_AUTO_GROW event.