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)', '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.