question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

What's the best way to clear the log?

If my log fills up, what is the best way to clear it in SQL Server 2000? What about in SQL Server 2005?

sql-server-2005sql-server-2000transaction-log
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered

Step 1 - back it up!

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andomar avatar image
Andomar answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dave ballantyne avatar image
dave ballantyne answered

Have you found the cause of why the log is filling up ? Microsoft have some good info Here

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jjerome avatar image
jjerome answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.