Shrink, Damn’d Log! Shrink, I Say!

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](http://msdn.microsoft.com/en-us/library/ms179635.aspx). Now, I knew from reading [Mladen Prajdić’s blog](http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx) 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 */