Return
-
Title
TempDB usage is increasing overtime caused by the Diagnostic Server session on monitored server. -
Description
The TempDB Spotlight usage increases over time on the monitored server.
-
Resolution
This is the problem of the source, not the Spotlight as can be read here:http://sqlity.net/en/671/deferred-deallocations-of-pages-in-tempdb/
The behavior of SQL Server may cause what can be seen on screenshots. There is more space allocated, then is the size of the TempDB, but in real, it based on incorrect numbers.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/af168419-5ad9-44eb-b377-fb5560c76a1b/tempdb-deallocation-dmdbsessionspaceusage?forum=sqldatabaseengineThat is why it is possible to see there bigger numbers than really are. The attached script doesn't give a total per session but it gives the correct sizes.The intent of the drill down is to show usage per session but unfortunately, the data source it uses isn't reliable.