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 */