In my SQL Server, I have 9 user databases and I would like to find out which user database is using how much resources in tempdb, which database is using more and which less, and furthermore, I would like to store the records.
While investigating the issue, I found all solutions on tempdb auto-growth tracking.
Since sys.dm_db_session_space_usage and sys.dm_db_task_space_usage DMVs don't keeps historical data, how can I get database-wise tempdb usage information?
Any suggestions are highly appreciated.
Create a Custom Logging Table: First, create a table in a database where you want to store the tempdb usage information.
Set Up Extended Events: You can use SQL Server's Extended Events to capture the tempdb usage for each database Schedule the Data Retrieval: Create a SQL Server Agent job or use another scheduling mechanism to run the data retrieval script periodically, such as every minute, hour, or as needed.