SQL Server 2005- Investigate what caused tempdb to grow huge

sql-server tempdb
AK2 picture AK2 · Feb 28, 2013 · Viewed 10.6k times · Source

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.

Answer

Milena Petrovic picture Milena Petrovic · Jul 30, 2014

As for postmortem analysis, you can use the tools already installed on your server. For future proactive analysis, you can use SQL traces directly in SQL Profiler, or query the traces using SQL statements.

sys.fn_trace_gettable

sys.trace_events

You can also use an auditing tool that tracks every event that happened on a SQL Server instance and databases, such as ApexSQL Comply. It also uses SQL traces, configures them automatically,and processes captured information. It tracks object and data access and changes, failed and successful logins, security changes, etc. ApexSQL Comply loads all captured information into a centralized repository.

enter image description here

Related questions

Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*

I can't update temp table. This is my query CREATE TABLE #temp_po(IndentID INT, OIndentDetailID INT, OD1 VARCHAR(50), OD2 VARCHAR(50), OD3 VARCHAR(50), ORD VARCHAR(50), NIndentDetailID INT, ND1 VARCHAR(50), ND2 VARCHAR(50), ND3 VARCHAR(50), NRD VARCHAR(50), Quantity DECIMAL(15,3)) INSERT INTO #temp_…

Read More
How tempDB works?

I am trying to understand the tempDB and following are the doubts popping in my mind. What is the lifetime of data in tempDB? Say a query is doing some Order By and uses tempDB for performing that. After this …

Read More
Failure SQL query insufficient disk space

Msg 1101, Level 17, State 10, Line 12 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth …

Read More