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.
我的实例的tempdb变得非常庞大,占用了所有可用的磁盘空间并导致应用程序崩溃。不得不在紧急情况下重启实例。但是,我想深入调查并深入了解导致临时数据库突然变大的原因。什么是查询,进程产生了这个?有人可以帮助我提取所需的信息。我知道我不会从SQL serevr获得大量历史数据。我部署了Idera SQL诊断管理器(第三方工具)。任何帮助使用该工具将非常感激。
4 个解决方案
#1
3
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.
至于事后分析,您可以使用服务器上已安装的工具。对于将来的主动分析,您可以直接在SQL事件探查器中使用SQL跟踪,或使用SQL语句查询跟踪。
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.
您还可以使用审计工具来跟踪SQL Server实例和数据库上发生的每个事件,例如ApexSQL Comply。它还使用SQL跟踪,自动配置它们,并处理捕获的信息。它跟踪对象和数据的访问和更改,失败和成功登录,安全性更改等.ApexSQL Comply将所有捕获的信息加载到集中式存储库中。
#2
2
There are several reasons that might cause your tempdb to get very big.
有几个原因可能会导致tempdb变得非常大。
A lot of sorting – if this requires more memory than your sql server has then it will store all temp results in tempdb
很多排序 - 如果这需要比sql server更多的内存,那么它会将所有临时结果存储在tempdb中
DBCC commands – if you’re frequently running commands such as DBCC CheckDB this might be the cause. These functions store its results in temp db
DBCC命令 - 如果您经常运行DBCC CheckDB等命令,则可能是原因。这些函数将其结果存储在temp db中
Very large resultsets – these are also using temp db to run properly
非常大的结果集 - 这些也使用临时数据库正常运行
A lot of heavy transactions such as bulk inserts
很多繁重的交易,如批量插入
Check out this article for more details http://msdn.microsoft.com/en-us/library/ms176029.aspx on how to troubleshoot this.
有关如何解决此问题的详细信息,请查看此文章http://msdn.microsoft.com/en-us/library/ms176029.aspx。
#3
1
AK2, We have Idera DM tool as well. If you know the time frame around what time your tempdb was used heavily you can go to History on the Idera tool to see what query was running at that time and what lead to the server to hose... On the "Tempdb Space used OverTime" you would usually see a straight line or a graph but at the time of heavy use of tempdb there's a pike and a straight drop. Referring to this time-frame you can check into Sessions>Details too see the exact query and who was running the query.
AK2,我们也有Idera DM工具。如果你知道你的tempdb被大量使用的时间框架,你可以在Idera工具上找到历史记录,看看那时正在运行什么查询以及导致服务器软管的原因......在“Tempdb Space used OverTime上“你通常会看到一条直线或一个图形,但在大量使用tempdb的时候,有一个长矛和一个直线下降。参考此时间范围,您可以查看“会话”>“详细信息”,也可以查看确切的查询以及运行查询的人员。
In our server this happens usually when there is a long query doing lots of join. or when there is an expensive query involving in dumping into temp table / table variable. Hope this will help.
在我们的服务器中,这通常发生在长查询执行大量连接时。或者有一个昂贵的查询涉及转储到临时表/表变量。希望这会有所帮助。
#1
3
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.
至于事后分析,您可以使用服务器上已安装的工具。对于将来的主动分析,您可以直接在SQL事件探查器中使用SQL跟踪,或使用SQL语句查询跟踪。
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.
您还可以使用审计工具来跟踪SQL Server实例和数据库上发生的每个事件,例如ApexSQL Comply。它还使用SQL跟踪,自动配置它们,并处理捕获的信息。它跟踪对象和数据的访问和更改,失败和成功登录,安全性更改等.ApexSQL Comply将所有捕获的信息加载到集中式存储库中。
#2
2
There are several reasons that might cause your tempdb to get very big.
有几个原因可能会导致tempdb变得非常大。
A lot of sorting – if this requires more memory than your sql server has then it will store all temp results in tempdb
很多排序 - 如果这需要比sql server更多的内存,那么它会将所有临时结果存储在tempdb中
DBCC commands – if you’re frequently running commands such as DBCC CheckDB this might be the cause. These functions store its results in temp db
DBCC命令 - 如果您经常运行DBCC CheckDB等命令,则可能是原因。这些函数将其结果存储在temp db中
Very large resultsets – these are also using temp db to run properly
非常大的结果集 - 这些也使用临时数据库正常运行
A lot of heavy transactions such as bulk inserts
很多繁重的交易,如批量插入
Check out this article for more details http://msdn.microsoft.com/en-us/library/ms176029.aspx on how to troubleshoot this.
有关如何解决此问题的详细信息,请查看此文章http://msdn.microsoft.com/en-us/library/ms176029.aspx。
#3
1
AK2, We have Idera DM tool as well. If you know the time frame around what time your tempdb was used heavily you can go to History on the Idera tool to see what query was running at that time and what lead to the server to hose... On the "Tempdb Space used OverTime" you would usually see a straight line or a graph but at the time of heavy use of tempdb there's a pike and a straight drop. Referring to this time-frame you can check into Sessions>Details too see the exact query and who was running the query.
AK2,我们也有Idera DM工具。如果你知道你的tempdb被大量使用的时间框架,你可以在Idera工具上找到历史记录,看看那时正在运行什么查询以及导致服务器软管的原因......在“Tempdb Space used OverTime上“你通常会看到一条直线或一个图形,但在大量使用tempdb的时候,有一个长矛和一个直线下降。参考此时间范围,您可以查看“会话”>“详细信息”,也可以查看确切的查询以及运行查询的人员。
In our server this happens usually when there is a long query doing lots of join. or when there is an expensive query involving in dumping into temp table / table variable. Hope this will help.
在我们的服务器中,这通常发生在长查询执行大量连接时。或者有一个昂贵的查询涉及转储到临时表/表变量。希望这会有所帮助。