{"id":179,"date":"2016-01-04T07:00:33","date_gmt":"2016-01-04T12:00:33","guid":{"rendered":"http:\/\/brentec.ca\/blog\/?p=179"},"modified":"2022-06-22T23:32:45","modified_gmt":"2022-06-23T04:32:45","slug":"an-alternative-to-sp_spaceused","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2016\/01\/an-alternative-to-sp_spaceused\/","title":{"rendered":"An alternative to sp_spaceused"},"content":{"rendered":"\r\n

The built-in procedure sp_spaceused<\/a> can provide some useful information, especially when an object name is specified; great for showing the finer details of that object. <\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n

\r\n\r\nFor example the MSreplication_options table:\r\n\r\n<\/p>\r\n\r\n\r\n

\r\nexec sp_spaceused 'MSreplication_options'\r\n<\/pre><\/div>\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
The information for the number of rows and especially index size can be useful when copying the data or determining space requirements when rebuilding indexes, respectively.<\/p>\r\n\r\n\r\n\r\n
And when an object is not specified, information about the entire database is shown, providing some good information. For example:<\/p>\r\n\r\n\r\n
\r\nexec sp_spaceused\r\n<\/pre><\/div>\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
Personally, I like to see more file information and get more granular details about a database and its size compared to the traditional, sp_spaceused.<\/p>\r\n\r\n\r\n\r\n
Yes, Microsoft has made some improvements to sp_spaceused in recent versions of SQL Server, however, I like to see the white space and a little more detail about each of the database files belonging to a database, not just the overall available space.<\/p>\r\n\r\n\r\n\r\n
Knowing where my allocated space resides will help me plan for growth. So without further adieu here is the script. <\/p>\r\n\r\n\r\n
\r\nSET NOCOUNT ON;\r\nSELECT DB_NAME() AS DBName,\r\n [name] AS [FileName],\r\n file_id AS ID,\r\n state_desc AS FileStatus,\r\n type_desc AS FileType,\r\n size \/ 128.0 AS FileSizeMB,\r\n FILEPROPERTY(name, 'SpaceUsed') \/ 128.0 AS SpaceUsedMB,\r\n size\/128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS INT)\/128.0 AS FreeSpaceMB,\r\n physical_name,\r\n CASE is_read_only\r\n  WHEN 0 THEN 'FALSE'\r\n  WHEN 1 THEN 'TRUE'\r\n END as is_read_only \r\nFROM sys.database_files;\r\n<\/pre><\/div>\r\n\r\n\r\n
\r\n\r\nAnd here is some sample output of what this script will show you:\r\n\r\n<\/p>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
\r\n\r\nNow, if you wanted to add more detail to what is already shown, you could look at the contents of sp_spaceused by doing the following…\r\n\r\n<\/p>\r\n\r\n\r\n
\r\nexec sp_helptext 'sp_spaceused'\r\n<\/pre><\/div>\r\n\r\n\r\n
…to get some further tidbits from sys.partitions<\/a>, sys.allocation_units<\/a>, and sys.internal_tables<\/a>, but I will leave that as an exercise for the reader. \ud83d\ude42<\/p>\r\n\r\n\r\n\r\n
Enjoy the script and hopefully it assists you in your day-to-day DBA functions as it does myself.<\/p>\r\n\r\n\r\n\r\n
\r\n\r\n\r\n\r\n
Edited January 12, 2016<\/strong><\/p>\r\n\r\n\r\n\r\n
I’ve modified the script to include 2 additional columns, one for used space and one for free space of each file as a percentage, which, at-a-glance allows one to see any problem areas quicker. The new script is as follows:<\/p>\r\n\r\n\r\n
\r\nSET NOCOUNT ON; \r\nSELECT DB_NAME() AS DBName,\r\n [name] AS [FileName],\r\n file_id AS ID,\r\n state_desc AS FileStatus,\r\n type_desc AS FileType,\r\n size \/ 128.0 AS FileSizeMB,\r\n FILEPROPERTY(name, 'SpaceUsed') \/ 128.0 AS SpaceUsedMB,\r\n size\/128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS INT)\/128.0 AS FreeSpaceMB,\r\n cast((FILEPROPERTY(name, 'SpaceUsed')\/128.0) \/ (size\/128.0)*100 as decimal(18,1)) as UsedSpacePercent,\r\n cast((size\/128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') as INT)\/128.0) \/ (size\/128.0)*100 as decimal(18,1)) as FreeSpacePercent,\r\n physical_name,\r\n CASE is_read_only\r\n  WHEN 0 THEN 'FALSE'\r\n  WHEN 1 THEN 'TRUE'\r\n END as is_read_only\r\nFROM sys.database_files;\r\n<\/pre><\/div>\r\n\r\n\r\n
\r\n\r\n\r\n\r\n
Edited January 13, 2016<\/strong><\/p>\r\n\r\n\r\n\r\n
I’ve further modified the script above to make it a little more readable giving the same results. This should be the final edit \ud83d\ude42<\/p>\r\n\r\n\r\n
\r\nSET NOCOUNT ON;\r\nWITH fileSizes AS \r\n(\r\n SELECT file_id,\r\n CAST(size \/ 128.0 AS DECIMAL(18,2)) AS FileSizeMB,\r\n CAST(FILEPROPERTY(name, 'SpaceUsed') \/ 128.0 AS DECIMAL(18,2)) AS SpaceUsedMB,\r\n CAST((size \/ 128.0) - (FILEPROPERTY(name, 'SpaceUsed') \/ 128.0) AS DECIMAL(18,2)) AS FreeSpaceMB FROM sys.database_files\r\n)\r\nSELECT DB_NAME() AS DBName,\r\n [name] AS [FileName],\r\n fs.file_id AS ID,\r\n state_desc AS FileStatus,\r\n type_desc AS FileType,\r\n fs.FileSizeMB,\r\n fs.SpaceUsedMB,\r\n fs.FreeSpaceMB,\r\n CAST(fs.SpaceUsedMB \/ fs.FileSizeMB * 100.0 AS DECIMAL(18,1)) AS UsedSpacePercent,\r\n CAST(fs.FreeSpaceMB \/ fs.FileSizeMB * 100.0 AS DECIMAL(18,1)) AS FreeSpacePercent,\r\n physical_name,\r\n CASE is_read_only \r\n  WHEN 0 THEN 'FALSE'\r\n  WHEN 1 THEN 'TRUE'\r\n END AS is_read_only \r\nFROM sys.database_files sdf \r\nJOIN fileSizes fs ON sdf.file_id = fs.file_id;\r\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
The built-in procedure sp_spaceused can provide some useful information, especially when an object name is specified; great for showing the finer details of that object.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[3,45],"tags":[],"_links":{"self":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/179"}],"collection":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":34,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"predecessor-version":[{"id":540,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/179\/revisions\/540"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}