Posted by Erland Sommarskog on 08/19/06 22:33

Charles MacLean (charlesmaclean@sbcglobal.net) writes:
> How can I truncate and shrink the transaction log under SQL 2000? My
> log is 10 GB in size and I assume most of the transactions are
> committed.

First question is: if your database goes belly-up, are you content with
restoring from the lastest backup, or do you need point-in-time recovery?

If you don't need point-in-time recovery, make sure that the database is
in simple recovery, and then use DBCC SHRINKFILE to shrink the file to
a reasonable size. What is a reasonable size, is difficult to say without
knowledge about your database, but say 25% the size of the data file.

If you need point-in-time recovery you need to regulary back up the
transaction log, just like you back up the database. Your question makes
me think you don't. First make sure that your database is in FULL or
BULK-LOGGED recovery, and if it's not, you need to take a full backup
of the database. Then backup the transaction log. Again, you can shrink
it to maybe 25% of the data file.

If you are already taking regular log backups, you apparently need a
10 GB log file, and I would advise against shrinking it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]