{"id":65,"date":"2014-01-02T10:02:27","date_gmt":"2014-01-02T16:02:27","guid":{"rendered":"http:\/\/blog.sqlsnee.com\/?p=65"},"modified":"2020-02-18T18:30:42","modified_gmt":"2020-02-19T00:30:42","slug":"files-and-freespace","status":"publish","type":"post","link":"https:\/\/blog.sqlsnee.com\/?p=65","title":{"rendered":"Files and Freespace"},"content":{"rendered":"<p><strong>UPDATE 2014\/02\/01<\/strong>: Script is now version 1.2 to accommodate CS collations<\/p>\n<p>Happy new year, everyone! With this post, I will be sharing a script I wrote to examine <em>how many <\/em>files you have for an instance, <em>where <\/em>they are, <em>how big <\/em>they are, and <em>how full<\/em> they are. It will also help you to reclaim some space, if that&#8217;s your goal. The script outputs 1 row per file.<\/p>\n<p><strong>DISCLAIMER:<\/strong> I am not advocating you shrink the free space out of your files.\u00a0 Generally, having free space is a good thing.\u00a0 Also, there are negative consequences of doing a file-shrink.<\/p>\n<h3><span style=\"text-decoration: underline;\">My advice<\/span><\/h3>\n<ul>\n<li><strong>Disable auto-shrink!<\/strong><\/li>\n<li>Don\u2019t use this, or any other mechanism, to otherwise routinely shrink files.<\/li>\n<li>Use it as the far exception, not the rule.<\/li>\n<li>Try to plan ahead, size your files, your drives, etc.<\/li>\n<li><strong>Understand the side-effects of shrinking a file!<\/strong>\n<ul>\n<li>This script will help give you <strong>information<\/strong> which will <em>help you make a better decision<\/em>.<\/li>\n<li>There may be non-technical factors that influence\u00a0 your decision on what to shrink or not shrink.<\/li>\n<\/ul>\n<\/li>\n<li>Sometimes life happens\u2026 you may find that, in full awareness of the consequences, shrinking a file is the best option.<\/li>\n<\/ul>\n<p>Perhaps I will blog about the effects of a fileshrink in a future post&#8230; Otherwise, there are many, many other posts on the matter.\u00a0 A quick web-search will get you started.\u00a0 I recommend <a title=\"www.brentozar.com\" href=\"http:\/\/www.brentozar.com\/archive\/2009\/08\/stop-shrinking-your-database-files-seriously-now\/\" target=\"_blank\" rel=\"noopener noreferrer\">Brent Ozar&#8217;s<\/a> or <a title=\"www.sqlskills.com\" href=\"http:\/\/www.sqlskills.com\/blogs\/paul\/why-you-should-not-shrink-your-data-files\/\" target=\"_blank\" rel=\"noopener noreferrer\">Paul Randal&#8217;s<\/a>, in particular.\u00a0 <strong>Bottom line<\/strong>: it&#8217;s really bad for performance; don&#8217;t do it unless you understand the costs and they are outweighed by the benefits.\u00a0 You&#8217;re the professional, and you&#8217;ll have to make that call, ultimately.<\/p>\n<p>And that&#8217;s all I will say about that.<\/p>\n<p>This script can also be used to snapshot space usage and trend it over time.\u00a0 There are a lot of things you probably want to remove if you&#8217;ll be doing that&#8230; but there&#8217;s a lot of useful data there.<\/p>\n<h3><span style=\"text-decoration: underline;\">Highlights of the Script<\/span><\/h3>\n<ul>\n<li>Supports Filestream\/FileTable, Database Snapshots<\/li>\n<li>Understands Filegroups<\/li>\n<li>Visual representation (careful, it is normalized)<\/li>\n<li>Generates convenient commands you can choose to run<\/li>\n<li>Displays VLF info<\/li>\n<li>Written for SQL 2012\u2026 works in 2008 with minor tweaks<\/li>\n<li>Converts sizes to human-readable format (but preserves raw bytes for sorting, filtering, etc)<\/li>\n<\/ul>\n<h3><span style=\"text-decoration: underline;\">Implementation<\/span><\/h3>\n<p>3 primary DMOs:<\/p>\n<ul>\n<li>sys.master_files\n<ul>\n<li>I prefer this DMV\u2026 contains info for all DBs in one place<\/li>\n<li>Only way to get info for offline databases<\/li>\n<li>tempdb shows its initial size here, not its current size<\/li>\n<li>For user databases, I\u2019ve seen times when this was inaccurate<\/li>\n<\/ul>\n<\/li>\n<li>sys.database_files\n<ul>\n<li>One view for each database<\/li>\n<li>Only way to get info for Filestream data<\/li>\n<\/ul>\n<\/li>\n<li>sys.dm_io_virtual_file_stats\n<ul>\n<li>DMF\u2026 can\u2019t use CROSS APPLY prior to SQL 2012<\/li>\n<li>Only way to get true size, on disk, for database snapshots (NTFS sparse file)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3><span style=\"text-decoration: underline;\">Credit where Credit is Due<\/span><\/h3>\n<p>A big thanks to Ken Simmons and Tim Ford (cited at the bottom of this post) for their blog posts on the same topic. I derived many ideas and some coding techniques from their examples.<\/p>\n<h3><span style=\"text-decoration: underline;\">Output Sample<\/span><\/h3>\n<p style=\"padding-left: 30px;\"><a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-119\" alt=\"FilesOutput\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput-1024x170.png\" width=\"1024\" height=\"170\" srcset=\"https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput-1024x170.png 1024w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput-300x50.png 300w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput.png 1264w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>Additional columns:<\/p>\n<p style=\"padding-left: 30px;\"><a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-117\" alt=\"FilesOutput2\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput2-1024x187.png\" width=\"1024\" height=\"187\" srcset=\"https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput2-1024x187.png 1024w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput2-300x54.png 300w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput2.png 1147w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>And finally:<\/p>\n<p style=\"padding-left: 30px;\"><a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-118\" alt=\"FilesOutput3\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput3.png\" width=\"431\" height=\"207\" srcset=\"https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput3.png 431w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutput3-300x144.png 300w\" sizes=\"auto, (max-width: 431px) 100vw, 431px\" \/><\/a><\/p>\n<p>The entire result set can be found here:<\/p>\n<p><a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutputFull.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-116\" alt=\"FilesOutputFull\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutputFull-1024x75.png\" width=\"1024\" height=\"75\" srcset=\"https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutputFull-1024x75.png 1024w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilesOutputFull-300x22.png 300w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<h3><span style=\"text-decoration: underline;\">The Script<\/span><\/h3>\n<pre escaped=\"true\" lang=\"tsql\">\/*\r\n* Author: Ryan Snee\r\n* Version: 1.2\r\n* Date 20140201\r\n* URL: http:\/\/sqlsnee.com\/r\/spaceused\r\n*\r\n* With inspiration from Tim Ford and Ken Simmons\r\n* Tim's Original Script: http:\/\/www.mssqltips.com\/sqlservertip\/1629\/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases\/\r\n* Ken's Original Script: http:\/\/www.mssqltips.com\/sqlservertip\/1510\/script-to-determine-free-space-to-support-shrinking-sql-server-database-files\/\r\n*\r\n* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.\r\n* License Details: http:\/\/creativecommons.org\/licenses\/by-sa\/3.0\/deeD.en_US\r\n*\r\n* LIMITATIONS\r\n*    No support for Hekaton\r\n*    Modifications needed to work on SQL 2008 R2\r\n*\r\n* Does handle:\r\n*    Snapshots\r\n*    Filestream\r\n*    Filegroups\r\n*    Offline databases (partial support)\r\n*\r\n* Grabs Virtual Log File count\r\n*\r\n***************************\r\n** Change History\r\n**************************\r\n** Version\t\tDate\t\t\tAuthor\t\tDescription\t\r\n** -------\t\t--------\t\t-------\t\t--------------------------------------------------------------------------------------------------------------------\r\n** 01.0\t\t\t2013\/12\/27\t\tRyan\t\tv1 Published\r\n** 01.1\t\t\t2014\/02\/01\t\tRyan\t\tFixed to work in a database with a CS collation\r\n\r\n*\r\n*\/\r\n\r\nSET NOCOUNT ON;\r\nDECLARE\r\n\t@LoopSafetyBefore int,\r\n\t@LoopSafetyAfter int,\r\n\t@sql nvarchar(max),\r\n\t@dbName sysname,\r\n\t@dbID int,\r\n\t@PrintMsg varchar(100),\r\n\t@Debug bit;\r\n\r\nSet @Debug=0;\r\n\r\nIF OBJECT_ID('tempdb..#DBs') IS NOT NULL\r\nBEGIN\r\n\tDROP TABLE #DBs\t\r\nEND\r\n\r\nIF OBJECT_ID('tempdb..#FileGroup') IS NOT NULL\r\nBEGIN\t\r\nDROP TABLE #FileGroup\r\nEND\r\n\r\nIF OBJECT_ID('tempdb..#TMPSPACEUSED') IS NOT NULL\r\nBEGIN\t\r\nDROP TABLE #TMPSPACEUSED\r\nEND\r\n\r\nIF OBJECT_ID('tempdb..#LogInfo') IS NOT NULL\r\nBEGIN\r\n\tDROP TABLE #LogInfo\r\nEND\r\n\r\nCREATE TABLE #TMPSPACEUSED (\r\n\t database_id\t\tint\r\n\t,DBNAME\t\t\t\tsysname\r\n\t,SPACEUSED\t\t\tFLOAT\r\n\t,size\t\t\t\tbigint\r\n\t,data_space_id\t\tint\r\n\t,FileID\t\t\t\tint\r\n\t,[type]\t\t\t\ttinyint\r\n\t,[type_desc]\t\tnvarchar(60)\r\n\t,Name\t\t\t\tsysname\r\n\t,physical_name\t\tnvarchar(260)\r\n\t,growth\t\t\t\tbigint\r\n\t,is_percent_growth\tbit\r\n\t,[max_size]\t\t\tbigint\r\n\t,is_read_only\t\tbit\r\n\t,[VLFs]\t\t\t\tint\t\t\t\tNULL\r\n\r\n);\r\n\r\nCREATE TABLE #LogInfo(\r\n\tRecoveryUnitID int,\r\n\tFileId tinyint,\r\n\tFileSize bigint,\r\n\tStartOffset bigint,\r\n\tFSeqNo int,\r\n\tStatus tinyint,\r\n\tParity tinyint,\r\n\tCreateLSN numeric(25,0)\r\n);\r\n\r\ncreate table #DBs (id int primary key not null, dbname sysname, is_distributor bit, state tinyint, user_access tinyint, is_read_only bit, source_database_id int NULL, isDone bit default 0)\r\ncreate table #FileGroup(id int identity(1,1) primary key not null, DatabaseID int not null, data_space_id int, name sysname, is_read_only bit)\r\n\r\nINSERT INTO #DBs (id, dbname, is_distributor, state, user_access, is_read_only, source_database_id, isDone)\r\nselect sdb1.database_id, sdb1.name, sdb1.is_distributor, sdb1.state, sdb1.user_access, sdb1.is_read_only, sdb1.source_database_id, 0\r\n\tfrom sys.databases sdb1\r\n\tleft join sys.databases sdb2\r\n\ton sdb1.source_database_id=sdb2.database_id\r\n\twhere 1=1\r\n\t\tAND sdb1.state=0 --Online\r\n\t\tAND sdb1.user_access =0\r\n\t\tAND sdb1.source_database_id is null OR (sdb2.state=0 and sdb2.user_access=0 )\r\n\r\nWHILE EXISTS (SELECT dbname FROM #DBs WHERE isDone=0)\r\nBEGIN\r\n\tSET @dbName= NULL\r\n\tSET @dbID= NULL\r\n\r\n\tselect @LoopSafetyBefore=count(*) FROM #DBs WHERE isDone=0;\r\n\r\n\t--Do work here\r\n\tselect top 1 @dbName= dbname, @dbID=id from #DBs WHERE isDone=0 order by dbname\r\n\tset @PrintMsg='--Scanning '+quotename(@dbName,']')\r\n\r\n\tif(@Debug=1)\r\n\tRAISERROR(@PrintMsg,0,0) WITH NOWAIT\r\n\r\n\tSET @sql=nchar(9) + N'\r\n\tUSE '+QUOTENAME(@dbName)+';\r\n\tINSERT INTO #FileGroup (DatabaseID, data_space_id, name, is_read_only)\r\n\tselect '+cast(@dbId as nvarchar(10))+', data_space_id, name, is_read_only from '+quotename(@dbName)+'.[sys].[filegroups];\r\n\r\n\tINSERT INTO #TMPSPACEUSED ([database_id], [DBNAME], [SPACEUSED], [size], [data_space_id], [FileID], [type], [type_desc], [Name], [physical_name], [growth], [is_percent_growth], [max_size], [is_read_only])\r\n\tSelect '+cast(@dbID as varchar(50))+', '''+@DBName+''' as DBName, fileproperty(Name,''SpaceUsed'') SpaceUsed, size, data_space_id, file_ID, type, type_desc, Name, physical_name, growth, is_percent_growth, [max_size], is_read_only from '+quotename(@dbName)+'.[sys].[database_files]\r\n\r\n\tTRUNCATE TABLE #LogInfo;\r\n\r\n\tinsert into #LogInfo\r\n\texec(''dbcc loginfo'');\r\n\r\n\tupdate t\r\n\tSET t.[VLFs]=\r\n\t(select count(*) from #LogInfo LI\r\n\twhere LI.FileID=t.FileID and t.database_id='+cast(@dbID as varchar(50))+')\r\n\tFROM #TMPSPACEUSED t\r\n\tWHERE [t].[database_id]='+cast(@dbID as varchar(50))+'\t\r\n\t\t\t' \r\n\r\n\tif(@Debug=1)\r\n\tprint @sql\r\n\r\n\t--Execute our statement\r\n\texec sp_executesql @sql;\t\t\t\r\n\r\n\tUPDATE TOP (1) #DBs SET isDone=1 WHERE dbname=@dbName\r\n\r\n\tselect @LoopSafetyAfter =count(*) FROM #DBs WHERE isDone=0;\r\n\r\n\tif(@LoopSafetyBefore&lt;=@LoopSafetyAfter)\r\n\tBEGIN\r\n\t\tRAISERROR('Probable infinite Loop in databases',11,1) WITH NOWAIT\r\n\t\tBREAK\r\n\tEND\r\n\r\nEND\r\n\r\n;WITH a as(\r\nSELECT \r\n\t vs.volume_mount_point AS [Drive]\r\n\t,vs.total_bytes as [DriveSize]\r\n\t,cast((cast(vs.available_bytes as decimal(32,4))\/(cast(vs.total_bytes as decimal(32,4))))*100 as decimal(18,2)) as [VolumePercentFree]\r\n\t,vs.available_bytes as [DriveFreeBytes]\r\n\t,DB.database_id,\r\n\t[DB].NAME AS [Database],\r\n\r\n\t[FG].name AS [FileGroup],\r\n\tcoalesce([D].NAME, [MF].[Name]) AS [LogicalFileName],\r\n    CASE [MF].[type]\r\n\t\tWHEN 0 THEN 'DATA'\r\n\t\tELSE [MF].[type_desc]\r\n\tEND AS [FileType]\r\n\r\n\t--master_files seems like inaccurate some times.  Doesn't reflect true tempdb size, but rather its initial size\r\n\t--database_files or io_virtual_file_stats seem best, in general.  For Filestream, data_files is the only way.\r\n\t--For snapshots, io_virtual_file_stats is the best way to get the true size, on disk\r\n\t--master_files is the only option if the database is offline, in single_user mode, or otherwise unavailable\r\n\r\n\t,CASE \r\n\t\tWHEN [MF].[type] = 2 --FileStream\r\n\t\t\tTHEN ([D].size*8192)\r\n\t\tWHEN DB.source_database_id is not null\r\n\t\t\tTHEN iovfs.size_on_disk_bytes\r\n\t\tELSE --Everything else\r\n\t\t\tcoalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))\r\n\tEND AS [FileSizeBytes]\r\n\r\n\t,DB.source_database_id\r\n\t,coalesce([D].[type], [MF].[type]) as [type]\r\n\t,[D].[type_desc]\r\n\t,CASE\r\n\t\tWHEN (DB.source_database_id IS NOT NULL) OR ([D].[type]=2) THEN cast(0 as decimal(18,2))\r\n\t\tELSE CAST(([D].size-[D].spaceused)*8192 AS DECIMAL(18,2))\r\n\tEND AS [SpaceFree]\r\n\t,CASE\r\n\t\tWHEN (DB.source_database_id IS NOT NULL) THEN cast(iovfs.size_on_disk_bytes  as decimal(18,2))\r\n\t\tWHEN ([MF].[type]=2) THEN ([D].size*8192)\r\n\t\tELSE CAST(([D].spaceused)*8192 AS DECIMAL(18,2)) \r\n\tEND AS [SpaceUsed]\r\n\r\n\t,CASE\r\n\t\tWHEN (DB.source_database_id IS NOT NULL) OR ([D].[type]=2) THEN cast(0 as decimal(18,2))\r\n\t\tELSE CAST(([D].size - D.SPACEUSED )*100.00\/([D].size) AS decimal(9,2))\r\n\tEND AS [FilePercentFree]\r\n\t,CASE \r\n\t\tWHEN (DB.source_database_id IS NOT NULL) OR ([MF].[type]=2) \r\n\t\t\tTHEN 'N\/A'\r\n\t\tWHEN coalesce([D].is_percent_growth,[MF].[is_percent_growth]) =0\r\n\t\t\tTHEN CAST((coalesce([D].[growth], [MF].[growth]) *8\/1024) AS VARCHAR(30))+' MB'\r\n\t\tWHEN coalesce([D].is_percent_growth,[MF].[is_percent_growth]) =1\r\n\t\t\tTHEN CAST(coalesce([D].[growth], [MF].[growth]) AS VARCHAR(30))+'%'\r\n\tEND\tAS [GrowAt]\r\n\r\n\t,CASE\r\n\t\tWHEN DB.source_database_id IS NOT NULL --DB is a snapshot.  Per BOL, use [D].SIZE to get max snapshot size\r\n\t\t\tTHEN (coalesce([D].[size], cast([MF].[size] as bigint))*8192)\r\n\t\tWHEN ((coalesce([D].[max_size],[MF].[max_size])=0) AND ([D].[type]&lt;&gt;2)) --Autogrow is disabled.  File is as big as it's going to get.\r\n\t\t\tTHEN coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))\r\n\t\tWHEN coalesce([D].[max_size],[MF].[max_size])=-1 --No limit\r\n\t\t\tTHEN NULL\r\n\t\tWHEN coalesce([D].[max_size],[MF].[max_size])=268435456 --Max size possible in SQL Server\r\n\t\t\tTHEN NULL\r\n\t\tELSE\t\t\t\r\n\t\t\t(coalesce([D].[max_size],[MF].[max_size])*8192)\r\n\tEND AS [MaxSizeBytes],\r\n\r\n\tCASE\r\n\t\tWHEN vs.available_bytes is null --We can't even get volume info for inaccessable databases\r\n\t\t\tTHEN null\r\n\r\n\t\tWHEN ((DB.source_database_id IS NOT NULL) and ([D].[type]&lt;&gt; 2) and (([D].[size]*8192) &gt; (vs.available_bytes + iovfs.size_on_disk_bytes))) --DB is a snapshot.  Per BOL, use [D].SIZE to get max snapshot size. Here, we've over-provisioned.  Number below will be negative.\r\n\t\t\tTHEN ((vs.available_bytes + iovfs.size_on_disk_bytes)-([D].[size]*8192))\r\n\t\tWHEN ((DB.source_database_id IS NOT NULL) and ([D].[type]&lt;&gt; 2) and (([D].[size]*8192) &lt;= (vs.available_bytes + iovfs.size_on_disk_bytes))) --DB is a snapshot. Per BOL, use [D].SIZE to get max snapshot size.\r\n\t\t\tTHEN (cast([D].[size] as bigint)*8192)\r\n\t\tWHEN (([D].[max_size]=0) AND ([D].[type]&lt;&gt;2)) --Autogrow is disabled.  File is as big as it's going to get.\r\n\t\t\tTHEN coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))\r\n\t\tWHEN (([D].[max_size])=-1 OR ([D].[max_size]=268435456)) --No limit\/Max size possible in SQL Server\r\n\t\t\tTHEN\r\n\t\t\t\tCASE\r\n\t\t\t\t\tWHEN ([D].[type]=2) --When it's a filestream file, use database_files + availble bytes on the volume\r\n\t\t\t\t\t\tTHEN\r\n\t\t\t\t\t\t\t(vs.available_bytes + ([D].size*8192))\r\n\t\t\t\tELSE\r\n\t\t\t\t\t(vs.available_bytes + coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192)))\r\n\r\n\t\t\t\tEND\r\n\t\tELSE\t\r\n\t\t\tCASE\r\n\t\t\t\tWHEN (([D].[type]&lt;&gt; 2) and (([D].[max_size]*8192) &gt; (vs.available_bytes + coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))))) --Not Filestream and the max size &gt; current size + freespace on the volume\r\n\t\t\t\t\tTHEN (vs.available_bytes + coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192)))\r\n\t\t\t\tWHEN (([D].[type]=2) AND (([D].[max_size]*8192) &gt; (vs.available_bytes + ([D].size*8192)))) --Filestream and the max size &gt; current size + freespace on the volume\r\n\t\t\t\t\tTHEN vs.available_bytes + ([D].size*8192)\r\n\t\t\t\tELSE\r\n\t\t\t\t\t([D].[max_size]*8192)\r\n\t\t\t\tEND\r\n\tEND AS [EffectiveMaxBytes],\r\n\t[DB].recovery_model_desc AS [RecoveryModel],\r\n\tcoalesce([D].PHYSICAL_NAME, [MF].[Physical_Name]) AS [PhysicalName],\r\n\tCASE \r\n\t\tWHEN [DB].name='tempdb'\t\t\t\t\tTHEN\t'--Don''t Shrink tempdb Online.  It is known to cause corruption.  Must start SQL in Single-User Mode.'\r\n\t\tWHEN [DB].name='model'\t\t\t\t\tTHEN\t'--Don''t shrink model'\r\n\t\tWHEN [DB].name='master'\t\t\t\t\tTHEN\t'--Don''t shrink master'\r\n\t\tWHEN [DB].name='msdb'\t\t\t\t\tTHEN\t'--Don''t shrink msdb'\r\n\t\tWHEN [DB].[state]&lt;&gt;0\t\t\t\t\tTHEN\t'--Database is not ''Online'''\r\n\t\tWHEN [DB].[is_read_only]=1\t\t\t\tTHEN\t'--Database is read_only and cannot be modified'\r\n\t\tWHEN DB.source_database_id IS NOT NULL\tTHEN\t'--Can''t shrink a snapshot'\r\n\t\tWHEN [FG].[is_read_only]=1\t\t\t\tTHEN\t'--Filegroup is read-only'\r\n\t\tWHEN [D].[type]=2\t\t\t\t\t\tTHEN\t'use '+quotename([DB].[Name])+';'+char(13)+char(10)+'CHECKPOINT;'+char(13)+char(10)+'GO'+char(13)+char(10)+'sp_filestream_force_garbage_collection  @dbname = '''+[DB].[Name]+'''  , @filename = '''+[D].[Name]+''';'\r\n\t\tELSE \r\n\t\t\tCASE\r\n\t\t\t\tWHEN [D].type_desc='LOG' --If it's a log file, shrink it as much as possible\r\n\t\t\t\t\tTHEN 'USE ['+[DB].name+']; DBCC SHRINKFILE (N'''+[D].name+''' , 1);'\r\n\t\t\t\tWHEN (max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]))&gt;=[D].size --If the biggest amount of space used (out of all the files in the filegroup) is larger than our file is, we are not going to GROW the file\r\n\t\t\t\t\tTHEN '--Not going to grow our file'\r\n\t\t\t\tELSE 'USE ['+[DB].name+']; DBCC SHRINKFILE (N'''+[D].name+''' , '+cast(ceiling((max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]) * 8 \/ 1024.0)) as nvarchar(100))+');'  --Otherwise, shrink all files in the filegroup to the greatest number of MB used (out of all the files in the filegroup)\r\n\t\t\tEND\r\n\tEND AS [ShrinkCmd],\r\n\r\n\tCASE \r\n\t\tWHEN [DB].name='tempdb'\t\t\t\t\tTHEN\t'--Don''t change tempdb'\r\n\t\tWHEN [DB].name='model'\t\t\t\t\tTHEN\t'--Don''t change model'\r\n\t\tWHEN [DB].name='master'\t\t\t\t\tTHEN\t'--Don''t change master'\r\n\t\tWHEN [DB].name='msdb'\t\t\t\t\tTHEN\t'--Don''t change msdb'\r\n\t\tWHEN [DB].name='dba_admin'\t\t\t\tTHEN\t'--Don''t change dba_admin'\r\n\t\tWHEN [DB].[state]&lt;&gt;0\t\t\t\t\tTHEN\t'--Database is not ''Online'''\r\n\t\tWHEN DB.source_database_id IS NOT NULL\tTHEN\t'--Can''t change a snapshot'\r\n\t\tWHEN [D].[type]=2\t\t\t\t\t\tTHEN\t'--Filestream has no autogrow'\r\n\t\tWHEN [DB].[is_read_only]=1\t\t\t\tTHEN\t'--Database is read_only and cannot be modified'\r\n\r\n\t\tELSE \r\n\t\t\tCASE [D].type\r\n\t\t\tWHEN 0\r\n\t\t\tTHEN \r\n\t\t\t\tCASE\r\n\t\t\t\t\tWHEN [D].growth=65536 AND [D].is_percent_growth=0\r\n\t\t\t\t\t\tTHEN ''\r\n\t\t\t\t\t\tELSE 'ALTER DATABASE ['+[DB].name+'] MODIFY FILE ( NAME = N'''+[D].name+''', FILEGROWTH = 524288KB )'\r\n\t\t\t\tEND\r\n\t\t\tELSE \r\n\t\t\t\tCASE WHEN [D].growth=32768 AND [D].is_percent_growth=0\r\n\t\t\t\t\tTHEN ''\r\n\t\t\t\t\tELSE 'ALTER DATABASE ['+[DB].name+'] MODIFY FILE ( NAME = N'''+[D].name+''', FILEGROWTH = 262144KB )'\r\n\t\t\t\tEND\r\n\t\t\tEND\r\n\r\n\tEND AS [AutoGrowth]\r\n\t,CASE\r\n\t\tWHEN [DB].name='tempdb'\t\t\t\t\tTHEN\t'--Don''t change tempdb'\r\n\t\tWHEN [DB].name='model'\t\t\t\t\tTHEN\t'--Don''t change model'\r\n\t\tWHEN [DB].name='master'\t\t\t\t\tTHEN\t'--Don''t change master'\r\n\t\tWHEN [DB].name='msdb'\t\t\t\t\tTHEN\t'--Don''t change msdb'\r\n\t\tWHEN [DB].[state]&lt;&gt;0\t\t\t\t\tTHEN\t'--Database is not ''Online'''\r\n\t\tWHEN DB.source_database_id IS NOT NULL\tTHEN\t'--Can''t change a snapshot'\r\n\t\tWHEN [DB].[is_read_only]=1\t\t\t\tTHEN\t'--Database is read_only and cannot be modified'\r\n\r\n\tELSE\r\n\t\tCASE \r\n\t\t\tWHEN ([DB].recovery_model=3 AND [D].FileID= min(D.FileID) OVER(PARTITION BY DB.database_id)) THEN 'ALTER DATABASE ['+[DB].name+'] SET RECOVERY FULL'\r\n\t\t\tWHEN ([DB].recovery_model=1 AND [D].FileID= min(D.FileID) OVER(Partition BY DB.database_id)) THEN 'ALTER DATABASE ['+[DB].name+'] SET RECOVERY SIMPLE'\r\n\t\t\tELSE ''\r\n\t\tEND\r\n\tEND AS [RecoveryModelCmd]\r\n\t,[DB].log_reuse_wait_desc\r\n\t,CASE\r\n\t\tWHEN [MF].[type]=1\r\n\t\t\tTHEN [D].[VLFs]\r\n\t\tELSE 0\r\n\tEND AS [VLFs]\r\n\r\n\t,CASE \r\n\tWHEN [DB].name='tempdb'\t\t\t\t\tTHEN\tcast(0 as bigint)\r\n\tWHEN [DB].name='model'\t\t\t\t\tTHEN\tcast(0 as bigint)\r\n\tWHEN [DB].name='master'\t\t\t\t\tTHEN\tcast(0 as bigint)\r\n\tWHEN [DB].name='msdb'\t\t\t\t\tTHEN\tcast(0 as bigint)\r\n\tWHEN DB.source_database_id IS NOT NULL\tTHEN\tcast(0 as bigint)\r\n\tWHEN [D].[type]=2\t\t\t\t\t\tTHEN\tcast(0 as bigint)\r\n\r\n\tWHEN [DB].[is_read_only]=1\t\t\t\tTHEN\tcast(0 as bigint)\r\n\tWHEN [DB].[state]&lt;&gt;0\t\t\t\t\tTHEN\tcast(0 as bigint)\r\n\tWHEN [FG].[is_read_only]=1\t\t\t\tTHEN\tcast(0 as bigint)\r\n\r\n\tELSE \r\n\t\tCASE\r\n\t\t\tWHEN [D].type_desc='LOG' --If it's a log file, shrink it as much as possible\r\n\t\t\t\tTHEN [D].size-[D].spaceused  --Not neccessarily, but a good guess\r\n\t\t\tWHEN (max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]))&gt;=[D].size --If the biggest amount of space used (out of all the files in the filegroup) is larger than our file is, we are not going to GROW the file\r\n\t\t\t\tTHEN 0\r\n\t\t\tELSE [D].size-(max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]))\r\n\t\tEND\r\n\tEND AS [ReclaimablePages]\r\nFROM     sys.databases [DB]\r\n\r\n\t\t\t\t inner JOIN sys.master_files [MF]\r\n           ON [DB].database_id= [MF].database_id\r\n\r\n         left JOIN #TMPSPACEUSED D\r\n           ON [DB].database_id = D.database_id\r\n\t\t   AND [D].FileID = [MF].[file_id] \r\n\t\touter APPLY sys.dm_os_volume_stats([DB].database_id, [MF].[File_ID]) vs\r\n\t\tleft join #FileGroup FG\r\n\t\ton [D].data_space_id=[FG].data_space_id\r\n\t\tand DB.database_id=[FG].DatabaseID\r\n\t\touter apply sys.dm_io_virtual_file_stats([DB].database_id, [D].[FileID]) iovfs\t\r\n\r\n), b as (\r\n\r\nselect \r\n\r\n\t[a].[Drive] as [MountPoint]\r\n\r\n\t,CASE \r\n\t\tWHEN (a.DriveSize) &gt; 1073741824000 THEN CAST(CAST(((a.DriveSize) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN (a.DriveSize) &gt; 1048576000 THEN CAST(CAST(((a.DriveSize) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST(((a.DriveSize) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND AS [DriveSize]\r\n\r\n\t,CASE\r\n\t\tWHEN ([a].[DriveFreeBytes]) &gt; 1073741824000 THEN CAST(CAST((([a].[DriveFreeBytes]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[DriveFreeBytes]) &gt; 1048576000 THEN CAST(CAST((([a].[DriveFreeBytes]) \/ 1024.0\/1024\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[DriveFreeBytes]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND AS [DriveFreeSpace]\r\n\t,nchar(9621)+replicate(nchar(9608),floor((100-VolumePercentFree)\/10))+replicate(nchar(9601),ceiling(VolumePercentFree\/10))+nchar(9615) as [VolumePercentUsed]\r\n\t,[a].[database_id] as [DatabaseID]\r\n\t,[a].[Database] as [DatabaseName]\r\n\t ,CASE\r\n\t\tWHEN (sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) &gt; 1073741824000 THEN CAST(cast((((sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) \/1024.0 \/1024.0\/1024.0\/1024.0)) as decimal(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN (sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) &gt; 1048576000 THEN CAST(cast((((sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) \/1024.0\/1024.0\/1024.0)) as decimal(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(cast((((sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) \/1024.0 \/ 1024.0)) as decimal(18,2)) AS VARCHAR(20)) + ' MB'\r\n\r\n\tEND AS [DatabaseSize]\r\n\t,[FileGroup] as [FileGroupName]\r\n\t,[LogicalFileName]\r\n\t,[FileType]\r\n\t,CASE \r\n\t\tWHEN ([a].[FileSizeBytes]) &gt; 1073741824000 THEN CAST(CAST((([a].[FileSizeBytes]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[FileSizeBytes]) &gt; 1048576000 THEN CAST(CAST((([a].[FileSizeBytes]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[FileSizeBytes]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND AS [FileSize]\r\n\r\n\t,CASE WHEN (a.source_database_id IS NOT NULL) OR ([a].[type]=2) THEN 'N\/A'\r\n\t\tWHEN ([a].[SpaceFree]) &gt; 1073741824000 THEN CAST(CAST((([a].[SpaceFree]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[SpaceFree]) &gt; 1048576000 THEN CAST(CAST((([a].[SpaceFree]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[SpaceFree]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND as [SpaceFree]\r\n\r\n\t,CASE\r\n\t\tWHEN ([a].[SpaceUsed]) &gt; 1073741824000 THEN CAST(CAST((([a].[SpaceUsed]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[SpaceUsed]) &gt; 1048576000 THEN CAST(CAST((([a].[SpaceUsed]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[SpaceUsed]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND as [SpaceUsed]\r\n\r\n\t,CASE WHEN (a.source_database_id IS NOT NULL) OR ([a].[type]=2) THEN 'N\/A'\r\n\t\tELSE nchar(9621)+replicate(nchar(9608),floor((100-FilePercentFree)\/10))+replicate(nchar(9601),ceiling(FilePercentFree\/10))+nchar(9615)\r\n\tEND as [FilePercentUsed]\r\n\t,[a].[GrowAt]\t\t\r\n\r\n\t,CASE\r\n\t\tWHEN ([a].[MaxSizeBytes]) &gt; 1073741824000 THEN CAST(CAST((([a].[MaxSizeBytes]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[MaxSizeBytes]) &gt; 1048576000 THEN CAST(CAST((([a].[MaxSizeBytes]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[MaxSizeBytes]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND as [MaxSize]\r\n\r\n\t,CASE\r\n\t\tWHEN ([a].[EffectiveMaxBytes]) &gt; 1073741824000 THEN CAST(CAST((([a].[EffectiveMaxBytes]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[EffectiveMaxBytes]) &gt; 1048576000 THEN CAST(CAST((([a].[EffectiveMaxBytes]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[EffectiveMaxBytes]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND as [EffectiveFileMax]\r\n\r\n\t,nchar(9621)+replicate(nchar(9608),floor((CAST(100*((cast(a.SpaceUsed as decimal(18,2)))\/(cast(a.EffectiveMaxBytes as decimal(18,2)))) AS decimal(18,2)))\/10))+replicate(nchar(9601),ceiling((100-(CAST(100*((cast(a.SpaceUsed as decimal(18,2)))\/(cast(a.EffectiveMaxBytes as decimal(18,2)))) AS decimal(18,2))))\/10))+nchar(9615) as [UsedPercentOfEffectiveMax]\r\n\r\n\t,CASE\r\n\t\tWHEN ([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) &gt; 1073741824000 THEN CAST(CAST((([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'\r\n\t\tWHEN ([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) &gt; 1048576000 THEN CAST(CAST((([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'\r\n\t\tELSE CAST(CAST((([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) \/ 1024.0\/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'\r\n\tEND as [AvailableSpaceToUse]\r\n\r\n\t,[a].[PhysicalName]\r\n\t,[a].[ShrinkCmd]\r\n\t,[a].[AutoGrowth]\r\n\t,[a].[RecoveryModel]\r\n\t,[a].[RecoveryModelCmd]\r\n\t,CASE\r\n\t\tWHEN [a].[type]=1\r\n\t\t\tTHEN [a].[log_reuse_wait_desc]\r\n\t\tELSE ''\r\n\tEND AS [LogReuseWait]\r\n\t,CASE\r\n\t\tWHEN (([a].[type]=1) and ([a].[VLFs] is not null))\r\n\t\t\tTHEN cast([a].[VLFs] as varchar(50))\r\n\t\tWHEN (([a].[type]=1) and ([a].[VLFs] is null))\r\n\t\t\tTHEN 'unknown'\r\n\t\tELSE ''\r\n\tEND AS [VLFCount]\r\n\t,sum(ReclaimablePages) OVER(PARTITION BY a.database_id, a.FileGroup) as [ReclaimableInTheFilegroup]\r\n\r\n\t--Raw data\r\n\t,a.DriveSize as [drive_size]\r\n\t,[a].[DriveFreeBytes] as [drive_free_bytes]\r\n\t,sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id]) as [db_size]\r\n\t,[a].[FileSizeBytes] as [file_size_bytes]\r\n\t,CASE\r\n\t\tWHEN (a.source_database_id IS NOT NULL) OR ([a].[type]=2) THEN NULL\r\n\t\t\tELSE a.SpaceFree\r\n\t\tEND as [space_free]\r\n\t,[a].SpaceUsed as [space_used]\r\n\t,[a].[MaxSizeBytes] as [max_size_bytes]\r\n\t,[a].[EffectiveMaxBytes] as [effective_max_bytes]\r\n\t,ReclaimablePages\r\n\r\nfrom a\r\n)\r\n\r\nselect \r\n\t [MountPoint]\r\n\t,[DriveSize]\r\n\t,[DriveFreeSpace]\r\n\t,[VolumePercentUsed]\r\n\t,[DatabaseID]\r\n\t,[DatabaseName]\r\n\t,[DatabaseSize]\r\n\t,[FileGroupName]\r\n\t,[LogicalFileName]\r\n\t,[FileType]\r\n\t,[FileSize]\r\n\t,[SpaceFree]\r\n\t,[SpaceUsed]\r\n\t,[FilePercentUsed]\r\n\t,[GrowAt]\r\n\t,[MaxSize]\r\n\t,[EffectiveFileMax]\r\n\t,[UsedPercentOfEffectiveMax]\r\n\t,[AvailableSpaceToUse]\r\n\t,[PhysicalName]\r\n\t,[ShrinkCmd]\r\n\t,[AutoGrowth]\r\n\t,[RecoveryModel]\r\n\t,[RecoveryModelCmd]\r\n\t,[LogReuseWait]\r\n\t,[VLFCount]\r\n\r\n\t--Other columns, not displayed, but useful for filtering\/ordering:\r\n\t\t--,[ReclaimableInTheFilegroup]\r\n\t\t--,[drive_size]\r\n\t\t--,[drive_free_bytes]\r\n\t\t--,[db_size]\r\n\t\t--,[file_size_bytes]\r\n\t\t--,[space_free]\r\n\t\t--,[space_used]\r\n\t\t--,[max_size_bytes]\r\n\t\t--,[effective_max_bytes]\r\n\t\t--,[ReclaimablePages]\r\nfrom b\r\nWHERE 1=1\r\n\t--AND MountPoint='G:\\'\r\n\t--AND DatabaseName LIKE 'AdventureWor%'\r\n\t--AND DatabaseName not in ('master','model','tempdb','msdb')\r\n\t--AND RecoveryModel = 1 --1=Full\r\n\t--AND FileGroupName='PartitionFG9'\r\n\r\nORDER BY [ReclaimableInTheFilegroup] DESC, [DatabaseID] ASC, FileGroupName ASC\r\n\r\nDROP TABLE #TMPSPACEUSED\r\nDROP TABLE #DBs\r\nDROP TABLE #FileGroup\r\nDROP TABLE #LogInfo<\/pre>\n<p>&nbsp;<\/p>\n<h3><span style=\"text-decoration: underline;\">&#8220;Filegroup Aware&#8221;<\/span><\/h3>\n<p>One of the things I&#8217;m most proud of with this script is actually easy to miss&#8230; the sort order.\u00a0 I&#8217;m not an expert on filegroups, but based on my understanding of SQL&#8217;s proportional file-fill algorithm, you want to try to keep the files in a filegroup as close as possible to the same size.\u00a0 As such, the script works as follows:<\/p>\n<ol>\n<li>It finds the file in each filegroup that&#8217;s <em>using<\/em> the most space<\/li>\n<li>The script attempts to shrink all files to that size<\/li>\n<li>No files are grown (that would defeat the purpose of freeing-up space)<\/li>\n<\/ol>\n<p>Based on how much the script estimates can be reclaimed in the <em>whole filegroup<\/em>, the results are sorted (by filegroup), with the biggest space-savers on top.\u00a0 If you aren&#8217;t using explicit filegroups, don&#8217;t worry- the files with the most free space will float to the top.<\/p>\n<p>Below is a picture from my slide-deck to help you visualize the calculation:<\/p>\n<p style=\"padding-left: 30px;\">\u00a0<a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilegroupAware.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-134\" alt=\"FilegroupAware\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilegroupAware-300x207.png\" width=\"300\" height=\"207\" srcset=\"https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilegroupAware-300x207.png 300w, https:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/FilegroupAware.png 562w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>The green space is summed for the filegroup, and the filegroup with the most &#8220;free space&#8221; (calculated as described above) will be on top.\u00a0 All the files in a filegroup will sort together.<\/p>\n<h3><span style=\"text-decoration: underline;\">Ratio Bar<\/span><\/h3>\n<p>The last thing I wanted to point out is visual ratio bar:<\/p>\n<p style=\"padding-left: 30px;\"><a href=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/VisualRatioBar.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-79\" alt=\"VisualRatioBar\" src=\"http:\/\/blog.sqlsnee.com\/wp-content\/uploads\/2013\/12\/VisualRatioBar.png\" width=\"128\" height=\"121\" \/><\/a><\/p>\n<p>It&#8217;s a normalization of a ratio, like a percentage, but with a resolution of 10 instead of 100.\u00a0 Having 100 characters resulted in too much scrolling, so I opted for just 10.\u00a0 I tried to find a word for this, but had no success.\u00a0 There&#8217;s a word for a ratio normalized to 1,000 (permillage) and one for 10,000 (permyriad), but I found nothing for 10.\u00a0 I wanted to call it a <strong><em>perdecage<\/em><\/strong>, but Google\/Bing don&#8217;t seem to agree with me that it&#8217;s a real word.<\/p>\n<p>As with any normalization, I wanted to point out that, while useful, it can be misleading.\u00a0 A file with 1MB free of 10 MB will look the same as a file with 100GB out of 1 TB.\u00a0 It&#8217;s not really a problem, but an observation.<\/p>\n<p>For anybody wondering how I did it, here&#8217;s the magic line:<\/p>\n<pre lang=\"tsql\">nchar(9621)+replicate(nchar(9608),floor((100-FilePercentFree)\/10))+replicate(nchar(9601),ceiling(FilePercentFree\/10))+nchar(9615)<\/pre>\n<p>For reference, the Unicode characters are as follows:<\/p>\n<ul>\n<li>nchar(9621): &#8220;\u2595&#8221;<\/li>\n<li>nchar(9608): &#8220;\u2588&#8221;<\/li>\n<li>nchar(9601): &#8220;\u2581&#8221;<\/li>\n<li>nchar(9615): &#8220;\u258f&#8221;<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>So we have vertical bars on either end, and 10 characters between.. each either a full bar, or a &#8220;bottom 1\/8 of a bar&#8221;, depending on the ratio.<\/p>\n<h3><span style=\"text-decoration: underline;\">Comparability with SQL 2008\/2008 R2<\/span><\/h3>\n<p>There are two changes needed for the script to run in versions prior to 2012<\/p>\n<ol>\n<li>Wrapper function- you can&#8217;t use correlated parameters for DMFs like <span style=\"color: #008000; background-color: #ffffff;\"><strong>sys.dm_io_virtual_file_stats<\/strong><\/span> prior to 2012.\u00a0 The workaround is to create a user-defined wrapper function for it.<\/li>\n<li>dbcc loginfo- A new column was added to its output in 2012, [RecoveryUnitId].\u00a0 The definition of the temp table in the script, <span style=\"color: #008080; background-color: #ffffff;\"><strong>#LogInfo<\/strong><\/span>, must be modified to remove that column if running it pre-2012.<\/li>\n<\/ol>\n<p>These changes can be found in the second script below.<\/p>\n<p>&nbsp;<\/p>\n<p>The full script can be found here: <a title=\"SpaceUsage.txt\" href=\"http:\/\/www.sqlsnee.com\/dl\/scripts\/SpaceUsage.txt\">SpaceUsage.txt<\/a>.<br \/>\nAnd a SQL 2008 version: <a title=\"SpaceUsage2008.txt\" href=\"http:\/\/www.sqlsnee.com\/dl\/scripts\/SpaceUsage2008.txt\">SpaceUsage2008.txt<\/a>.<\/p>\n<p>Please let me know if you found this script useful or if you have any suggestions to improve it.<\/p>\n<p>LLAP,<br \/>\nRyan<\/p>\n<p>A few sources I&#8217;d like to cite for contributing ideas:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.mssqltips.com\/sqlservertip\/1510\/script-to-determine-free-space-to-support-shrinking-sql-server-database-files\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/www.mssqltips.com\/sqlservertip\/1510\/script-to-determine-free-space-to-support-shrinking-sql-server-database-files<\/a><\/li>\n<li><a href=\"http:\/\/www.mssqltips.com\/sqlservertip\/1629\/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/www.mssqltips.com\/sqlservertip\/1629\/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases<\/a><\/li>\n<\/ul>\n<hr \/>\n<p>As always, please note the <em>waive of liability<\/em> published on the <a title=\"Legalese\" href=\"http:\/\/www.sqlsnee.com\/r\/legalese\">About<\/a> page.<\/p>\n<p>The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee.\u00a0 You are free to reproduce it, as outlined by the <a href=\"http:\/\/www.sqlsnee.com\/r\/cclicense\" target=\"_blank\" rel=\"noopener noreferrer\">Creative Commons Attribution-ShareAlike 3.0 Unported License<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>UPDATE 2014\/02\/01: Script is now version 1.2 to accommodate CS collations Happy new year, everyone! With this post, I will be sharing a script I wrote to examine how many files you have for an instance, where they are, how big they are, and how full they are. It will \u2026 <a class=\"continue-reading-link\" href=\"https:\/\/blog.sqlsnee.com\/?p=65\"> Continue reading <span class=\"meta-nav\">&rarr; <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,15],"tags":[11,13,14,22],"class_list":["post-65","post","type-post","status-publish","format-standard","hentry","category-sql2012","category-storage","tag-files","tag-freespace","tag-shrink","tag-storage"],"_links":{"self":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/65","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=65"}],"version-history":[{"count":10,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/65\/revisions"}],"predecessor-version":[{"id":253,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/65\/revisions\/253"}],"wp:attachment":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}