Trace to catch Temdb Blow out

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Trace to catch Temdb Blow out

Trace to catch Temdb Blow out

Rate Topic

Display Mode

Topic Options

Author

Message

nigelc

Posted Wednesday, September 19, 2012 2:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:19 PM
Points: 28, Visits: 106

Hi

we have something running that is causing tempdb to take all availible space (100 gig)and we can't figure out what it is

We believe it is being casued by our installation of a heavily customised MS CRM as the DB for this sits in this instance and is the main DB, as well as the report server DB with the only reports running being ones related to CRM

I have a trace running on it whihc captures SQL full text query, Showplan XML and SQL transaction, but nothing is jumping out.

We think that the blow out is being casued by either a query running from CRM or a report being run in CRM, we have tried to recreate the problem but have so far been unsuccessful. We have around 100 users of CRM based around the country and the tempdb is blowing out around 3-4 times a week at random times

Does anyone have any suggestions on what we could be monitoring or collecting to help find out what is causing the tempdb to do this

Thanks
Nigel

Post #1361193

anthony.green

Posted Wednesday, September 19, 2012 2:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

One way, and others may add their own but the way I would do it is to set a poll up on sys.dm_os_performance_counters for the TempDB space used metric (Free Space in tempdb (KB) ), then once it hits a certain threshold, query sys.dm_exec_requests which cross applys to sys.dm_exec_sql_text to get what was running at that particular time.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1361200

sqlnyc

Posted Wednesday, September 19, 2012 7:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:16 PM
Points: 49, Visits: 438

Does the instance in question utilize Read Committed Snapshot Isolation? This makes extensive use of TempDB.

sqlnyc

Post #1361349

nigelc

Posted Wednesday, September 19, 2012 1:44 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:19 PM
Points: 28, Visits: 106

Hi

No we do not have Read Committed Snapshot Isolation on.

I have an alert to tell me when TempDB starts to grow so I will query sys.dm_exec_requests and see what I get back

Thanks
Nigel

Post #1361601

Deque

Posted Friday, September 21, 2012 7:26 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 31, Visits: 1,118

Coincidentally, we have this exact same problem with one of our servers. We are running MS CRM v4.0 and the SQL server it exists on has this problem maybe once or twice a month where the tempDb will grow to 85 GB (the cap, in our case) and then of course that query will end.

In our case, we do not have the SSRS databases on the same system.

I was convinced it was another application that is on that SQL Server because it has some custom reports that the end users have written, but now I'm thinking otherwise.

The problem I have is that the only time I'm aware this problem exists is when SCOM sends me an alert where it couldn't allocate more space in the PRIMARY filegroup, but by the time I get that alert, it's too late because the query has already rolled back because it failed to do whatever it was doing in tempdb because of space.

I'm going to look at setting up something similar to what you have setup and see if I can determine what the problem is. Thanks for the ideas, everyone!

If you do identify the issue, can you post it here? I'm curious if we're experiencing the same issue.

Post #1362659

anthony.green

Posted Friday, September 21, 2012 7:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Even though the SSRS databases maybe on another server it could still be them causing the TempDB issue, especially if the reports use excessive sorting or #temp tables.

You can query the report server databases for reports which failed execution or which contain #temp tables order bys etc.

For quick easy viewing, you can create the SSRS diagnostic reports by Michael Davis from this link http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/69257/ which will help to find if it is reports causing the issue or not.

Post #1362663

Deque

Posted Friday, September 21, 2012 8:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 31, Visits: 1,118

In our case, I queried the ExecutionLog table in the SSRS database and we have 0 records that have a status other than rsSuccess. They don't run many SSRS reports in our case because it looks like they run 1 report on average per day and none of them have large values for TimeDataRetrieval (they're all around 1-2s).

Post #1362728

anthony.green

Posted Friday, September 21, 2012 8:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Yep, it looks like you can rule reports out of the offending TempDB growth then based on that information.

Post #1362731

nigelc

Posted Sunday, September 23, 2012 2:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:19 PM
Points: 28, Visits: 106

Interesting you have the same problem as us. I had assumed it was unique to us due to the customisations we have in our CRM application. Our vendor has not been able to shed any light on the issue either. I wonder if it is a CRM 4 issue? We are planning to upgrade to CRM 2011 and are hoping to have this fixed before we change over

Post #1363300

nigelc

Posted Sunday, September 23, 2012 8:48 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 29, 2013 5:19 PM
Points: 28, Visits: 106

Deque do you have offline users who sync back?

As using the "select * from sys.dm_exec_requests " I can see that there is an insert Into query that i believe is the one casuing the issue. it was sitting at a suspended state and then once the tempdb hits it's limited it disappears.

I have previusly thought that it was users syncing back but when i had tried to tie up the sync dates in the DB to the times that the tempdb blew out i coudn't find consistant readings that tied back, But if as you say the query has rolled back there would be nothing in the synce tables showing this.

We do not have any big imports other than ones i would do or through process's we can track so i'm sure the insert into must be related to users syncing back

Post #1363319

« Prev Topic | Next Topic »

Permissions