log for tempdb is full

Question

log for tempdb is full

coolchaitu

SSChampion

Points: 10334
More actions
April 4, 2016 at 12:03 pm

#312912

Job failed last night due to temp log consuming full disk space. Could you please advise on fixing this. Heard,Shrinking tempdb log file is not a good idea.

Viewing 15 posts - 1 through 15 (of 17 total)

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

devontarkan SSC Journeyman Points: 96 More actions · Answer 1

Increase space in TempDB, set it to autogrow, manually increase the file size, increase space on the TempDB drive if its at capacity.

Managed VPS India :: Dedicated Server :: Windows VPS India

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

coolchaitu (4/4/2016)
Job failed last night due to temp log consuming full disk space. Could you please advise on fixing this. Heard,Shrinking tempdb log file is not a good idea.

Quick question, what is the tempdb's file configuration?

😎

Consider adding another log file on another drive if space is limited or space to the current drive.

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

coolchaitu (4/4/2016)
Job failed last night due to temp log consuming full disk space. Could you please advise on fixing this. Heard,Shrinking tempdb log file is not a good idea.

I don't know how much space that is but I'd look into the code and see where the accidental CROSS JOINs are, which are usually the result of someone not having correct criteria which, in turn, cause many-to-many joins. Step 1 is to look for anything that uses DISCTINCT. Step 2, look for GROUPBYs. Step 3, look for "monster" queries, usually have a large number of tables.

--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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Jeff Moden (4/4/2016)
coolchaitu (4/4/2016)
Job failed last night due to temp log consuming full disk space. Could you please advise on fixing this. Heard,Shrinking tempdb log file is not a good idea.
I don't know how much space that is but I'd look into the code and see where the accidental CROSS JOINs are, which are usually the result of someone not having correct criteria which, in turn, cause many-to-many joins. Step 1 is to look for anything that uses DISCTINCT. Step 2, look for GROUPBYs. Step 3, look for "monster" queries, usually have a large number of tables.

That's a s good and sound advice from Jeff, seen DISTINCT (hash aggregate) go hundreds of GB in tempdb only to return handful of rows.

😎

coolchaitu SSChampion Points: 10334 More actions · Answer 5

coolchaitu

SSChampion

Points: 10334

April 4, 2016 at 9:08 pm

#1869702

Dear Experts,

Can we shrink tempdb log file?

coolchaitu SSChampion Points: 10334 More actions · Answer 6

Dear Experts,

Also, is there a way to find what caused tempdb to become full last night?

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 7

Take a look at these links: https://dbamohsin.wordpress.com/2009/02/20/tempdb-full-%E2%80%93-how-to-clear-tempdb-log-in-sql-server-2005/ [/url]

http://www.sqlserverlogexplorer.com/error-9002-transaction-full/ [/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

coolchaitu (4/4/2016)
Dear Experts,
Can we shrink tempdb log file?

You can, but I don't know what you expect that to achieve.

The error said that the log was full, that is, the log was too small to what it needed to contain. I'm not sure why making the log smaller is being considered when the problem is that it's already too small.

Surely you'd want to make the log larger to fix the error saying it's too small.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

coolchaitu (4/4/2016)
Dear Experts,
Also, is there a way to find what caused tempdb to become full last night?

What kind of monitoring do you have on this server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 10

Dear Gail Madam

I did not understand your message. Are you saying that there is no point in shrinking tempdb log file?could you please let me know the solutions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Your error was "log file is full". Full, meaning that the entire space in the file had been used up and there wasn't space available. That means the file is too small for the workload.

Why then, when the error is telling you that the file is *too small*, do you think the solution is to make it even smaller?

I mean, if your cupboard was too small, you wouldn't suggest using an even smaller cupboard (I hope), so why are you trying to do that in SQL?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 12

We are adding space and tempdb log becomes full in few days. Please advise

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Then you need to figure out what uses TempDB and whether it's correct usage or not, and whether there are transactions left open that keep the log from being reused.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 14

Dear Gail Madam,

How to know which query caused tempdb to become full 1 hour back?