http://www.sqlservercentral.com/blogs/mssqlfun/2013/04/24/dmv-8-check-space-consumed-by-databasesysdm_db_file_space_usage/

Printed 2014/04/23 04:49AM

DMV-8 : Check Space consumed by Database……..sys.dm_db_file_space_usage

2013/04/24

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 :-

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



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.