asked
SQL Server 2005- Investigate what caused tempdb to grow huge
The tempdb of my instance grew huge eating up all the available disk space and causing applications to go down. Had to restart the instance in emergency. However, I want to investigate and dig deep as to what caused the temp db to grow huge all of sudden. What were the queries, processes that casued this? Can someone help me to pull the required info. I know I wont get much of historical Data from the SQL serevr. I do have the Idera SQL Diagnostic Manager(third party tool) deployed. Any help to use the tool would be really appreciated.
Since you restarted the server, any sort of evidence as to exactly what was running on SQL Server is lost. You can look at historical information on Diagnostic Manager including a history of the most active queries. Just change the time frame within the tool. For details on that go to the Idera web site.
Maintenance task like index rebuild and consistency checks like DBCC CHECKDB, version store and some times user queries can increase tempdb size, as you restarted the instance you have a little chance to find the root cause. I am not sure about SQL Diagnostic Manager that can capture historical state of the server. If not you have to wait until next time when [tempdb fills up to troubleshoot][1]. [1]:
http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
I'm not that sure right now (since I'm not sitting in front of an SQL Server to check by myself), but does SQL 2005 also have a default trace enabled? I'd say yes. And I would also assume that there's also a file rollover, e.g. there should be more than one trace file in the log folder of sql server installation. In that default trace events like auto growths are tracked, which spid caused it, which application and so on. That should help.
Just an addon to my answer: Since 2005 we have that default trace. So you should give it a try. Maybe you're lucky and get some infos. Here are some very good examples:
https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/
14 People are following this question.
