[thelist] Truncating Transaction Logs SQL Server 2000

[Anthony Baratta]

Just to second Ken's suggestion, and add a few thoughts. I've found that sometimes SQL Manager can not deal with transaction logs that are 10's of GBs in size. What I've done in the past, is to "detach" the DB, delete the transaction log, and then re-attach the DB. Of course this means time that the DB can be down.

If you need to keep the DB on-line, you need a place where you can back up the transaction log to, then run the shrink option. The shrink won't run until you've backed it up, or forced a check-point.

Lastly, make sure you have a DB Maintenance plan to the DB and backup the Transaction log regularly. This will force a checkpoint of the transaction log and clear out all the olde transactions you don't need anymore. Oh, and make sure you setup a single maintenance plan for each DB, because if one transaction log or db backup fails, then everything scheduled after that one does not run. This way one failure does not cause the other maintenance routines to not run.

HTH.

-----Original message-----
From: "Ken Schaefer" Ken at adOpenStatic.com
Date: Mon, 17 Apr 2006 05:09:28 -0700
To: thelist at lists.evolt.org
Subject: Re: [thelist] Truncating Transaction Logs SQL Server 2000

> Backing up and truncating the transaction log is detailed, quite extensively,
> in both the SQL Server 2000 and 2005 documentation. You just need to RTM.