September 24, 2009 at 11:17 pm
Hello All,
To give a brief description of the problem.
I had configured a "Job" on SQL Server 2005 to run periodically
deleting old records from a huge table that has more than 40 million
records. But the job always fails with the exception
*****************************************************
The transaction log for database 'TPDB' is full. To find out why space
in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases [SQLSTATE 42000]
*****************************************************
apparently due to the long running delete operation.
I reproduce below the SQL statement causing this problem
delete GSMMessageDetails from GSMMessageDetails MD,
GSMMessageHdr MH, TraceFile TF
where
MH.TraceFileId = TF.TraceFileId
and MD.MessageId = MH.MessageId
and DATEDIFF(day, TF.StartTime , Getdate()) > ' + cast(@Time as
varchar)
Here are the options that I tried in vain
a. Configured the Transaction Log to "Unlimited Growth"
b. Increased the size of the "tempdb".
c. Removed the transaction for the delete operation.
d. Set the Recovery Mode for the DB to SIMPLE as opposed to FULL .
No answer yet.
Is there a way to get around this problem? Please suggest
Thanks,
Phani
September 25, 2009 at 8:18 am
Check out this article[/url]. Essentially you need to do your deletes in smaller batches and commit the transactions sooner.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2009 at 8:55 am
This question has also been posted here http://www.sqlservercentral.com/Forums/Topic793659-338-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply