Solved

Shrinking SQL 2000 Transaction Log file

Posted on 2007-03-28
3
2,248 Views
Last Modified: 2008-01-09
I have a SQL 2000 transaction log file that has grown too large.  I'm running in Full Recovery Mode and have found a number of articles that say running a backup will automatically truncate and shrink the log file.  I've ran multple backups but the log file is still large.
0
Comment
Question by:emauch
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18811011
>have found a number of articles that say running a backup will automatically truncate and shrink the log file.  
either the article text is indeed wrong, or you misinterpreted the text.

running regular transaction log backups will keep the log file from growing, but will NOT shrink it.
it will clear the contents, so internally, there is new free (reusable) space in the file.

to shrink it, run a transaction log backup, try to use DBCC SHRINKFILE ( log_name, <size> )
if that does not work the first time, repeat the above steps, plus add some transactions to the database.

the issue is that when the internal log pointer is at the end of the file, it will not shrink the file. doing the transactions will get that pointer moved to the beginning of the file, and the shrink will succeed.

good luck
0
 
LVL 20

Expert Comment

by:Sirees
ID: 18811017
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 18811025
Hi,

run this script:

-- Run this in the db that you want to shrink. Use query analyser

-- Create a fixed length string. It HAS to be fixed length
declare @chPathAndFileName as char(60)

-- Create the path and file name
select @chPathAndFileName = '<<Insert path and name of log file to back up>>-' + convert(varchar(255), getdate(), 112) + right('00' + cast(datepart(hh, getdate()) as varchar(2)), 2) + right('00' + cast(datepart(mm, getdate()) as varchar(2)), 2) + right('00' + cast(datepart(ss, getdate()) as varchar(2)), 2)+ '.trn'
select @chPathAndFileName

-- add a checkpoint to the transaction log
checkpoint

-- perform a backup to disk
backup log <<Insert database name here>> to disk = @chPathAndFileName

-- backup and truncate
backup log <<Insert Log file Name here >> with truncate_only

-- truncate the log file
dbcc shrinkfile('<<Log file filename>>_log', 10, 'truncateonly')

-- At the end, perform a full backup of the database in case anything goes wrong between now and when the next back up is, otherwise you've lost everything


Try that. It's a script I used to truncate the logs at my old work place. No idea if it is the proper way to do it but it always worked for me.

Cheers,

Lee
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question