What is the Used, Free and Reserved space per DB / LOG
--**************************************************************************************** -- Used, Free and Reserved space per DB / LOG --**************************************************************************************** -- Versie: 1.0 -- Author: Theo Ekelmans -- Date: 2009-06-11 -- --**************************************************************************************** declare @sql_string nvarchar(4000) declare @DbName sysname set nocount on create table #dbinfo ( dbname sysname, dbTotalFileSizeMB varchar(15), dbDataFileSizeMB varchar(15), dbLogFileSizeMB varchar(15), dbFileSizeUnusedMB varchar(15), --dbLogSizeUnusedMB varchar(15), ReservedMB varchar(15), DataMB varchar(15), IndexSizeMB varchar(15), UnusedMB varchar(15) ) create table #temp_table( database_name nvarchar(255), log_size_MB numeric(17,2), log_space_used numeric(17,2), status char(10) ) set @sql_string = 'dbcc sqlperf(logspace)' insert into #temp_table execute sp_executesql @sql_string declare dcur cursor local fast_forward for select name from master..sysdatabases open dcur fetch next from dcur into @DbName while @@fetch_status=0 begin set @sql_string = ' declare @pages bigint ,@dbsize bigint ,@logsize bigint ,@reservedpages bigint ,@usedpages bigint select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) ,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from [' + @DbName + '].dbo.sysfiles select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size" When it.internal_type IN (202,204) Then 0 When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) from [' + @DbName + '].sys.partitions p inner join [' + @DbName + '].sys.allocation_units a on p.partition_id = a.container_id left join [' + @DbName + '].sys.internal_tables it on p.object_id = it.object_id select dbName = ''[' + @DbName + ']'' ,dbTotalFileSizeMB = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2)) ,dbDataFileSizeMB = ltrim(str((convert (dec (15,2),@dbsize) ) * 8192 / 1048576,15,2)) ,dbLogFileSizeMB = ltrim(str((convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2)) ,dbFileSizeUnusedMB = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) ) ,ReservedMB = ltrim(str(@reservedpages * 8192 / 1048576.,15,0)) ,DataMB = ltrim(str(@pages * 8192 / 1048576.,15,0)) ,IndexSizeMB = ltrim(str((@usedpages - @pages) * 8192 / 1048576.,15,0)) ,UnusedMB = ltrim(str((@reservedpages - @usedpages) * 8192 / 1048576.,15,0)) ' insert into #dbinfo(dbName, dbTotalFileSizeMB, dbDataFileSizeMB, dbLogFileSizeMB, dbFileSizeUnusedMB, ReservedMB, DataMB, IndexSizeMB, UnusedMB) execute sp_executesql @sql_string fetch next from dcur into @DbName end close dcur deallocate dcur Select * from #dbinfo left join #temp_table on dbname = '[' + database_name + ']' order by dbname asc drop table #dbinfo, #temp_table