If my log fills up, what is the best way to clear it in SQL Server 2000? What about in SQL Server 2005?
asked
What's the best way to clear the log?
Step 1 - back it up!
The nuclear option:
DBCC SHRINKFILE(<logical logfile name>, 1)
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<logical logfile name>, 1)
This pretends to do a backup and throws away all existing transaction logs, and then it shrinks the logfile. This is not without danger as you cannot do a point-in-time restore after truncating the logfile.
The logical logfile name can be found in the properties of the database, Files tab, and then Logical Name.
Don't think I can help with 2000, but I have a couple notes from shrinking a 2005 log.
I assume you're using a Recovery model of 'Full' for your database (in SSMS: right click on db -> properties -> Options). The log will not empty out until you back it up. Most times you can do this by doing a FULL backup of you database, which marks the spot in the log where you have backed up. The database won't allow you to clear anything out of the log until it sees the log file has been backed up.
Any time I've had problems with a full log, I've had to run a couple FULL backups to properly mark the log file for shrinking. This involves working with SQLPERF and LOGINFO to make sure the log is setup correctly before doing the SHRINKFILE.
This link should help: http://www.mssqltips.com/tip.asp?tip=1225
