Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2008 R2 TEMPDB massive autogrowth suddenly. Expand / Collapse
Author
Message
Posted Sunday, January 6, 2013 9:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:48 PM
Points: 31, Visits: 124
Today while i was at lunch, TEMPDB had massive autogrows until it filled the disk it sits on. When i returned from lunch there were no complaints from users of any issues just the email alerts from SQL,

DESCRIPTION: Could not allocate space for object 'dbo.SORT temporary run storage: 143231156879360' 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.

AND

DESCRIPTION: k:\SQLDATA\tempdb.mdf: Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered.

My first knee jerk reaction was to grow the disk which i did but then when i ran the disk usage report i saw TEMPDB was empty.

There is only one user DB host on this server which is a DB supplied by a company for a program we use. But i just want to find out what caused the issue so i can try to stop it happening again. I have logged a case with the vendor but is there anything i can check. I would of expected if TEMPDB filled the server would fall over. Or is it a matter of TEMPDB filled whatever was trying to run stopped and SQL server emptied the TEMPDB file again?
Post #1403401
Posted Monday, January 7, 2013 1:22 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
Did you ask the sysadmins of the application if they were running any jobs/queries/reports during that timeframe? Is there a log for the application that will let you know what it was doing during that time?

Joie Andrew
"Since 1982"
Post #1403447
Posted Monday, January 7, 2013 1:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:34 AM
Points: 2,609, Visits: 3,903
Anything from SQL error log??
Post #1403449
Posted Monday, January 7, 2013 4:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
Sounds like whatever transaction was running stopped and the tempdb emptied back out. Normal, if unfortunate, behavior. I'd try to track down where that query came from because, if your business people are anything like the ones I've supported, it'll be back.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1403543
Posted Monday, January 7, 2013 1:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:48 PM
Points: 31, Visits: 124
Yep I've asked the app admin and report people everyone is claiming they were doing nothing.

All the users access the DB via the application front end. So general users shouldn't(wouldn't) be running queries directly against the DB the only exception is the report writers, they have a report DB restored from the nightly backup but sometimes they want full live data.

Most likely it was an glitch in the application i guess. some query the app executed got stuck and filled up tempdb?

Here are the errors in SQL error log.

--------------------------------------------------------------------------------------
Date 7/01/2013 12:57:40 PM
Log SQL Server (Current - 7/01/2013 1:00:00 PM)

Source spid2866

Message
Error: 17053, Severity: 16, State: 1.
----------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
Date 7/01/2013 12:57:40 PM
Log SQL Server (Current - 7/01/2013 1:00:00 PM)

Source spid2866

Message
k:\SQLDATA\tempdb.mdf: Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered.
-----------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
Date 7/01/2013 12:57:41 PM
Log SQL Server (Current - 7/01/2013 1:00:00 PM)

Source spid2866

Message
Error: 1105, Severity: 17, State: 2.
--------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
Date 7/01/2013 12:57:41 PM
Log SQL Server (Current - 7/01/2013 1:00:00 PM)

Source spid2866

Message
Could not allocate space for object 'dbo.SORT temporary run storage: 143231156879360' 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.
------------------------------------------------------------------------------------------
Post #1403857
Posted Monday, January 7, 2013 3:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 7,127, Visits: 12,656
The reference to dbo.SORT in the error message says to me that the growth was due to an internal work table that spilled to tempdb. Some reasons why this could have occurred:

- It could have spilled due to memory pressure that is not always present.
- It could happen if an ad hoc query returned more rows than the query author expected (cartesian product maybe) and the execution plan had a Sort operator within it.
- It could happen to a query that typically runs normally if your stats are outdated and the engine picked a bad plan with a Sort in it that it may not have picked had your stats been up to date.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1403894
Posted Monday, January 7, 2013 4:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:48 PM
Points: 31, Visits: 124
Thanks for the tips.

Memory pressure,
The server has 32GB of RAM, 28GB is assigned to SQL server as MAX memory. TempDB is presized to 10GB (app vendor best prac). The user DB that is hosted on the server is 48GB, the tempdb grew in about 3 minutes from 10GB to 20GB filling the disk. It would have to be an aweful lot of memory pressure to make SQL release 20 odd GB of memory to tempdb wouldn't it? Yes this server is excluive to SQL so SQL should only be competeing with Windows services for resources.

As for stats, the vendor gave us some update stats jobs that run every morning before the core userbase arrives. there is also a job that runs every hour that updates stats on some heavy hit tables. indexs are also rebuilt nightly.

All Jobs are from app vendor.

So what does this leave as likely outcomes?
Post #1403905
Posted Monday, January 7, 2013 4:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:48 PM
Points: 31, Visits: 124
Could your second point be caused by a "hero report writer" :)?? the server has been humming alot fine for almost 3 years then this suddenly yesterday.
Post #1403906
Posted Monday, January 7, 2013 4:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
davidwarner (1/7/2013)
So what does this leave as likely outcomes?


Someone ran a query that pulled a lot of data and put an order by on it (or something that required a sort). SQL attempted to sort, split the sort to TempDB (sorts are very prone to spilling because of the sheer volume of workspace memory they need), the sort filled TempDB, caused the query to fail.

You'd need to identify who was running what. Unless there was some auditing or tracing already in place, SQL won't have a record.

I'd speak to anyone that can run custom queries, see what they were doing.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1403912
Posted Monday, January 7, 2013 4:55 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
You might be able to check recent expensive queries to try and identify the query that caused your tempdb issue. You can try seeing if Activity Monitor in SSMS will show you the query. Grant also had a really good SQLPASS web seminar that used DMVs to get info on long running queries. I believe the code he used was:

--Long running queries
select top 10 *
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
cross apply sys.dm_exec_query_plan(plan_handle)
order by total_elapsed_time/execution_count desc

Of couse these solutions are only good if SQL has not been restarted since the TempDB errors, as the DMVs will only collect metrics from the time SQL was started.


Joie Andrew
"Since 1982"
Post #1403915
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse