December 9, 2008 at 9:23 am
I am using a job to update some data but it fails with the message…..
The log file for database 'DB1' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002). The step failed.
However the recovery model is simple and at the beginning of the script Ive got DBCC SHRINKDATABASE (MyDatabase, 20);
If I just run all the stored procedures it seems to work OK but it doesn’t in the job. Does anyone have any other ideas that I can do… Im sure I have got enough space
Thanks
Debbie
December 9, 2008 at 9:44 am
How much disk space is there for the log drive?
Also, does the log have restricted max size?
Any other maint. tasks like reindexing etc? that would cause the log to grow
December 9, 2008 at 9:50 am
Jerry Hung (12/9/2008)
How much disk space is there for the log drive?Also, does the log have restricted max size?
Any other maint. tasks like reindexing etc? that would cause the log to grow
It is set to 'By 10% unrestricted growth
At present the initial size for the log file is 11,468. It is currently saying that the size is 23227.13 MB and space available is 9994.74 MB.
I cant see any reindexing tasks or anything
Debbie
December 9, 2008 at 10:22 am
Does the job run all the procedures within a single transaction?
You could run a profile when the job is running, include the errors and warnings as well as growth information and try to capture exactly what is happening when the log runs out of space.
December 9, 2008 at 10:55 am
Instead of shrinking the database at the beginning of the process, I'd suggest growing the log out to the necessary size and making sure that it's cleaned out (BACKUP LOG dbname WITH NO_LOG). If it's still filling up then, you need more room.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 11:04 am
Debbie Edwards (12/9/2008)
at the beginning of the script Ive got DBCC SHRINKDATABASE (MyDatabase, 20);
Why?
Is the entire job running within a transaction? Logs can only be truncated to the beginning of the oldest open transaction, even in Simple. Any form of replication on that database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2008 at 12:42 pm
Grant Fritchey (12/9/2008)
Instead of shrinking the database at the beginning of the process, I'd suggest growing the log out to the necessary size and making sure that it's cleaned out (BACKUP LOG dbname WITH NO_LOG). If it's still filling up then, you need more room.
Grant, I would not recommend issuing the above - especially since it is deprecated in SQL Server 2008. Here is the message you will get on a 2008 instance:
Msg 3032, Level 16, State 2, Line 1
One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.
I would, however - recommend growing the log file out to the size needed for the largest transaction and then issuing a checkpoint between each transaction. That should allow the log space to be reused.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 9, 2008 at 12:51 pm
Jeffrey Williams (12/9/2008)
Grant, I would not recommend issuing the above - especially since it is deprecated in SQL Server 2008. Here is the message you will get on a 2008 instance:
Oh man, thanks for pointing that out. I have been spending WAY too much time mucking about with TSQL queries. I haven't even looked into some of the admin work on 2008 yet.
More to learn. Yay! :w00t:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2008 at 2:04 am
Thanks for all the advice.
What Im going to do is split up each stored procedure into lots of little seperate procedures. I currently have 2 in the job and it fails at step 1. Lets see if this helps....
Debbie
December 10, 2008 at 2:05 am
Nicholas Cain (12/9/2008)
Does the job run all the procedures within a single transaction?You could run a profile when the job is running, include the errors and warnings as well as growth information and try to capture exactly what is happening when the log runs out of space.
Thanks for this bit of advice. Ill split up the SPs and use profiler to see what is happening....
Debbie
December 10, 2008 at 2:06 am
Jeffrey Williams (12/9/2008)
Grant Fritchey (12/9/2008)
Instead of shrinking the database at the beginning of the process, I'd suggest growing the log out to the necessary size and making sure that it's cleaned out (BACKUP LOG dbname WITH NO_LOG). If it's still filling up then, you need more room.Grant, I would not recommend issuing the above - especially since it is deprecated in SQL Server 2008. Here is the message you will get on a 2008 instance:
Msg 3032, Level 16, State 2, Line 1
One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.
I would, however - recommend growing the log file out to the size needed for the largest transaction and then issuing a checkpoint between each transaction. That should allow the log space to be reused.
When you say issue a checkpoint. Would it be enough to just split the SQL into seperate SP's or should I be doing something else???
Thanks for the help again.
Debbie
December 10, 2008 at 8:44 am
After each operation, you can issue the statement CHECKPOINT. You can review BOL at:
Basically, when you issue a checkpoint - all dirty pages are written to disk and the transaction log is updated marking the space in the transaction log so the space can be re-used.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply