{"id":594,"date":"2016-10-04T04:17:48","date_gmt":"2016-10-04T04:17:48","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=594"},"modified":"2016-10-04T04:19:32","modified_gmt":"2016-10-04T04:19:32","slug":"space","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=594","title":{"rendered":"Space"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nDisk space<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT DISTINCT vs.volume_mount_point, vs.file_system_type, \r\nvs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes\/1073741824.0) AS [Total Size (GB)],\r\nCONVERT(DECIMAL(18,2), vs.available_bytes\/1073741824.0) AS [Available Size (GB)],  \r\nCONVERT(DECIMAL(18,2), vs.available_bytes * 1. \/ vs.total_bytes * 100.) AS [Space Free %]\r\nFROM sys.master_files AS f WITH (NOLOCK)\r\nCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs \r\nORDER BY vs.volume_mount_point OPTION (RECOMPILE);\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT DISTINCT vs.volume_mount_point, vs.file_system_type,<br \/>\nvs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes\/1073741824.0) AS [Total Size (GB)],<br \/>\nCONVERT(DECIMAL(18,2), vs.available_bytes\/1073741824.0) AS [Available Size (GB)],<br \/>\nCONVERT(DECIMAL(18,2), vs.available_bytes * 1. \/ vs.total_bytes * 100.) AS [Space Free %]<br \/>\nFROM sys.master_files AS f WITH (NOLOCK)<br \/>\nCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs<br \/>\nORDER BY vs.volume_mount_point OPTION (RECOMPILE);<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nDatabase files (detailed)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT f.name AS [File Name] , f.physical_name AS [Physical Name], \r\nCAST((f.size\/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],\r\nCAST(f.size\/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)\/128.0 AS DECIMAL(15,2)) \r\nAS [Available Space In MB], \r\nCAST(f.size\/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)\/128.0 AS DECIMAL(15,2))\/CAST((f.size\/128.0) AS DECIMAL(15,2))*100 as [Free space in pct],\r\nf.[file_id], fg.name AS [Filegroup Name],\r\nf.is_percent_growth, f.growth, \r\nfg.is_default, fg.is_read_only\r\nFROM sys.database_files AS f WITH (NOLOCK) \r\nLEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)\r\nON f.data_space_id = fg.data_space_id\r\nORDER BY f.[file_id] OPTION (RECOMPILE);\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT f.name AS [File Name] , f.physical_name AS [Physical Name],<br \/>\nCAST((f.size\/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],<br \/>\nCAST(f.size\/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)\/128.0 AS DECIMAL(15,2))<br \/>\nAS [Available Space In MB],<br \/>\nCAST(f.size\/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)\/128.0 AS DECIMAL(15,2))\/CAST((f.size\/128.0) AS DECIMAL(15,2))*100 as [Free space in pct],<br \/>\nf.[file_id], fg.name AS [Filegroup Name],<br \/>\nf.is_percent_growth, f.growth,<br \/>\nfg.is_default, fg.is_read_only<br \/>\nFROM sys.database_files AS f WITH (NOLOCK)<br \/>\nLEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)<br \/>\nON f.data_space_id = fg.data_space_id<br \/>\nORDER BY f.[file_id] OPTION (RECOMPILE);<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nTotal DB space (all files combined)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT cast(SUM(f.size\/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],\r\n\tCAST(CAST(sum(FILEPROPERTY(f.name, 'SpaceUsed')) as int)\/128 AS DECIMAL(15,2)) as [Total space used],\r\n\t100-cast(CAST(CAST(sum(FILEPROPERTY(f.name, 'SpaceUsed')) as int)\/128 AS DECIMAL(15,2)) \/ cast(SUM(f.size\/128.0) AS DECIMAL(15,2))*100 as decimal(15,2)) as [Total free pct]\r\nFROM sys.database_files AS f WITH (NOLOCK) \r\nLEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)\r\nON f.data_space_id = fg.data_space_id\r\nwhere f.type = 0 OPTION (RECOMPILE);\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT cast(SUM(f.size\/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],<br \/>\n\tCAST(CAST(sum(FILEPROPERTY(f.name, 'SpaceUsed')) as int)\/128 AS DECIMAL(15,2)) as [Total space used],<br \/>\n\t100-cast(CAST(CAST(sum(FILEPROPERTY(f.name, 'SpaceUsed')) as int)\/128 AS DECIMAL(15,2)) \/ cast(SUM(f.size\/128.0) AS DECIMAL(15,2))*100 as decimal(15,2)) as [Total free pct]<br \/>\nFROM sys.database_files AS f WITH (NOLOCK)<br \/>\nLEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)<br \/>\nON f.data_space_id = fg.data_space_id<br \/>\nwhere f.type = 0 OPTION (RECOMPILE);<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nAll instance files (sorted by size)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT\r\n   mf.name, db.name, mf.physical_name, type_desc AS FileType, CAST(mf.size\/128.0 AS DECIMAL(15,2)) as file_size_mb\r\nFROM\r\n    sys.master_files mf\r\nINNER JOIN \r\n    sys.databases db ON db.database_id = mf.database_id\r\n    order by mf.size desc;\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT<br \/>\n   mf.name, db.name, mf.physical_name, type_desc AS FileType, CAST(mf.size\/128.0 AS DECIMAL(15,2)) as file_size_mb<br \/>\nFROM<br \/>\n    sys.master_files mf<br \/>\nINNER JOIN<br \/>\n    sys.databases db ON db.database_id = mf.database_id<br \/>\n    order by mf.size desc;<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Disk space SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes\/1073741824.0) AS [Total Size (GB)], CONVERT(DECIMAL(18,2), vs.available_bytes\/1073741824.0) AS [Available Size (GB)], CONVERT(DECIMAL(18,2), vs.available_bytes * 1. \/ vs.total_bytes * 100.) AS [Space Free %] FROM sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs ORDER BY vs.volume_mount_point OPTION (RECOMPILE); SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes\/1073741824.0) AS [Total [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":576,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-594","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-9A","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/594","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=594"}],"version-history":[{"count":3,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/594\/revisions"}],"predecessor-version":[{"id":598,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/594\/revisions\/598"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/576"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}