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: log_reuse_wait, replication, shrink, sp_repldone, sql server, transaction log file

posted by solutionsguy at 10:59 AM 0 Comments

This week I spent a considerable amount of time installing and troubleshooting a SSL install on IIS 6. I won't get into downloading and applying the Cert- that is documented everywhere. Here are few things to check for that will make it easier to get it all working.

1. You HAVE to specify an IP Address in the Web Site Identification box. The default is (All unassigned). Make sure to click on the "Advanced" button and add that IP address in the SSL Identities for the website. If you don't specify an IP Address for the SSL identity it will not work.

2. Make sure your Certficate Chain is valid. The easiest way to do this is with the