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! :)
I wish I found this article about 2 months ago. Luckily for me I just renamed the transaction log in the “don’t do this” scenario before I figured out how to properly do it. I’m coming from the MySQL and DB2 side of the datbase world with just a couple MS SQL servers… It’s a whole new GUI world. ;)
Some doc from BOL
DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related database.
Syntax
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
I use this command over DBCC SHRINKFILE as it shrinks all the data files in one shot instead of having to do them individually.
DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
More info available in BOL.
This and DBCC SHRINKFILE are about 10 billion times safer and ‘cleaner’ than detaching and reattaching your database. This is especially true in a production environment where you most likely have your data split across numerous files and drives. Additionally, both commands can be performed on an online system whereas detaching the database takes your database offline.
TBH I’m shocked that detaching the database is even suggested. Please don’t ever try that on a production system. Hopefully, professional DBA’s know this, and I hope the original author of this article backs his/her database up on a regular basis. Something tells me you’ll need it.
Thanks for the comments. We’ve updated this post and corrected our erroneous ways! We can assure you that we are not professional DBA’s, just a couple of Googling computer guys.