{"id":82015,"date":"2007-07-14T15:35:20","date_gmt":"2007-07-14T15:35:20","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73155"},"modified":"2018-12-12T13:42:17","modified_gmt":"2018-12-12T13:42:17","slug":"sys-dm_db_file_space_usage","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sys-dm_db_file_space_usage\/","title":{"rendered":"sys.dm_db_file_space_usage"},"content":{"rendered":"<p>Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis. <\/p>\n<p><b>Type: <\/b>View <\/p>\n<p><b>Data:<\/b> temporal, reflects the current state of the file usage <\/p>\n<p><b>Columns:<\/b> <\/p>\n<ul>\n<li><b>database_id<\/b> \u2013 identifies the database (relates to sys.databases)&nbsp;(NOTE: only includes tempdb in 2005)<\/li>\n<li><b>file_id<\/b> \u2013 the file identifier (relates to sys.database_files)<\/li>\n<li><b>unallocated_extent_page_count <\/b>\u2013 Total number of pages that are located on unallocated extents (8 contiguous 8K pages) that are reserved in the file but not currently allocated to objects. Note that unused pages on extents that have any active data on them will not be reflected in the total.<\/li>\n<li><b>version_store_reserved_page_count \u2013 <\/b>Number of pages that are reserved to support snapshot isolation transactions.<\/li>\n<li><b>user_object_reserved_page_count &#8211; <\/b>Number of pages reserved to user tables<b><\/b><\/li>\n<li><b>internal_object_reserved_page_count<\/b> <b>&#8211;<\/b><b> <\/b>Number of pages reserved to internal objects, such as work tables that SQL Server creates to hold intermediate results<\/li>\n<li><b>mixed_extent_page_count<\/b> \u2013 Number of extents that have pages of multiple types (user objects, version store, or internal objects, Index Allocation Map (IAM) pages, etc.)<\/li>\n<\/ul>\n<p><b>Example:<\/b> <\/p>\n<p>The following query will show the number of pages allocated to each file in your tempdb, and how much space is allocated to the various purposes, or unallocated. <\/p>\n<p>select mf.physical_name, mf.size as entire_file_page_count,<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dfsu.unallocated_extent_page_count,<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dfsu.user_object_reserved_page_count, <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dfsu.internal_object_reserved_page_count,<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dfsu.mixed_extent_page_count<br \/>from sys.dm_db_file_space_usage dfsu<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join sys.master_files as mf<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; on mf.database_id = dfsu.database_id<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and mf.file_id = dfsu.file_id<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis. Type: View Data: temporal, reflects the current state of the&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82015","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82015"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82015\/revisions"}],"predecessor-version":[{"id":82385,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82015\/revisions\/82385"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82015"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}