SQL 2000 Transaction Log is Full

I've run into an issue today.  One of my databases transaction logs is full.  The disk where the transaction log resides has 14.5 GB of free space.  The Transaction Log is set to auto grow at 10 percent and there is no maximum file size restriction.  Thats where the issue starts.  In an attempt to resolve the issue I'm runninng a manual backup of the TL to free up space.  The backup task sits and doesn't appear to fail or move forward.  I let is sit for 8 hours.  Any ideas?  I'd like to ultimately free up space in the log file, but as a temporary fix I'm thinkinig I can manually increase the size of the log file.  What do you think?
ammexitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
>>In an attempt to resolve the issue I'm runninng a manual backup of the TL to free up space.

It won't free up OS space.  After you run the backup, do this:
dbcc shrinkfile('logfile_log.ldf',0)


after that, either set your recovery mode to simple, or start doing regular log backups.
0
ammexitAuthor Commented:
Yes, I know it won't shrink the file itself, but I need to free up space within the file.  The transaction log backup does not complete which is a ig part of my issue.
0
chapmandewCommented:
Do you have open transactions?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

ammexitAuthor Commented:
It is likely, yes.
0
chapmandewCommented:
Then....stop them, and then try issuing your trans log backup.
0
ammexitAuthor Commented:
Issue is resolved.  Changing the auto growth setting to grow by 10 MB instead by a percentage returned the Log file to a usable state.
0
chapmandewCommented:
But, you said it was full.  You switching it from a % to 10MB will not matter.
0
ammexitAuthor Commented:
I had said that the file itself was full.  The drive where the file resided has 14.5 GB of free space.  It was the auto growth of the file that was failing which was causing "log file full" errors to occur.  Changing the file to grow by MB instead of by percent resolved the issue.  Not sure why it wasn't able to grow by percentage, maybe the percentage was too high, but set at 10 MB the file is growing as needed and responding as it should.
0
chapmandewCommented:
You also said this, which throws me off:  The Transaction Log is set to auto grow at 10 percent and there is no maximum file size restriction.
0
ammexitAuthor Commented:
Yes, that was true, which is what through me off too.  Hence, why I posted this.  With the TL set to auto grow by 10 percent, no maximum file size and plenty of space on the drive, I should not have been experiencing any issues.
0
chapmandewCommented:
And you received an actual error saying that the transaction log was full...even though it was set to autogrow and you weren't out of space?
0
ammexitAuthor Commented:
Correct.  I can paste it in shortly.
0
chapmandewCommented:
Please do.  Give me screenshots of the db settings as well as the errors you were getting.  This may be a bug...if it is, I will let MS know.
0
ammexitAuthor Commented:
Here is the error in event viewer:

Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (2)
Event ID:      17052
Date:            3/28/2010
Time:            1:13:10 AM
User:            N/A
Computer:      SQLServer
Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'DATABASE' is full. Back up the transaction log for the database to free up some log space.

I'm attaching the screen shot of the transaction log settings before the change.  Hope this helps anyone who experiences the same issue.
settings.jpg
0
chapmandewCommented:
Yeah, don't close yet...I am talking to the MS guys right now.
0
chapmandewCommented:
OK, so I think the problem is that the disks your trans log is on are slow, and that is causing the error.  
0
ammexitAuthor Commented:
Definitely something worth looking at if the issue was still occuring.  Our disks are performing currently.
0
chapmandewCommented:
Is there any other activity on the drives that the log file is on?  

A way to see if this is the issue is to make the log files really large (maybe 5 gb, and have it grow in 100mb increments)
0
ammexitAuthor Commented:
A few other log files reside on the drive.  Its a production database so I would need to recreate the issue in our test environment.
0
chapmandewCommented:
Ok. No data files, or backup files, right?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.