November 13, 2007 at 4:12 pm
Hello,
Someone reported this error in their application (the db is SQL 2000 standard edition):
SQLExecDirect Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'mydb' is full. Back up the transaction log for the database to free up some log space.--37000 Statement: UPDATE etc
I tried to manually back up the log to a different drive, but the log did not appear to truncate and the space in the log file was not freed up. I also noticed they had set the log file growth to restrict to 1 GB.
In order to relieve the problem, I set the log file to 2 GB and set the file to autogrow. This is a temporary stopgap, but I wonder why the initial manual backup of the log did not free up the space. Does anyone know why not or what I may have done wrong?
Also, I see that the transaction log backup maintenance plan is failing with this error:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. This job owner is sa.
Does anyone know why this error is happening? Is running the job under sa the cause, or is there some other reason the job is failing?
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2007 at 4:20 pm
Just to be sure, were you looking at the size of the .ldf file on disk after the BACKUP LOG or were you looking at the space in the .ldf looking at using the Task Pad view in EM?
It has been a while I had this type of issue, but did you try a second BACKUP LOG also?
😎
November 13, 2007 at 4:33 pm
Thanks for your reply.
I was looking at the ldf file in the file system. I understand that the file may stay the same size after a transaction log backup (a shrink file would be needed to shrink it, as I understand it), but it should free up the space in the file for more transactions, shouldn't it?
Also, does the transaction log not shrink if there are uncommitted transactions in the log?
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2007 at 4:44 pm
It will only autoshrink if you have autoshrink turned on (not recommended). You shouldn't shrink the physical size of your transaction log after your log backups as your transaction log will have to grow again as transactions are written between log backups.
You try to set the size of your transaction log large enough to handle your normal level of database activity between transaction log backups, but allow it to grow should you have an unexpect rise in transactions. You could set up a process to shrink the transaction log back to the basic size, but i would only do that during an off period time for the database so that the shrink process does not adversely impact performance.
😎
November 13, 2007 at 4:49 pm
Thanks, absolutely, I don't think I would shrink the transaction file given the size it is now (around 1 GB). However, this application I have been introduced to seems to produce wild swings in tempdb size (>70 GB) and may do the same with the transaction log. I just want some way to find a way to control that if possible. If the application admins or the vendor can't control it, I'm not sure how to avoid situations where the log fills up, aside from trying to get a much bigger drive to see just how big the files grow.
At any rate, I was just wondering why the initial log backup did not free up the space.
Also, do you know what might cause that error with the failed maintenance plan?
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2007 at 8:36 pm
The maintenance plan should have created a log file that should provide you more information regarding the error. The error message itself does not provide enough information.
As for controlling the size of the transaction log, or preventing it from filling up, your best bet is to increase the frequency of your transaction log backups. If you are doing them every 2 hours (for instance) you should increase the frequency of your backups to say every hour. The frequency of your transaction log backups, however, should really be determined by how much data your users (not you) are willing to lose should you have to restore from backups. If you (the users) can not afford to lose more than 5 minutes worth of activity, then you need to be running your transaction log backups every five minutes.
Your tempdb, set it to the max it has used, and allow it to grow by a set size, not a percentage. It should also be on its own set of disks, seperate from your database files.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply