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

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?

From stackoverflow Ed Haber

• DBCC SHRINKFILE.

  Here for 2005. Here for 2000.

  From Forgotten Semicolon

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

  From Dana

• 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.

  From Cade Roux

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

  From

• 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.

  From Fry

• 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)*/
  

  From jdecuyper

• 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

  Ed Haber : This worked great!
  From kristof

• 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)
  

  From spinner_den

• 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.

  From Mladen