In the tempdb of my instance, a huge amount is eating up all the free space on the disk and causing the applications to go down. I had to restart the instance in an emergency. However, I want to investigate and deeply understand what caused the sudden increase in the pace of db. What were the requests, the processes that caused this? Can someone help me get the information I need. I know that I do not get much historical data from SQL serevr. I have installed Idera SQL Diagnostic Manager (third-party tool). Any help with using the tool would be greatly appreciated.
Regarding postmortem analysis, you can use tools already installed on your server. For future proactive analysis, you can use SQL traces directly in SQL Profiler or query traces using SQL statements.
sys.fn_trace_gettable
sys.trace_events
You can also use an audit tool that tracks every event that occurred in an instance of SQL Server and databases, such as ApexSQL Comply . It also uses SQL traces, automatically tunes them, and processes the captured information. It tracks access and access changes to objects and data, failed and successful logins, security changes, etc. ApexSQL Comply uploads all captured information to a centralized repository.
, - tempdb .
- , sql, tempdb
DBCC - , DBCC CheckDB, . temp db
- temp db
,
http://msdn.microsoft.com/en-us/library/ms176029.aspx .
AK2, Idera DM. , tempdb , "" "", , , ... "Tempdb Space" OverTime " , tempdb . , " " > " ", .
, , . , temp table/table. , .
You can use SQL Profiler. Please try the link below.
SQL Profiler
Sql profiler