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

sql-server, system-administration

Solution

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

Problem

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?

Original source