.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Total, free, and used space in tempdb in SQL Server

Posted By:Manning       Posted Date: February 12, 2011    Points: 75    Category: DataBase    URL: http://www.dotnetspark.com  

The author describes how to find the amount of space in tempdb in order to prepare for future space usage in SQL Server.
 

This article is taken from the book SQL Server DMVs in Action.  The author describes how to find the amount of space in tempdb in order to prepare for future space usage.


Get 
40% off any version of SQL Server DMVs in Action with the checkout code dnspark40. Offer is only valid through www.manning.com.


As part of the normal housekeeping tasks, it makes sense to regularly check tempdb for space usage. Recording this over a period of time will allow us to plan future space usage before it becomes a problem that needs to be fixed urgently.


The script I use to find the amount of space (total, used, and free) in tempdb is shown in listing 1.

 Listing 1 Amount of space (total, used, and free) in tempdb  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024)
AS [TotalSizeOfTempDB(MB)]
, SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024)
AS [UsedSpace (MB)]
, SUM(unallocated_extent_page_count * (8.0/1024)) AS [FreeSpace (MB)]
FROM sys.dm_db_file_space_usage


The script calculates the total size of the tempdb database space by summing up the value of the following columns, across all the files that make up tempdb:


§   user_object_reserved_page_count

§   internal_object_reserved_page_count


§   version_store_reserved_page_count

§   mixed_extent_page_count

§   unallocated_extent_page_count

The amount of used space is calculated by summing up the value of the following columns, across all the files that make up tempdb:


§   user_object_reserved_page_count

§   internal_object_reserved_page_count

§   version_store_reserved_page_count

§   mixed_extent_page_count

Finally,  the  amount  of  free  space  is  calculated  by  summing  up  the  value  of  the  column unallocated_extent_page_count, across all the files that make up tempdb.


In all cases, since the column values relate to the number of database pages, I have multiplied the value by 8 (one page is 8k in size) and divided the result by 1024 to calculate the sizes as megabytes. Mixed extends are pages that contain a mixture of different object types.
An example of the type of output for this query is given in figure 1.



Figure 1 Output showing space (total, used, and free) in tempdb.


The output shows in this example that the total size of tempdb is about 98 gigabytes (100857.5 / 1024), of which about one gigabyte is currently used and 97 gigabytes are unused.


Monitoring this output over time will allow you to determine the pattern of tempdb usage, perhaps identifying periods when it is nearing its size limit. In this case, it makes sense to pre-empt the file growth and expand it before it is needed. I have experienced several occasions where the client code (for example, a .NET client) has terminated with a timeout error because the database has been busy growing its files.


We could use the metrics relating to the amount of free space to determine at runtime if a routine that uses a lot of tempdb space should be run now or be rescheduled to run when more space is available.


It is also possible to obtain details of the size of all the files that make up the database, not just tempdb, by querying the sys.dm_os_performance_counters DMV. The output includes the total size of the data file, the log file, and the amount of the log file that has been used.
The script we use to find the total amount of space (data, log, and log used) in each of the databases on the
server is shown in listing 2.

 Listing 2 Total amount of space (data, log, and log used) by database  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT instance_name
, counter_name
, cntr_value / 1024.0 AS [Size(MB)] FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Databases' AND counter_name IN (
'Data File(s) Size (KB)'
, 'Log File(s) Size (KB)'
, 'Log File(s) Used Size (KB)')
ORDER BY instance_name, counter_name

This script queries the sys.dm_os_performance_counters DMV for information relating to database file sizes. It does this by selecting counters where the object name is equal to 'SQLServer:Databases' and the counter name is one of the following: 'Data File(s) Size (KB)',  'Log File(s) Size (KB)', or 'Log File(s) Used Size (KB)'. The output is sorted by database name (instance_name) and counter name (type of data file).
An example of the type of output for this query is given in figure 2.



Figure 2 Output showing space usage (data and log) by database

Summary


When there are server problems, tempdb is a typical area to check. Checking its free space will help to eliminate it from our concerns or require us to investigate further, perhaps drilling down on the individual sessions and tasks using it. We can determine how much space tempdb has free, using the DMV sys.dm_db_space_usage.



SQL Server DMVs in Action
EARLY ACCESS EDITION


Ian W. Stirk
MEAP Release: February 2010
Softbound print: Winter 2010 | 375 pages
ISBN: 9781935182733


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend