The transaction log for database 'tempdb' is full.

Question

Post reply

The transaction log for database 'tempdb' is full.

Scott Thornton-407727

SSCrazy

Points: 2271
More actions
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?

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

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 ?

Scott Thornton-407727 SSCrazy Points: 2271 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

Scott Thornton-407727 SSCrazy Points: 2271 More actions · Answer 4

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,

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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)