Archive

Posts Tagged ‘tempdb’

Using SQL DMVs – sys.dm_db_file_space_usage

06.06.2011 No comments

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.

sys.dm_db_file_space_usage has following columns:

1. database_id – will always be 2, database id of tempdb.

2. unallocated_extent_page_count – total number of free extents in the database file

3. version_store_reserved_page_count – pages allocated for version store

4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…

5. internal_object_reserved_page_count – pages allocated for internal objects

for details about other columns returned refer BOL.

 

Let’s see it in action:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      *

FROM        sys.dm_db_file_space_usage

 

SELECT      *

INTO        #TempTable2

FROM        AdventureWorks2008R2.HumanResources.Employee

 

SELECT      *

FROM        sys.dm_db_file_space_usage

Result Set:

database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134832                        0                                 64                              0                                   136

 

(1 row(s) affected)

 

(290 row(s) affected)

 

database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134824                        0                                 72                              0                                   136

 

(1 row(s) affected)

The user_object_reserved_page_count changed to 72 from 64 after creating a temporary table.

And you can also see that the free extents available decreased from 134832 to 134824.

 

If you need to check currently available free space in tempdb, you can use below query:

SELECT      (unallocated_extent_page_count * 1.0) / 128

            AS [FreeSpcae (MB)]

FROM        sys.dm_db_file_space_usage

Result Set:

FreeSpcae (MB)

—————————————

1053.312500

 

(1 row(s) affected)

Hope This Helps!

Vishal

SQL Server has been running for …

04.05.2011 1 comment

Recently on one of our production servers, which is a two-node Active/Passive cluster.  A failover had occurred. I wanted to find the exact time when this happened. You can get this information from SQL Server Log.

Another quick way is to check the creation time of  “tempdb” database. This will be same as SQL Server Service start time since “tempdb” is recreated each time SQL Server starts.

You can check the creation time using:

SELECT create_date

FROM   sys.databases

WHERE  name = 'tempdb'

 

You can also format the output using:

 

SELECT (DATEDIFF(DAY, create_date, GETDATE()))

       AS [Days],

       ((DATEDIFF(MINUTE, create_date, GETDATE())/60)%24)

       AS [Hours],

       DATEDIFF(MINUTE, create_date, GETDATE())%60

       AS [Minutes]

FROM   sys.databases

WHERE  name = 'tempdb'

 

This returns below on my local system:

 

image

 

Hope This Helps!

Vishal