December 27, 2006 at 3:14 am
hi,
i have a job created which runs 5 procedures.
setep1 : execute procedure_one, setep2: execute procedure_two .....
as usual....
It updates and inserts millions of records. I got a message 'The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..' at the end in sql server logs.
Procedure did not ran successfully.
Please guide
December 27, 2006 at 11:06 am
Your code may be generating temp tables even you are not explicitely...
Run these procedures one by one in QA after enabling the query plan and see which part of the procedure is creating the temp tables...
Adding additional indexes can solve or reducing the transaction/batch size may resolve this issue...
What is the tempdb size??
MohammedU
Microsoft SQL Server MVP
December 28, 2006 at 2:25 am
Tempdb size is 9.8 GB. space available is also 9.8 GB (approx) - from enterprise manager.
But In the c drive where the server is installed tempdb.mdf size is more than 10 GB !!
I executed one of the procedure (not in a job), before that I edited the procedure, added begin transaction , commit wherever required. It ran successfully. I hope the 2nd procedure will also run successfully.
But will these 2 procedures run successfully if I put it in a job.
Job Step1 - execute proc_1
Job Step2 - execute proc_2
Will the addition of begin transactio.... sql statement.... commit really solves this problem If I put 5 procedure inside a job.
December 28, 2006 at 11:04 am
Begin tran and commit tran will not solve the problem...
You have to figure it out which procedure is causing tempdb to increase...
Run one by one in QA after enabling execution plan and SET STATISTICS I/O ON to see which procedure is generating big temp tables...
Always make your transaction as shot as possible...
MohammedU
Microsoft SQL Server MVP
January 2, 2007 at 5:58 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply