SQL Server: Calculate tempdb Space using DMV

I am a huge fan of Dynamic Management Views (DMV) as they provide me the ability to look into the internals of SQL Server. I have been writing regularly about the same. Here are some of the articles

List Dynamic Management Views (DMV) by Category in SQL Server 2008
Monitor Running Processes in SQL Server 2005/2008 (written by Madhivanan)
SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them (written by Madhivanan)
Find the Most Used Stored Procedures in SQL Server
Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server

In this article, we will see how to calculate total and free space in tempdb using the sys.dm_db_file_space_usage DMV, which returns space usage information for each file in the database. tempdb is an important object in SQL Server as all databases utilize its resources. Thus it becomes important to monitor tempdb’s space usage.
Here’s the query to find the Total tempdb Size and Free space
TempDB size DMV

SELECT
SUM(unallocated_extent_page_count
+ user_object_reserved_page_count
+ internal_object_reserved_page_count
+ mixed_extent_page_count
+ version_store_reserved_page_count) * (8.0/1024.0)
AS [TotalTempDBSizeInMB]
, SUM(unallocated_extent_page_count * (8.0/1024.0))
AS [FreeTempDBSpaceInMB]
FROM sys.dm_db_file_space_usage


In the query above, we are doing a sum of columns across all tempdb files. The result is multiplied by 8 as page count values will always be a multiple of eight. The division by 1024 is to calculate the size in MB’s.

OUTPUT

image


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

3 comments:

javin paul said...

This is simply great, I have ran out of tempdb space quite a many time specially deleting some large content where I am not using truncate, having an idea of size of tempdb would be good .

Javin
10 tips on using find command in Unix

Suprotim Agarwal said...

Thanks Javin. Glad you liked the article!

Hank said...

Sorry Sir I have another way of getting it done, which gives slightly different results because it use sysfiles and sysfilegroups.

So, I need you to look at what I have historically done and advise which you think is better. Please contact for me so I can send it to you.


Hank Freeman
hfreeman@msn.com