Solved

Shrinking SQL 2000 Transaction Log file

Posted on 2007-03-28
3
2,245 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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Help with CASE 4 42
How to upload /Create/manage SQL database on Azure 3 31
Permissions on Database 11 36
SQL bit field not working as expected 3 19
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now