• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2258
  • Last Modified:

Shrinking SQL 2000 Transaction Log file

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
emauch
Asked:
emauch
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
SireesCommented:
0
 
Lee SavidgeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now