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?

more ▼

asked Nov 12 '09 at 12:25 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Step 1 - back it up!

more ▼

answered Nov 12 '09 at 01:23 PM

Blackhawk-17 gravatar image

11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 12 '09 at 12:45 PM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

That link only applies to versions 4.21a, 6.0 and 6.5
Nov 12 '09 at 01:02 PM Kev Riley ♦♦

Apologies. Here is a more upto date link

Nov 12 '09 at 01:21 PM dave ballantyne
I like the title of that second one. If the computer wasn't running SQL Server and the transaction log grew, then that would be unexpected!
Nov 12 '09 at 01:32 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 12 '09 at 08:07 PM

jjerome gravatar image


A FULL backup will not allow used log space to be reused or shrunk. Only a LOG backup will mark the log space for reuse and allow SHRINKFILE to release that space back to the OS. If you shrink then make sure you allow enough space so that the log doesn't need to grow again.
Nov 13 '09 at 08:00 AM David 1
(comments are locked)
10|1200 characters needed characters left

The nuclear option:

DBCC SHRINKFILE(<logical logfile name>, 1)
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.

more ▼

answered Nov 12 '09 at 12:40 PM

Andomar gravatar image

320 3 3 6

I don't think I should shrink it first. What does this do?
Nov 12 '09 at 02:23 PM Steve Jones - Editor ♦♦
It forces the database to discard the transaction logs, basically something you'd use if the harddisk was 100% full and nothing else worked. If you can still back up, that's by all means the best option :)
Nov 12 '09 at 08:51 PM Andomar
Note that the TRUNCATE_ONLY option was deprecated in 2005 and removed altogether in 2008. Set the Simple Recovery mode instead, which does much the same thing.
Nov 13 '09 at 07:56 AM David 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 12 '09 at 12:25 PM

Seen: 1896 times

Last Updated: Nov 12 '09 at 12:25 PM