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

tempdb getting full Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 5:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 505, Visits: 344
My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and joins , How can I troubleshoot the same, please help.
thanx in advance.
Post #1454495
Posted Monday, May 20, 2013 6:40 AM


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 @ 11:47 AM
Points: 41,525, Visits: 34,442
Identify what is using lots of TempDB via the task_space_usage and session_space_usage DMVs, tune the offending queries to use less TempDB space.


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 #1454505
Posted Monday, May 20, 2013 7:12 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:28 AM
Points: 31, Visits: 307
Common causes include insufficient memory, large temporary tables,large queries silling into TempDB. use queries on Monitor TempDB usage or if you want to list current TempDB statements - List Current TempDB statements

Work on optimizing the queries


Jack Vamvas
sqlserver-dba.com
Post #1454519
Posted Monday, May 20, 2013 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:56 PM
Points: 237, Visits: 761
SQL Addict (5/20/2013)
My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and joins , How can I troubleshoot the same, please help.
thanx in advance.


How big is your tempdb in relation to the databases (and their largest tables) on your instance?
Post #1454590
Posted Tuesday, May 21, 2013 2:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 505, Visits: 344
I have identified the stored procedures which are causing increase in tempdb size , procedures are using cursors and creating huge temp tables, what should I do to avoid this?

thanks in advance...
Post #1454883
Posted Tuesday, May 21, 2013 2:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 505, Visits: 344
I have identified the stored procedures which are causing increase in tempdb size , procedures are using cursors and creating huge temp tables, what should I do to avoid this?

thanks in advance...
Post #1454884
Posted Tuesday, May 21, 2013 2:54 AM


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 @ 11:47 AM
Points: 41,525, Visits: 34,442
Work through the procedures, see if you can rewrite them not to use cursors and temp tables.


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 #1454885
Posted Thursday, May 23, 2013 11:48 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:28 AM
Points: 31, Visits: 307
Are you receiving any Sort warnings? these can be a clue of ORDER BY spilling into TempDB and insufficient memory. Check Statistics are up to date

Jack Vamvas
sqlserver-dba.com
Post #1456296
Posted Friday, May 24, 2013 12:19 AM


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 @ 11:47 AM
Points: 41,525, Visits: 34,442
Jack Vamvas (5/23/2013)
Are you receiving any Sort warnings? these can be a clue of ORDER BY spilling into TempDB and insufficient memory. Check Statistics are up to date


Sorts are very prone to spilling even if everything's correct in terms of stats and there's tonnes of memory. It's because of the size of the memory required to do the sort, while SQL may well have enough memory, it's not likely to toss lots of stuff out of the buffer pool just so that one process can have a few GB of workspace memory for a sort



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 #1456303
Posted Saturday, May 25, 2013 11:38 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:28 AM
Points: 31, Visits: 307
When I see Sort warnings - I check a) Statistics are up to date b) Consider Query Tuning

Jack Vamvas
sqlserver-dba.com
Post #1456843
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse