Thursday, July 14, 2011

Query to Pull Database File Information

This query will list the name, size and location of all files for all databases. This is handy for checking and documenting database server configuration to confirm whether the server follows our recommended best practices.
set nocount on
declare @sql varchar(max), @sql2 varchar(max), @name varchar(100)
if object_id('tempdb..#t') is not null drop table #t
create table #t (DbName varchar(30), LogicalName varchar(30), FileName varchar(100), sizeMB int, UsedSpaceMB int, growthMB int, is_percent_growth bit)
set @sql = ' substring(name,1,30) as LogicalName,substring(physical_name,1,75) as FileName,'
set @sql = @sql + 'size * 8192./1024/1024 as SizeMB,sum(a.total_pages * 8192./1024/1024 ) as UsedSpaceMB, '
set @sql = @sql + 'growth * 8192./1024/1024 as growthMB, is_percent_growth from '
declare c cursor for
select name from sys.databases where database_id > 4
open c
fetch next from c into @name
while @@fetch_status=0begin
set @sql2 = @sql + @name + '.sys.database_files df left join '
set @sql2 = @sql2 + @name + '.sys.allocation_units a on df.data_space_id=a.data_space_id left join '
set @sql2 = @sql2 + @name + '.sys.partitions p on p.partition_id = a.container_id '
set @sql2 = @sql2 + 'group by df.name, df.physical_name, growth, is_percent_growth, df.size'
begin try exec ('insert #t select ''' + @name + ''' as DbName, ' + @sql2 ) end try begin catch end catch
fetch next from c into @name
end
close c
deallocate c
select * from #t


I’m sure that there are other ways to pull this data, however, in some environments your permissions may restrict you from using any method other than this to pull the data.

No comments:

Post a Comment

Resoved: Error Creating Storage Event Trigger in Azure Synapse

My client receives external files from a vendor and wants to ingest them into the Data Lake using the integration pipelines in Synapse (Syna...