question

abhishek120684 avatar image
abhishek120684 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.
sql-server-2005tempdb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DirkHondong avatar image
DirkHondong answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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/
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.