Free space in all databases..or just one

Useful query which returns a lot of information around physical and logical disk usage for all database. A where clause can be commented out to just return for one database or a selection

CREATE TABLE #tmp_fileinfo(
    [Server] [varchar](30) NOT NULL,
    [Instance] [varchar](30) NULL,
    [DBName] [sysname] NULL, 
    [File_Name] [sysname] NOT NULL,
    [Size] [numeric](10, 2) NULL,
    [Used] [numeric](10, 2) NULL,
    [Unused] [numeric](10, 2) NULL,
    [Drive] [nvarchar](3) NULL,
    [Physical_Location] [varchar](260) NULL,
    [State] [varchar](15) NULL,
    [AutoGrow_MB] [int] NULL,
    [Percent_growth_set] [Varchar](5) NULL,
    [Percent_growth] [int] NULL,
    [Max_Size_MB] [varchar](9) NULL,
    [Timestamp] [datetime] NULL
) ON [PRIMARY]
 
declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL + 'USE'  + QUOTENAME(name) + '
insert into #tmp_fileinfo
select Convert(varchar(30),SERVERPROPERTY(''MachineName'')), Convert(varchar(30), 
SERVERPROPERTY(''InstanceName'')), ' + QUOTENAME(name,'''') + ' AS [DBName],
[name] As [File_Name]
,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2)) AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],''SpaceUsed'')/128.,2)) AS [Used]
,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],''SpaceUsed''))/128.,2)) AS [Unused]
,UPPER(SUBSTRING(physical_name, 1, 3)) AS [Drive]
,UPPER([physical_name]) AS [Physical Location]
,UPPER([state_desc]) AS [State]
,[AutoGrow_MB] = CASE WHEN [is_percent_growth] = 0 THEN [Growth]*8/1024 ELSE 0 END
,[Percent_growth_set] = CASE WHEN is_percent_growth = 1 THEN ''TRUE'' ELSE ''FALSE'' END
,[Percent_growth] = CASE WHEN [is_percent_growth] = 1 THEN [Growth] ELSE 0 END
,[MAX_SIZE_MB] = 
    CASE 
        WHEN CAST(max_size as VARCHAR(15)) = -1 THEN ''Unlimited''
        WHEN max_size = 268435456 THEN CAST(2097152 AS VARCHAR(15))
        WHEN max_size >=0 THEN CAST(([max_size]*8)/1024 AS VARCHAR(15))
    END
,getdate()
FROM [sys].[database_files] '
from sys.databases
  
execute (@SQL)
select * from #tmp_fileinfo 
where DBName = 'master'
order by DBName, File_Name
  
drop table #tmp_fileinfo
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: