Sql-server – Large Transaction Log Backups in Service Broker-enabled system

service-brokersql serversql-server-2008

We have a system that uses Service Broker for asynchronous messaging, with a fairly high throughput of messages. We don't really care about the persistent features of SSB in this scenario — the messages are pretty much just immediate notifications and if they are not processed immediately when the intended receiver application is up we end up just discarding any backed-up messages when the application restarts.

What we've noticed is that our database is manifesting very large transaction log backups, most of which are filled up with SSB transactions. This is complicating our backup management and test restore scenarios, and generally just making life harder on the DBA side of the fence.

Are there any tips or tricks that can be used to minimize the transaction log overhead of SSB messages in the case where we don't really care about having them available for restore?

Best Answer

You could use a different database for your SSB load. It seems like your SSB data is transitory in nature. That would allow you to either switch to SIMPLE recovery model for that DB or throw away the log backups in case you need FULL for some other reason like mirroring.

I don't think you'll be able to reduce log usage for SSB operations. I'm interested to see other answers but usually log usage is not tunable for OLTP-style transactions. They are always fully logged. Minimal logging only applies to special cases.

Related Solutions

SQL Server – Service Broker & AlwaysOn Availability Groups Transmission Queue Behavior

Over the past several months, I have been working with Microsoft's Product Support team, and they have acknowledged two bugs in SQL Server 2012 in relation to this issue. They will be releasing patches for those bugs as part of the next service pack for SQL Server 2012.

Sql-server – How to troubleshoot db/app latency during transaction log backup

It sounds like your transaction log probably has a lot of VLFs on it which causes the SQL Server to take a hit when doing the transaction log backups. You can find this out by running

use {MyDatabase}
GO
DBCC LOGINFO
GO

The important part to look at is the number of rows which are returned. If there are more than about 100 rows returned then you've got problems (please comment with the number of rows that are returned).

The fix is pretty easy. Make a note of the size of the transaction log. Shrink the transaction log down to as small as possible (the above command will return 2 rows). This is done using the DBCC SHRINKFILE command. You may need to run the shrink command a few times to get it small enough. Try waiting a minute between shrink commands for the logs to cycle and run a log backup just before running the shrinkfile command.

DBCC SHRINKFILE (2,1)
GO

Then once the transaction log file is as small as possible grow the file in 8000 meg chunks using either the GUI in SQL Server Management Studio or by using the ALTER DATABASE command.

ALTER DATABASE MyDatabase
MODIFY FILE (name='MyLogFile', FILESIZE=8000MB)
GO

If you need the file to be larger than 8000 Megs do 8000 megs in size, then 16000 megs in size, then 24000 megs in size always growing 8000 megs at a time. Compared to the prior size of the transaction log grow the low to the 8000 meg mark past the size that the log was.

If this doesn't do the trick let me know.

Best Answer

Related Solutions

SQL Server – Service Broker & AlwaysOn Availability Groups Transmission Queue Behavior

Sql-server – How to troubleshoot db/app latency during transaction log backup

Related Question