473,224 Members | 1,681 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 1982
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I...
10
by: TZoner | last post by:
1) Can one find the location of the 'Transaction Log' at: <Hard Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? 2) Is it safe to delete it, as SQL will create a new Transaction...
3
by: Thiko | last post by:
Hi I take one nightly full database backup at 02:00 and backup the transaction log to one backup set every 15mins. The commands to do this are as follows and are set up to run as database...
2
by: Deepak Mehta | last post by:
i have to update two tables from ASP pages with same data but i want that both of them should be updated at one time. If either of them is not updated then my transaction should roll back.I want...
1
by: Avanish Pandey | last post by:
Hello All We have 3 differen services (in 3 different server) Service A,B,C . We want to implement distributed transaction when call methods of B and C from A. Is it possible? if yes then how? ...
2
by: John Lee | last post by:
Hi, I have few questions related to .NET 2.0 TransactionScope class behavior: 1. Check Transaction.Current.TransactionInformation.DistributedIdentifier to identify if distributed transaction...
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
1
by: Hubert Fröhlich | last post by:
Hi list, when doing a large Insert I get in the logfile: 2004-08-03 07:23:46 LOG: recycled transaction log file "000001BA000000BD" 2004-08-03 07:34:00 LOG: recycled transaction log file...
1
by: Matik | last post by:
Hi to all, Probably I'm just doing something stupid, but I would like you to tell me that (if it is so), and point the solution. There ist the thing: I' having a sp, where I call other sp...
2
by: Ryan Liu | last post by:
Hi, I have few db write and read to execute, so I use transaction. Is that a problem or is that a regular way that I only use transaction on some cmds only, and other cmds I do not use...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.