ammexit
asked on
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is likely, yes.
Then....stop them, and then try issuing your trans log backup.
ASKER
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.
But, you said it was full. You switching it from a % to 10MB will not matter.
ASKER
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.
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.
ASKER
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.
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?
ASKER
Correct. I can paste it in shortly.
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.
ASKER
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
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
Yeah, don't close yet...I am talking to the MS guys right now.
OK, so I think the problem is that the disks your trans log is on are slow, and that is causing the error.
ASKER
Definitely something worth looking at if the issue was still occuring. Our disks are performing currently.
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)
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)
ASKER
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.
Ok. No data files, or backup files, right?
It won't free up OS space. After you run the backup, do this:
dbcc shrinkfile('logfile_log.ld
after that, either set your recovery mode to simple, or start doing regular log backups.