How do I shrink the transaction log on MS SQL 2000 databases?

Question

I have several databases where the transaction log (.LDF) is many times larger than the database file (.MDF).

What can I do to automatically shrink these or keep them from getting so large?

Answer 1

DBCC SHRINKFILE.

Here for 2005. Here for 2000.

Answer 2

Right click on the database in Enterprise Manager > All Tasks > Shrink Database.

Answer 3

Backup transaction log and shrink it.

If the DB is being backed up regularly and truncated on checkpoint, it shouldn't grow out of control, however, if you are doing a large number (size) of transactions between those intervals, it will grow until the next checkpoint.

Answer 4

try sp_force_shrink_log which you can find here http://www.rectanglered.com/sqlserver.php

Answer 5

Another thing you can try is to set the recovery mode to simple (if they are not already) for the database, which will keep the log files from growing as rapidly. We had this problem recently where our transaction log filled up and we were not permitted anymore transactions.

A combination of the shrink file which is in multiple answers and simple recovery mode made sure our log file stayed a reasonable size.

Answer 6

Using Query Analyser:

USE yourdabatase
SELECT * FROM sysfiles

You should find something similar to:

FileID    …  
1             1             24264    -1            1280      1048578               0             yourdabatase_Data    D:\MSSQL_Services\Data\yourdabatase_Data.MDF
2             0             128         -1            1280      66           0                             yourdabatase_Log      D:\MSSQL_Services\Data\yourdabatase_Log.LDF

Check the file ID of the log file (its 2 most of the time). Execute 2 or 3 times the checkpoint command to write every page to the hard-drive.

Checkpoint
GO
Checkpoint
GO

Execute the following transactional command to trunk the log file to 1 MB

DUMP TRAN yourdabatase WITH no_log 
DBCC SHRINKFILE(2,1)  /*(FileID , the new size = 1 Mb)*/

Answer 7

That should do the job

use master
go
dump transaction <YourDBName> with no_log
go
use <YourDBName>
go
DBCC SHRINKFILE (<YourDBNameLogFileName>, 100) -- where 100 is the size you may want to shrink it to in MB, change it to your needs
go
-- then you can call to check that all went fine
dbcc checkdb(<YourDBName>)

A word of warning

You would only really use it on a test/development database where you do not need a proper backup strategy as dumping the log will result in losing transactions history. In live systems you should use solution sugested by Cade Roux

Answer 8

Here is what I have been Using

BACKUP LOG <CatalogName> with TRUNCATE_ONLY
DBCC SHRINKDATABASE (<CatalogName>, 1)
use <CatalogName>
go
DBCC SHRINKFILE(<CatalogName_logName>,1)

Answer 9

no one here said it so i will: NEVER EVER shrink the transaction log. It is a bad idea from the sql server point of view.

keep it small by doing daily db backups and hourly (or less) transaction log backups. the transaction log backup interval depends on how busy your db is.