Solutions Guy - a simple way to get your business going
Wednesday, January 28, 2009
I recently had a transaction log that had consumed over 99% of the available drive space and was at the verge of running out of room. Initially I did a transaction log backup and immediately followed it up with a log file shrink. No luck. The file would not shrink. So I knew that there were pending transactions that would not allow it to shrink.

To double check before wasting precious time, I ran the following query:

select name, log_reuse_wait, log_reuse_wait_desc from sys.databases

The log_reuse_wait was set to 6, log_reuse_wait_desc was REPLICATION.

Ah. One thing people often forget is that until a transaction has recorded in the distribution database it won't be fully committed in the database. So I had some transactions that hadn't been published yet that were hanging up my log file from shrinking. Although I do have replication enabled on this server, all of the publications/subscriptions are snapshots, so I really don't need to have all the transactions pushed to the distribution database. Regardless, there were transactions waiting.

To find out what transactions were pending replication, I ran this query: sp_repltrans. It returned a list of 9 transactions that were pending. Because I only do snapshots I didn't care about these pending transactions and decided to flush them out. To flush unfinished transactions I use the sp_repldone command. If you want to set all transactions to "published" so they will clear from the transaction log, use the following syntax:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Be careful with this if you are running transactional replication because it will allow your data to get out of sync.

After I flushed the pending replication transactions, I ran another log backup, then a file shrink and the file was down to a respectable size.

Labels:

, , , , ,

posted by solutionsguy at 10:59 AM 0 Comments

 
© 2008 Solutions Guy, All Rights Reserved  |  801.228.0312  |   Email Us