Post reply

The transaction log for database 'tempdb' is full.

  • November 1, 2010 at 5:20 pm

    #242480

    Hi,

    I am executing a stored procedure, for a financial year's worth of data.

    I get the error:

    The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

    The log_reuse_wait_desc contains "ACTIVE_TRANSACTION"

    The space reserved for tempdb.mdf is 25.93 GB, the amount of space used us 11.13MB

    Can anyone provide some insight to what is going on, and a solution to the error?

  • November 1, 2010 at 8:13 pm

    #1245173

    Sounds like you have an uncommitted transaction(s).

    http://support.microsoft.com/kb/317375

    What is the result of DBCC OPENTRAN ?

    If you search "The transaction log for database 'tempdb' is full" you will find plenty to read that may help. Is your disk drive full ?

  • November 1, 2010 at 8:22 pm

    #1245174

    Hi,

    Well the result of the DBCC command is:

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    however at the time the stored procedure was running, the result could well have been different.

  • November 1, 2010 at 8:42 pm

    #1245180

    Scott Thornton-407727 (11/1/2010)

    Hi,

    Well the result of the DBCC command is:

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    however at the time the stored procedure was running, the result could well have been different.

    It sounds to me more like the stored procedure has an accidental cross join (ie: Inadvertant Many-to-Many Join). This will be a little tough to find... since the code doesn't actually complete, we can't use an actual-execution plan to find the cross join by row count on the arrows and the estimated-execution plan probably won't show it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 1, 2010 at 9:14 pm

    #1245184

    HI Jeff,

    As it happens, there is a cross join; eg For each of my hospitals I join a date period table which would result in ( for a year) 365 days * 25 hospitals, which is joined to another table to obtain daily inpatient (bed days) statistics.

    I can only imagine that the number of rows would be quite large.

    I will investigate the size available on the drive that holds the tempdb and log files, and consider another approach to the stored procedure, but for the time being, I guess I wll have to run the procedure once for each month ( which does work) and consolidate the results within excel.

    Thanks,

  • November 1, 2010 at 10:28 pm

    #1245192

    Scott Thornton-407727 (11/1/2010)

    HI Jeff,

    As it happens, there is a cross join; eg For each of my hospitals I join a date period table which would result in ( for a year) 365 days * 25 hospitals, which is joined to another table to obtain daily inpatient (bed days) statistics.

    I can only imagine that the number of rows would be quite large.

    I will investigate the size available on the drive that holds the tempdb and log files, and consider another approach to the stored procedure, but for the time being, I guess I wll have to run the procedure once for each month ( which does work) and consolidate the results within excel.

    Thanks,

    No need to consolidate in Excel. Just route the answers to another table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply