Investigate SQL server Shutdown caused by tempdb maxing out.

Question

Investigate SQL server Shutdown caused by tempdb maxing out.

LearnSQL!!!

SSC Eights!

Points: 825
More actions
November 30, 2016 at 10:05 am

#315381

SQL server got shutdown caused by tempdb maxing out, how can I trace what\who was running at the moment ? Any help would be appropriated.

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

i'd say look at the SQL server error log first. see exactly what time, and what errors occurred. the error might point to something obvious.

compare that to your job schedules; are you doing backups or re-indexing or update statistics or checkDB (or all of the above at the same time)

during that same time slot?

how about massive ETL packages? anything running before that tempdb bloat that might eat a ton of disk?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

Everything Lowell says, plus check the system_health extended event session. It captures a whole bunch of behaviors, errors, etc., that aren't normally in the log. It could help identify the culprit.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

LearnSQL!!! SSC Eights! Points: 825 More actions · Answer 3

Thanks for reply Lowell.

Yes, I have checked the Error logs and did find out the reason SQL Server was shutdown i.e.

--Date11/30/2016 8:00:55 AM

--Message

--Could not allocate space for object 'dbo.SORT temporary run storage: 140739788865536'

--in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by

--deleting unneeded files, dropping objects in the filegroup, adding additional files

--to the filegroup, or setting autogrowth on for existing files in the filegroup.

Any DMV queries you can provide me to find out, what was running at the moment .

Lowell SSC Guru Points: 323504 More actions · Answer 4

typically something like this is due to an automated process, combined with not a lot of available disk space.

the dmv's will not be any help, as they are really run time stats, not historical stop reasons.

I'd circle back at looking at scheduled jobs; failed jobs at that time would be what i'd look for first thing.

i've seen things like multiple things launching at exactly 4am(backup + reindex +updatestatistics) bloat my server like that, for example...scheduling them so they don't all launch at the same time was the easy fix

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

If I had a nickel for every client I have come across over the past two decades that had umpteen SQL Agent or other batch jobs starting at the same time I would be living on my own private island - like Hawaii!! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

Runaway processes usually cause out of space conditions.

I have never heard of SQL Server shutting down because of that though.

Super Cat SSCertifiable Points: 7332 More actions · Answer 7

I would try the default trace, if your qucik enough you may get some information of what caused the files to grow and at what time.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 8

Talib123 (12/2/2016)
I would try the default trace, if your qucik enough you may get some information of what caused the files to grow and at what time.

The system_health extended event session has everything the default trace has and more.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Super Cat SSCertifiable Points: 7332 More actions · Answer 9

OK Grant, I might finally start looking @Extend Events.

I have been avoiding it. Wish me luck.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 10

Talib123 (12/2/2016)
OK Grant, I might finally start looking @Extend Events.
I have been avoiding it. Wish me luck.

Ha!

It's not that bad. Erin Stellato has a bunch of blog posts on getting started and making the switch from Trace. Jonathon Kehayias has tons of articles on it too. I've posted a few to my blog as well. The GUI is a little bit of a mess, but once you get used to it, you're going to love all the added functionality.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Talib123 (12/2/2016)
OK Grant, I might finally start looking @Extend Events.
I have been avoiding it. Wish me luck.

Don't feel bad. I have been consulting on SQL Server for 20+ years now and I think I have used an Extended Event in anger once. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

Grant Fritchey (12/2/2016)
Talib123 (12/2/2016)
I would try the default trace, if your qucik enough you may get some information of what caused the files to grow and at what time.
The system_health extended event session has everything the default trace has and more.

Will it display the SQL code that is causing TempDB to grow?

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

LearnSQL!!! (11/30/2016)
SQL server got shutdown caused by tempdb maxing out, how can I trace what\who was running at the moment ? Any help would be appropriated.

Correct identification of the problem is 90% of the solution. I recommend that you set TempDB back to whatever "normal" is for you and then setup an alert that not only tells you that growth has reached some threshold but also identifies the likely culprits in code.

The following post seems to provide a fairly easy method to do that.

https://www.mssqltips.com/sqlservertip/3276/sql-server-alert-for-tempdb-growing-out-of-control/

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 14

Jeff Moden (12/3/2016)
Grant Fritchey (12/2/2016)
Talib123 (12/2/2016)
I would try the default trace, if your qucik enough you may get some information of what caused the files to grow and at what time.
The system_health extended event session has everything the default trace has and more.
Will it display the SQL code that is causing TempDB to grow?

Not directly. You can read more about it here. However if there were processes that had waits beyond 15 seconds on latches or 30 seconds on locks (and tempdb might be in that), it records them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning