SQLServerOne of our database’s transaction log had grown to over 5gig. Considering the actual size of the database itself and the amount of activity this is huge. Plus it slows down our back-ups and just generally bugs me.
So, first I’ll tell you how not to try and clean it up. Don’t goto the Enterprise manager and right click on the database and select “Take Offline.” The next thing you don’t want to do is go and delete the transaction log file now that the database is offline. This sounds like a good idea and yet it’s not. When you goto bring the database back online it will kick and scream and throw fits and generally not work. Plus if the transaction log is large like say 5gig, it’s too big to send to the recycle bin and it gets permanently deleted.
The way you should shrink the size of the transaction logs is this:
a) Detach the database using sp_detach_db procedure (before that ensure no processes are using the database files.) Open query analyzer and type: “sp_detach_db ‘DB Name'” then execute query.
b) Delete the log file. (I feel more safe simply renaming it before deleting it.)
c) Attach the database again using sp_attach_db procedure.
This will tell the database server that the database was closed cleanly and it will proceed to create a new transaction log.
Update 10/03/06: We’ve since switched to using the following command to shrink our transaction log:
backup log “databasename” with Truncate_only
DBCC SHRINKFILE (‘databasename_log’,truncateonly)
It’s been pointed out to us that detaching the database is not a safe way to shrink your transaction log. We stand corrected! :)