What's the best way to clear the log?

Question

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

Answer 1

0

Step 1 - back it up!

answered Nov 12 '09 at 01:23 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

(comments are locked)

Answer 2

0

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

answered Nov 12 '09 at 12:45 PM

dave ballantyne
918 ● 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

http://support.microsoft.com/kb/317375

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)

Answer 3

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

Answer 4

0

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.

answered Nov 12 '09 at 12:40 PM

Andomar
300 ● 1 ● 1 ● 5

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)

What's the best way to clear the log?

Follow this question

Related Questions