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
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
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/)