SQL 2005 - Transaction Logs

The transaction logs on our servers have grown too large and it takes ages for tools to crawl through them looking for specific transactions.

Our backups are done at the disk level, so SQL itself doesn't know that we've backed up OK

So - here's how to clear the transaction logs:

1. Set the recovery mode to simple. This stops SQL from logging transactions
2. Run a database shrink - this will clear the logs
3. Set the recover mode back to full.

A couple of things can go wrong:

a) One of our databases thought it was being replicated although we've never turned it on. In this case:

Exec sp_removedbreplication // against the database to remove replication info.

However - if you are using replication you should use:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 // this removes the log entries that are locked as unreplicated

b) If you have created multiple files for data and logs, DON'T unless you have to for example to span disks. To remove them:

1. Clear the log files (see above)
2. Shrink each additional file ticking the "empty file by migrating" box
3. Go to properties of the DB, then file and remove it

c) If you have multiple log files, you will not be able to detatch the DB, delete the log files then re-attach - it will fail so DON'T! The detach, delete, attach process seems to work OK for single log file DB's - but always keep a back of the log file.

Nick