September 19, 2012 at 2:40 am
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
September 19, 2012 at 2:54 am
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.
September 19, 2012 at 7:43 am
Does the instance in question utilize Read Committed Snapshot Isolation? This makes extensive use of TempDB.
sqlnyc
September 19, 2012 at 1:44 pm
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
September 21, 2012 at 7:26 am
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.
September 21, 2012 at 7:31 am
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.
September 21, 2012 at 8:46 am
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).
September 21, 2012 at 8:48 am
Yep, it looks like you can rule reports out of the offending TempDB growth then based on that information.
September 23, 2012 at 2:49 pm
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
September 23, 2012 at 8:48 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply