I had an interesting SQL Server 2005 problem the other day, which I figured I'd share with you all, since it was kind of hard to find the appropriate solution.

One of our databases that tops out at only 500mb had a transaction log of over 50gb. The t-log never truncated when doing a full backup (although it was supposed to), and there were no error messages about this in the events or maintenance logs. I searched through the open transactions and processes, but none of them had run for more than a few minutes nor were they associated with that database. And shrinking the t-log didn't/wouldn't help, since it was full of actual data.

I Googled quite a bit for a solution, and finally found a forum post that suggested I had to detach then reattach the database without the bad t-log file. Sure enough, that worked... and now a week later the t-log remains at just a few kilobytes.

Epilogue: in the process of reattaching the database I found that the OS said the t-log was still "in use"... The open-handles feature of Sysinternal's Process Explorer made it quick work to find the offending process. The culprit in this case was CA's XOsoft Replication, which was probably the cause of the growing transaction log in the first place. Sheesh.