Find TempDb Space Consumers in SQL Server

Spread the love

I often see various SQL Server DBA & professionals struggling with tempdb. As a DBA it is very common to see issues where some user processes cause tempdb data files to be 100% utilized.

In my organization, we have stability as the top priority. So, we are allowed to kill user processes causing tempdb issues whenever it happens.

To maintain this stability regarding tempdb usage, we have created a stored proc [usp_TempDbSaver] that is executed every minute. This stored procedure checks tempdb space usage, finds top sessions consuming space, and kills the top contributor session. Code for [usp_TempDbSaver] stored procedure can be obtained from my GitHub repository.

To kill top tempdb consumer session whenever tempdb usage rises beyond 80%, [usp_TempDbSaver] can be executed with the below parameter sets –

With 1 or 2 as @verbose parameter value, we can see the background logging working for us.

With @verbose value 2, we get intermediate table results as well.

Troubleshoot SQL Server tempdb database space issue and find out what sessions are consuming tempdb space.

The procedure populates following two tables with each execution –

  • dbo.tempdb_space_usage – This table shows space utilization within tempdb data and log file at specific moments.
  • dbo.tempdb_space_consumers – This table returns tempdb space consumer sessions collected for a moment if the space utilization crosses the mentioned thresholds.
Troubleshoot SQL Server tempdb database space issue and find out what sessions are consuming tempdb space.

Below is the session recording where I explained the inner working of the stored procedure along with a demo of all the 3 tempdb space consumption use cases. The below video was created on older version of tempdb saver, but is still valid for understanding the workings.

I hope this would be helpful to SQL Server DBAs out there like me.

Happy coding ๐Ÿ™‚

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.