May 27, 2011

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

3 Responses to "SQL Server: Calculate tempdb Space using DMV"
  1. Javin Paul said...
    May 30, 2011 at 6:22 AM

    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

  2. Suprotim Agarwal said...
    May 31, 2011 at 3:30 AM

    Thanks Javin. Glad you liked the article!

  3. Hank said...
    June 6, 2011 at 3:37 PM

    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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions