Log File is full
-
We have a DB that has 1 gig storage for a log file. My application users keep getting "The log file for database XYZ is full. Back up the transaction log for the databse to free up some log space".
I am a developer but the guys in charge of the DB think it is my app causing the problem because we have other apps that perform more tranactions that don't get this error and have a much smaller space allocation.
Can you tell me what to look for or where to start? I thought it may have to do with not enough 'commits' in my app? I am not a DBA.
I believe it is cleared out or backed up every hour but we a filling up 1 gig within an hour.
Thanks
-
First thing to do is to check that the transaction log is actually getting backed up. It won't be the first time that a log should be getting backed up, but isn't.
More commits won't help. Every single data modification must be logged, regardless of how large the transactions are. That said, if you have very large, very long running transactions it would be an idea to split them up.
If the log is getting abcked up, get the DBAs to check before and after a log backup how much space is used in the log. If it doesn't decrease then ther's something preventing the log from truncating.
The normal process is that once the log is backed up, the inactive transactions (the ones that have been committed and written to the data files) are cleared from the log. If that isn't happening then there's either an open transaction, or replication preventing the log from truncating.
If there's transactional replication in place in this DB, ask the DBAs to check that the replication isn't lagging behind.
Make sure in your code, whenever you begin a transaction you either commit it or roll it back. you can use DBCC OPENTRAN to check if there are any uncommitted transaction. You'll probably have to ask the DBAs to do this.
Is this SQL 2000 or SQL 2005?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I'm trying to get sa access to find out some of these things on my own. Another developer here said he had a similar problem a couple of years back and they dropped an recreated the indexes weekly and that helped. Does that sound right?
-
That isn't right. If anything, recreating indexes might cause more log space to be used.
Be sure the network people are backing up the log. Backing up the database is not enough.
-
Hello and Wish you all a very Happy new year
,1. Agree with Jones, Check if the logs are geting backed up.
2. Run DBCC SHRINKFILE with the desired target size until the log file shrinks to the target size.
Please check the KB for more information:
http://support.microsoft.com/kb/272318/en-us
Regards,
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
-
sounds to me that you need a DBA ! If you don't need to keep transaction log backups then put the database into simple recovery. You were close with your suggestion about more commits but what you might need is batches, these might enable you to maintain the transaction log better.
DO NOT shrink ldf and mdf files unless they are never going to grow again, and then always rebuild all your indexes after a shrink to make the data contiguous ( as as much as it can be ) shrinking databases can seriously impact performance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
How can you view the log file?
-
View what about it? The size, the percentage full, the actual log records?
While it is possible to read the log records, I really don't recomend it. It's verbose, completely undocumented and quite hard to uunderstand.
What do you want to know?
p.s. I'll second Colin on shrinking files. Don't do it unless you really know what you're doing. Usually causes more problems than it solves.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
My suspicion of what is causing my problem is that there may be an uncomitted transaction. I was wondering if I could figure out which one might be causing the problem.
-
Possibly, but there's a lot of info in there and it's not intended to be human-readable. It's confusing at best.
Run DBCC OPENTRAN to see if there are any open transactions. You'll get something like this if there are any open transactions
Transaction information for database 'MyDB'.
Oldest active transaction:
SPID (server process ID): 88
UID (user ID) : -1
Name : user_transaction
LSN : (2986:3096:2)
Start time : Jan 4 2007 8:48:35:130AM
SID : 0x010500000000000515000000d064d2063316aa3c297c3d4960560200
Have you confirmed that the log is been backed up?
If so, have the DBA check the utilised space in the log before and after a transaction log backup.
Is there transactional replication on this server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
First off, we don't have 'traditional' DBA's. Just a couple of guys with DBA authority - that's why I'm having to track this down.
Have you confirmed that the log is been backed up? I'm pretty sure it is (They said it was and offered to show me).
If so, have the DBA check the utilised space in the log before and after a transaction log backup. How do they do this? What should I look for?
Is there transactional replication on this server? How would I find out? What should I look for if there is/isn't?
Thanks for all of your input!
-
Accept their offer, and check that the log is been backed up. Check how often, and make sure it's not failing.
DBCC
SQLPerf(LogSpace)
It'll show you the percentage of traqnsaction log that is used. check before and after a transaction log backup. The percentage used should have gone down. If it hasn't, it means that something is preventing the log from truncating.
Ask the guys with DBA authority about the replication. They should know how to check. If there is, they were probably the ones to set it up.
If not, you can see from enterprise manager is a server has replication enabled. See if any of the databases have a hand under the icon in the tree-view.
If there isn't, no worries. If there is, it's another possible cause for the log not shrinking.
Is this a SQL 2000 server, or a SQL 2005 server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
At 13:33:
Database Name Log Size (MB) Log Space Used (%)
----------------------- ----------------- ------------------
MY_DB_NAME 919.61719 51.470974
Database backup at 13:36
At 13:39
Database Name Log Size (MB) Log Space Used (%)
----------------------- ----------------- ------------------
MY_DB_NAME 919.61719 0.98716348 0
It is a SQL 2000 server.
When I look in enterprise manager, I see a picture of a "database" with a circle at the bottom with a green triangle in it. Is this what you were referring to for replication?
-
Database backup at 13:36
Database backup, or log backup? I guess the latter, since db backups don't truncate the log.
I think what might be necessary at this point is to wait until the log fills and then debug.
See if you can get a job put on that server that checks the log space every halff hour, or so and logs it to a table. Might make tracing problems easier.
In the mean time, I'd suggest that you check through your code, make sure whenever you begin a transaction the it will be rolled back or commited. Make sure you don't have any places where you begin a tran and wait for input.
When I look in enterprise manager, I see a picture of a "database" with a circle at the bottom with a green triangle in it. Is this what you were referring to for replication?
No, that's the server, not a database, and the green arrow just says it's running (as opposed to a red square). Expand that out til you see the databases and look at them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It seems as though the commits are there properly. We do not have replication.
I will set up a trace on Monday.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply