The Transaction log for the database 'tempdb' is full, please help!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » The Transaction log for the database 'tempdb'...

The Transaction log for the database 'tempdb' is full, please help!

Rate Topic

Display Mode

Topic Options

Author

Message

Reemi12

Posted Wednesday, October 03, 2012 9:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 8:33 AM
Points: 2, Visits: 30

Hi,

I just started using SQL server management Studio, i have a database and a table with 498591 records.

I have a sql script which remove duplicates.

Whilst running the script, 2.5 hours later i get a popup saying space on E:\ is running low.

At the end it said query completed with errors, "The Transaction log for the database 'tempdb' is full".

I checked the E:\ and it was full, only had 10mb available, E:\ is also where the tempdb file was.

What would be the best course of action? I assume i need to rerun the script again as there is still 498591 records.

Post #1367760

laurie-789651

Posted Wednesday, October 03, 2012 9:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 19, 2013 1:16 PM
Points: 272, Visits: 781

You could post the script - sounds a bit slow-running!

Post #1367779

Reemi12

Posted Wednesday, October 03, 2012 9:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 8:33 AM
Points: 2, Visits: 30

The script works as its been tested by our coding department. Is there anything else i can do?

Post #1367804

Jeff Moden

Posted Wednesday, October 03, 2012 4:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 32,893, Visits: 26,765

rahim.shabbir (10/3/2012)

The script works as its been tested by our coding department. Is there anything else i can do?

Obviously your coding department didn't test it for scalability because it's not working for you. There's likely a "Triangular Join" in the script that's generating billions of internal rows that TempDB is trying to store.

Post the code and the table definition (CREATE TABLE) or we just won't be able to help. I recommend that you take a look at the second link in my signature line to get the best help possible on this problem.

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1368079

Sameer Kapur

Posted Friday, October 05, 2012 8:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:24 AM
Points: 8, Visits: 62

Either you need to rework on your script, something like using table variable that shall take top 5000 records and delete it, put it in a while loop till all records are deleted, it seems that your script is trying to delete all the records in one go forcing tempdb to fill.

Else you may want to create another tempdb log file to another drive where ample space is present.

Post #1369062

Bhuvnesh

Posted Thursday, October 11, 2012 1:10 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

[quote]rahim.shabbir (10/3/2012)

i have a database and a table with 498591 records.I have a sql script which remove duplicates./quote] whenever you are dealing with high volume of data you need to think about batch approach (deals in chunks with data)

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1371237

« Prev Topic | Next Topic »

Permissions