unable to shrink log file

Question

Post reply

unable to shrink log file

Sean Trask

SSC Veteran

Points: 265
More actions
July 16, 2007 at 12:58 pm

#117846

Hello,
Has anyone been unable to shrink their transaction log file in SQL Server 2005?
The issue happened like this:
- tlog was growing, and in danger of filling up the disk it was on
- backed up tlog a few times
- tried to shrink, it would not shrink
- backup w/ truncate only, and still could not shrink it
At that point, we were desperate, so we recycled SQL Server. When the db was started, it went into recovery mode for 20 minutes (very stressful), but came back, and then we could shrink the transaction log.
My question is this?
1) What happened, did someone hold onto some transaction and not let go
2) How can we monitor for this, or what query can be run to check if we are at risk for this behavior?
Thanks,
Sean

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

1. Is the database replicated ?

2. What's the recovery Mode

3. Are there any opened transactions ?

* Noel

Vivien Xing SSChampion Points: 12297 More actions · Answer 2

There could be a long running update or delete process. Break down to smaller chunk each time for the update or delete, this will help to reduce the T-log size.

Rebooting the server is not a good option for this.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

You wopuld have used dbcc sqlperf(logspace) to see how your log was being used and then tried shrinking.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ajaykumar SSC-Addicted Points: 469 More actions · Answer 4

u just delete the inactive transaction logs i..e transactions already recovered.

try this it might help u .

***ajay

Sean Trask SSC Veteran Points: 265 More actions · Answer 5

Hi all, thanks for the replies, I will answer some of the questions:

1) Database is NOT replicated

2) Database is in FULL recovery mode

3) As far as open transactions, none I am aware of, but that is all that makes any sense.

I also want to add that this went on for like 5 days, the log went from 40 gig, to 60 gig, to 80 gig, and all the while we were trying to get the darn thing to shrink.

In the future I should run the dbcc checkperf command? Also, are there other queries, things I can check to see who is holding onto logspace that won't be freed up?

leokarp SSC Enthusiast Points: 149 More actions · Answer 6

In order to shrink log file when database is in FULL recovery mode you need to perform two consecutive actions:

1. Backup transaction log.

2. Immediately after step 1 run DBCC SHRINKFILE ('transaction log logical file name',TRUNCATEONLY)

You can create Database Maintenance Plan with Backup Database(transaction log) task and Execute T-SQL statement task or alternatively create a job with the steps described.

Sean Trask SSC Veteran Points: 265 More actions · Answer 7

Hi there - thanks for the reply, I did that, several times. We backed it up, tried to shrink, backed it up, shrink - no dice. We then did the truncate, and tried to shrink, still no luck.

noeld SSC Guru Points: 96590 More actions · Answer 8

so you:

1. backed up the log

2. executed DBCC SHRINKFILE ('transaction log logical file name', 10000)

and still does not do it ?

what is the output of:

USE [dbname]

go

DBCC SQLPERF(LOGSPACE)

* Noel

Sean Trask SSC Veteran Points: 265 More actions · Answer 9

Noel - you had the sequence of event correct.

Unfortunately, the event has passed, so doing a dbcc sqlperf(logspace) doesn't get me anywhere now, but if it happens again, I shall run the command - and post what I find.

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 10

What version of SQL2k5 are you on? That could be an issue. I have run into this issue when a process is still holding on to some process while the truncate is going. I have to go in later or I have now scheduled truncates on all dbs just prior to full backups and that has helped.

Sean Trask SSC Veteran Points: 265 More actions · Answer 11

We're on SP1 of Sql Server Enterprise - we'll get to SP2 as soon as we can get an outage.

Stuart Jones-461126 SSC Rookie Points: 40 More actions · Answer 12

Hi,

I had the same problem a few days ago. For better or worse i ended up changing the recovery mode to simple, then did a backup and shrink. This had an immediate effect on the log. I then set the recovery mode back to Full. Im no SQL expert, but this certainly worked for me.

hth

Stuart

Murali-420858 Ten Centuries Points: 1076 More actions · Answer 13

Try checking if there are any long running queries that are having any blocks or deadlocks else you can try issuing a manual chekpoint to clear the committed transacitons from the log.