sys.dm_db_file_space_usage DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms174412.aspx

Returns space usage information for each file in the database.

It’s most commonly used DMV to check total used & available free space in database. Before SQL Server 2012, it applicable only to the tempdb database

Key Columns :-

• database_id – identifies the database on basis on database id
• unallocated_extent_page_count – Total number of pages that are in unallocated extents (8 contiguous 8K pages) that are reserved in the file but not currently allocated to objects. Unused pages in used extents (having any active data) will not be included in this total.
• version_store_reserved_page_count – Number of pages reserved to support snapshot isolation transactions
• mixed_extent_page_count – Number of extents that have pages of multiple types (user objects, internal objects, version store, Index Allocation Map (IAM) pages, etc.)

Query 1 : Calculate total, used & unused in databases

SELECT

DB_NAME(SU.DATABASE_ID) DBNAME,

MF.PHYSICAL_NAME,

SU.ALLOCATED_EXTENT_PAGE_COUNT*8/1024 ALLOCATED_EXTENT_SIZE_MB,

SU.TOTAL_PAGE_COUNT*8/1024 TOTAL_SIZE_MB,

SU.UNALLOCATED_EXTENT_PAGE_COUNT*8/1024 UNALLOCATED_EXTENT_SIZE_MB

FROM

SYS.DM_DB_FILE_SPACE_USAGE SU

JOIN SYS.MASTER_FILES AS MF

ON MF.DATABASE_ID = SU.DATABASE_ID

AND MF.FILE_ID = SU.FILE_ID

Query 2 : Calculate Free space in TempDB

SELECT

UNALLOCATED_EXTENT_PAGE_COUNT/128 [FREESPCAE

(MB)]

FROM

SYS.DM_DB_FILE_SPACE_USAGE

Remarks

1. User required VIEW SERVER STATE permission on the server.

2. sys.dm_db_file_space_usage did not include the LDF file size whereas SP_Spaceused include LDF file size while calculating total database size.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)