September 24, 2009 at 11:11 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 27, 2009 at 6:38 pm
Regardless whether your database is in Simple or Full recovery the log tracks update, insert, delete statements.
The actual delete statement was not complete or had an extraneous quote character so the below statement carries that forward as I'm not exactly sure what the statement should be. By looping with some value (below it is defined as 100K) the log for each purge of 100K or less rows should be reduced but in your situation you may need to reduce or increase the value. In Full recovery you would still need to backup the log often and possibly during the looping. I've also seen in Simple a truncate log statement during the looping. you will need to test for your situation whether that is required.
One last comment if you have an index on TF.StartTime you are most likely not using the index. You are better off changing the logic to have only TF.StartTime on one side and the calculated value on the other.
DECLARE @Purged_Count INT -- Number that were processed
DECLARE @Loop_Count INT -- Number of rows to process each loop
SET @Purged_Count = 1
SET @Loop_Count = 100000
WHILE (@Purged_Count) ' + cast(@Time as
varchar)
SET @Purged_Count = @@ROWCOUNT
END
September 28, 2009 at 3:49 am
You've already asked this question here
http://www.sqlservercentral.com/Forums/Topic793661-338-1.aspx
and here
http://www.sqlservercentral.com/Forums/Topic793659-338-1.aspx
and had responses on both... what else are you looking for?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply