transaction log

[Charles MacLean]

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.

Thanks

[Hugo Kornelis]

Re: transaction log

On Sat, 19 Aug 2006 17:46:44 GMT, Charles MacLean wrote:

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

Hi Charles,

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Re: transaction log

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@sommarsk og.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