Long Running Transaction throws Out of Space Error 9002

  • 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

  • Check out this article[/url]. Essentially you need to do your deletes in smaller batches and commit the transactions sooner.

  • 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