x

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, 2009 at 12:25 PM in Default

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

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

4 answers: sort voted first

Step 1 - back it up!

more ▼

answered Nov 12, 2009 at 01:23 PM

avatar image

Blackhawk-17
12k 30 35 42

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

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.

more ▼

answered Nov 12, 2009 at 12:40 PM

avatar image

Andomar
320 3 5 10

(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, 2009 at 12:45 PM

avatar image

dave ballantyne
928 1 3 6

(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, 2009 at 08:07 PM

avatar image

jjerome
191 2 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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x2017
x502
x80

asked: Nov 12, 2009 at 12:25 PM

Seen: 2288 times

Last Updated: Nov 12, 2009 at 12:25 PM

Copyright 2016 Redgate Software. Privacy Policy