Data file sizes and free space

This script builds up on http://www.cookingsql.com/2014/12/free-space-on-drives/ and correlate the data with the location of data files and their sized.

 

/*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 
Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers

 
*/
declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

select * from @t


Leave a Reply

Your email address will not be published. Required fields are marked *