Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Shrinking SQL 2000 Transaction Log file

Posted on 2007-03-28
3
Medium Priority
?
2,256 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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