Solved

Shrinking SQL 2000 Transaction Log file

Posted on 2007-03-28
3
2,251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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