469,160 Members | 1,586 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

transaction log

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
Aug 19 '06 #1
2 1875
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
Aug 19 '06 #2
Charles MacLean (ch************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by TZoner | last post: by
3 posts views Thread by Thiko | last post: by
2 posts views Thread by Deepak Mehta | last post: by
1 post views Thread by Avanish Pandey | last post: by
15 posts views Thread by Zeng | last post: by
1 post views Thread by Hubert Fröhlich | last post: by
1 post views Thread by Matik | last post: by
2 posts views Thread by Ryan Liu | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.