use master go SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO -- Create a stub procedure if none exists, -- this allows the procedure script to be an ALTER. if OBJECT_ID('sp_storage', 'P') is null exec ('create proc dbo.sp_storage as print ''stub'''); go Alter procedure dbo.sp_storage @db_likes nvarchar(4000) = N'' , @result_sets tinyint = 7 -- 7=all, 1=Drive Summary, 2=DB Summary, 4=File Summary + add together , @output_style tinyint = 1 -- 1 formatted strings, 0 - numerics, 2 - save to table , @sort_files_by tinyint = 0 -- 0=Drive, db, FG 1=DB, FG (PRIMARY and log first) , @filter_drive_symmary_by_db_likes BIT = 0 -- 1=Filter , @output_schema nvarchar(4000) = null output , @save_to_table sysname = null , @save_column_list nvarchar(4000) = '' output , @dbcc_checkdb_days_ago_threashold smallint = 31 , @help tinyint = 0 -- 0=no help, 1=print help /* * sp_storage is an aid to managing storage in SQL Server. It produces 3 * result sets: Drive Summary * Database Summary * File Summary * * (C) copyright 2013 Novick Software, Inc. * send feedback to anovick@NovickSoftware.com * * find documentation, newest versions and related articles at: http://www.NovickSoftware.com/Articles/sp_storage.htm * * example: exec sp_storage @db_likes='%' -- include all databases * exec sp_storage @db_likes='msdb,tempdb,pubs,northwind' * ----------------------------------------------------------------- -- create and populate the drive_summary use [your_database_goes_here] -- use the database you choose to store the drive_summary table -- if object_id('dbo.drive_summary') is not null drop table dbo.drive_summary declare @output_schema nvarchar(max) = '' exec sp_storage @db_likes='%',@output_style=0,@result_sets=1, @output_schema=@output_schema output set @output_schema = 'CREATE TABLE drive_summary ( ' + @output_schema + ' , [SERVER] nvarchar(256) default (@@SERVERNAME) , [captured_datetime] datetime default (getdate()) ) create clustered index ix_dbo_drive_summary on drive_summary (captured_datetime, drv) ' select @output_schema as [processing-instruction(create_table)] for XML PATH ('') -- stop here if you do NOT want to create the table exec (@output_schema) -- This request inserts the database summary into he dbo.database_summary table exec sp_storage @db_likes='%',@output_style=2,@result_sets=1 , @save_to_table='dbo.drive_summary' select * from dbo.drive_summary ---------------------------------------------------------------------------- -- This sequence get's the fragmental schema for the database summary, it then -- adds a few columns and creates the table use [your_database_goes_here] -- use the database you choose to store the database_summary table if object_id('dbo.database_summary') is not null drop table dbo.database_summary declare @output_schema nvarchar(max) = '' exec sp_storage @db_likes='%',@output_style=0,@result_sets=2, @output_schema=@output_schema output set @output_schema = 'CREATE TABLE database_summary ( ' + @output_schema + ' , [SERVER] nvarchar(256) default (@@SERVERNAME) , [captured_datetime] datetime default (getdate()) ) create clustered index ix_dbo_database_summary on database_summary (captured_datetime, [database]) ' select @output_schema as [processing-instruction(create_table)] for XML PATH ('') -- stop here if you do NOT want to create the table exec (@output_schema) -- This request inserts the database summary into he dbo.database_summary table exec sp_storage @db_likes='%',@output_style=2,@result_sets=2 , @save_to_table='dbo.database_summary' select * from dbo.database_summary -- ----------------------------------------------------------------- -- create and populate the file_summary use [your_database_goes_here] -- use the database you choose to store the file_summary table -- if object_id('dbo.file_summary') is not null drop table dbo.file_summary declare @output_schema nvarchar(max) = '' exec sp_storage @db_likes='%',@output_style=0,@result_sets=4, @output_schema=@output_schema output set @output_schema = 'CREATE TABLE file_summary ( ' + @output_schema + ' , [SERVER] nvarchar(256) default (@@SERVERNAME) , [captured_datetime] datetime default (getdate()) ) create clustered index ix_dbo_file_summary on file_summary (captured_datetime, [database], file_id) ' select @output_schema as [processing-instruction(create_table)] for XML PATH ('') -- stop here if you do NOT want to create the table exec (@output_schema) -- This request inserts the database summary into he dbo.database_summary table exec sp_storage @db_likes='%',@output_style=2,@result_sets=4 , @save_to_table='dbo.file_summary' select * from dbo.file_summary exec sp_storage @db_likes='%',@output_style=1, @result_sets=1 **************************************************************************/ as set nocount on set xact_abort on set ansi_warnings on declare @debug_level tinyint = 0 -- set to > 3 to get debugging info DECLARE @db_like_list TABLE (db_like NVARCHAR(4000)); DECLARE @nums TABLE (number INT PRIMARY KEY CLUSTERED); declare @num_likes smallint = 0 , @like_clause NVARCHAR(4000) =N'' , @num_db int , @sql nvarchar(max) , @msg nvarchar(2000) , @rc int = 0 if @output_schema is not null begin if @result_sets not in (1, 2, 4) begin raiserror ('Schema requested for multiple result sets', 16,1); set @rc = 50000; goto exit_proc; end; if @output_style = 1 begin raiserror ('Schema not available for @output_style=1', 16,1); set @rc = 50000; goto exit_proc; end; set @output_schema = case when @result_sets = 1 then ' Drv varchar(5) not null , avail_mb numeric(18,3) null , data_mb numeric(18,3) null , data_fls int null , log_mb numeric(18,3) null , log_fls int null , non_sql_mb numeric(18,3) null , total_mb numeric(18,3) null , RO bit null ' when @result_sets = 2 then ' [database] nvarchar(256) not null , warning_indicator char(5) null , data_fls smallint null , data_mb numeric(18,3) null , unused_mb numeric(18,3) null , data_pct numeric(18,2) null , log_fls smallint null , log_mb numeric(18,3) null , log_pct numeric(18,2) null , total_mb numeric(18,3) null , last_backup_date date null , recovery_model nvarchar(64) null , is_allow_snapshot bit null , is_read_committed_snapshot bit null , is_encrypted bit null , is_trustworthy bit null , page_verify nvarchar(64) null , [state] nvarchar(64) null , database_id int , warning nvarchar(4000) null ' else ' Drv varchar(5) not null , [database] nvarchar(255) null , [filegroup] nvarchar(776) null , logical_file nvarchar(776) null , size_mb numeric(18,3) , used_mb numeric(18,3) , [full_pct] numeric(18,2) , max_size_mb numeric(18,3) , avail_mb numeric(18,3) , nxt_grow_mb numeric(18,3) , RO bit null , [state] nvarchar(64) null , file_id int null , physical_name nvarchar(260) null ' end end if @help = 1 begin print '---------------------------------------------------------------------' print '-- sp_storage - a utility to manage SQL Server storage' print '-- (C) copyright 2013 Andrew Novick' print '-- send feedback to anovick@NovickSoftware.com' print '---------------------------------------------------------------------' print '-- Documenation and the latest version of this script can be found at' print '-- http://www.NovickSoftware.com/Articles/sp_storage.htm' print '-- You are granted rights to use this software for any purpose wih ' print '-- This software is provided for demonstaration purposes only' print '-- This software is provided without warrentee of any kind including' print '-- warrentee of fitness for use.' print '---------------------------------------------------------------------' print ' parameters' print '---------------------------------------------------------------------' print ' @db_linkes nvarhcar(4000) -- comma seperated list of LIKE operators used to specifity' print ' -- the databases to be included in the results. The default '' specifies to ' print ' -- include only the current database. ''%'' would include all databases. ' print ' -- a simple list works to include only those databases, for example ' print ' -- ''model,pubs,northwind'' would included only those databases.' print ' @result_sets tinyint -- 1=Drive Summary, 2=DB Summary 4=File Summary - Add to get multiple so 7=ALL' print ' @output_style tinyint -- 1 output formattted strings, 0 = output numerics, 2= save to table' print ' @sort_files_by tinyint -- 0=Drive, db, filegroup 1=DB, filegroup with Log and PRIMARY at top' print ' @filter_drive_summary_by_db_likes BIT -- When 1 only selected databases are reflected in the measurements in the drive summary' print ' @output_schema nvarchar(4000) -- list of columns in the output, helps with table creation' print ' @save_to_table sysname -- When @output_style=2 this is the table to save to.' print ' @dbcc_check_db_days_ago__threashold smallint = 31 -- For DB warning, days in past to warn about no DBCC CHECKDB' print ' @help tinyint -- 0=No help, 1=print help and exit' print '---------------------------------------------------------------------' goto exit_proc end if @output_style = 2 and coalesce(@save_to_table, '') = '' begin; raiserror ('when saving to a table (@output_style=2) @save_to_table must be supplied', 16, 1) ; goto exit_proc; end; if @output_style=2 begin set @save_column_list = case when @result_sets = 1 and coalesce(@save_column_list, '')='' then 'Drv, avail_mb, data_mb, data_fls, log_mb, log_fls, non_sql_mb, total_mb, RO' when @result_sets = 2 and coalesce(@save_column_list, '')='' then '[database], warning_indicator, data_fls, data_mb, unused_mb, data_pct, log_fls, log_mb, log_pct, total_mb, last_backup_date, recovery_model, is_allow_snapshot, is_read_committed_snapshot, is_encrypted, is_trustworthy, page_verify, [state], warning' when @result_sets = 4 and coalesce(@save_column_list, '')='' then ' Drv, [database], [filegroup], logical_file, size_mb, used_mb, [full_pct], max_size_mb, avail_mb, nxt_grow_mb, RO, [state], file_id, physical_name' end end if OBJECT_ID('tempdb..#db') is not null drop table #db create table #db(database_id int not null primary key clustered , db_name sysname , selected bit not null default (0)) -- start with a list of all databases insert into #db(database_id, db_name) -- these are not yet selected select database_id, name from sys.databases if @debug_level > 3 PRINT '->' + @db_likes + '<-' -- When db_likes is not specified then, if in the master database show all files if coalesce(@db_likes, '') = '' set @db_likes = case when db_name()='master' then '%' else db_name() end; -- populate the @nums table WITH digits AS (SELECT digit FROM (VALUES (0), (1), (2),(3),(4),(5),(6),(7),(8),(9)) digits(digit)) INSERT INTO @nums (number) SELECT 1+d1.digit+d2.digit*10+d3.digit*100+d4.digit*1000 n FROM digits AS d1 CROSS JOIN digits AS d2 CROSS JOIN digits d3 CROSS JOIN digits d4 where 1+d1.digit+d2.digit*10+d3.digit*100+d4.digit*1000 < 4001 ORDER BY n -- parse the @db_likes INSERT INTO @db_like_list (db_like) SELECT TOP 2000000000 LTRIM (RTRIM (SUBSTRING(@db_likes, number, CHARINDEX(',', @db_likes+',', number) - number))) AS item FROM @nums WHERE number <= LEN(@db_likes) AND SUBSTRING (',' + @db_likes, number, 1)=',' ORDER BY number - LEN (REPLACE(LEFT(@db_likes, number), ',', '')) + 1 SET @num_likes = @@ROWCOUNT SELECT @like_clause = @like_clause + 'OR db_name LIKE '''+db_like+''' ' FROM @db_like_list set @sql= 'update #db set selected = 1 where ' + substring(@like_clause , 3, 4000) exec sp_executesql @stmt=@sql if @debug_level > 3 begin PRINT '->'+@like_clause+'<-'; select * from @db_like_list; end; select @num_db = count(*) from #db where selected = 1 if @num_db = 0 begin set @msg = 'no databases selected by @db_likes'; raiserror (@msg, 11, 1); set @rc = 50000; goto exit_proc; end if @debug_level > 3 select * from #db if object_id('tempdb..#file_summary') is not null drop table #file_summary CREATE TABLE #file_summary( preserve_order int not null default (1), drv char(1) not null, database_id int NOT NULL, [database] sysname null, [file_id] int NOT NULL, [type] tinyint NOT NULL, type_desc nvarchar(60) NULL, data_space_id int NOT NULL, [filegroup] sysname null, logical_file sysname NOT NULL, physical_name nvarchar(260) NOT NULL, state tinyint NULL, state_desc nvarchar(60) NULL, size_mb numeric(18,3) NOT NULL, size_formatted varchar(16) null, max_size_mb numeric(18,3) NOT NULL, max_size_formatted varchar(16), growth numeric(18,3) NOT NULL, is_percent_growth bit NOT NULL, RO bit NOT NULL, read_only_formatted char(2) null, volume_mount_point nvarchar(256) NULL, volume_id nvarchar(256) NULL, logical_volume_name nvarchar(256) NULL, file_system_type nvarchar(256) NULL, drive_total_mb numeric(18,3) NOT NULL, avail_mb numeric(18,3) NOT NULL, avail_mb_formatted varchar(16) null, volume_is_read_only tinyint NULL, volume_is_compressed tinyint NULL, nxt_grow_mb as convert(numeric(18,3), case when is_percent_growth=1 then size_mb * growth else growth/128.0 end ), next_file_growth_status varchar(16) NULL, used_mb numeric(18,3) null, used_formatted varchar(16), full_pct numeric(18,2) null, full_pct_formatted varchar(16) null ) insert into #file_summary ( drv, database_id, [database], [file_id], [type], type_desc, data_space_id, logical_file, physical_name, [state], state_desc, size_mb, max_size_mb, growth, is_percent_growth, RO, volume_mount_point, volume_id, logical_volume_name, file_system_type, drive_total_mb, avail_mb, volume_is_read_only, volume_is_compressed) SELECT UPPER(left(physical_name, 1)) drv , mf.database_id , DB_NAME(mf.database_id) [database] , mf.[file_id] , mf.[type] , mf.type_desc , mf.data_space_id , mf.name , mf.physical_name , mf.[state] , mf.state_desc , mf.size/128.0 size_mb , case when mf.max_size = -1 then -1 else mf.max_size/128.0 END max_size_mb , mf.growth , mf.is_percent_growth , mf.is_read_only , vs.volume_mount_point , vs.volume_id , logical_volume_name , file_system_type , convert(numeric(18,3), vs.total_bytes/1048576) drive_total_mb , vs.available_bytes/1048576 avail_mb , vs.is_read_only , vs.is_compressed from sys.master_files mf cross apply sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs where mf.database_id != 2 /* for tempdb master_files holds the startup size */ and (@filter_drive_symmary_by_db_likes = 0 or mf.database_id in (select database_id from #db where selected = 1) ) union all SELECT UPPER(left(physical_name, 1)) drv , 2 , DB_NAME(2) [database] , mf.[file_id] , mf.[type] , mf.type_desc , mf.data_space_id , mf.name , mf.physical_name , mf.[state] , mf.state_desc , mf.size/128.0 size_mb , case when mf.max_size = -1 then -1 else mf.max_size/128.0 END max_size_mb , mf.growth , mf.is_percent_growth , mf.is_read_only , vs.volume_mount_point , vs.volume_id , logical_volume_name , file_system_type , convert(numeric(18,3), vs.total_bytes/1048576) drive_total_mb , vs.available_bytes/1048576 avail_mb , vs.is_read_only , vs.is_compressed from tempdb.sys.database_files mf cross apply sys.dm_os_volume_stats(2, mf.file_id) vs where (@filter_drive_symmary_by_db_likes = 0 or 2 in (select database_id from #db where selected = 1) ) update m set next_file_growth_status = case when volume_is_read_only=1 then 'RO' when max_size_mb > 0 and size_mb + nxt_grow_mb > max_size_mb then 'Max Size' when size_mb + nxt_grow_mb > avail_mb then 'No Room' else ' ' end from #file_summary m if @debug_level > 3 select * from #file_summary if object_id('tempdb..#drv_summary') is not null drop table #drv_summary CREATE TABLE #drv_summary( presentation_order int null default (1), drv char(5) not null, num_databases int null, num_databases_formatted varchar(10) null, data_fls int null, data_fls_formatted varchar(10) null, log_fls int null, log_fls_formatted varchar(10) null, data_mb numeric(18,3) null, data_mb_formatted varchar(16) null, log_mb numeric(18,3) null, log_mb_formatted varchar(16) null, non_sql_mb numeric(18,3), non_sql_mb_formatted varchar(16) null, total_mb numeric(18,3) null, total_formatted varchar(16) null, avail_mb numeric(18,3) null, avail_mb_formatted varchar(16) null, RO bit null default (0), read_only_formatted char(2) null, volume_is_compressed bit null, file_system_type nvarchar(256) NULL, logical_volume_name nvarchar(256) NULL, ) insert into #drv_summary ( drv, num_databases, data_fls, log_fls, data_mb, log_mb, total_mb, avail_mb, RO, volume_is_compressed, file_system_type, logical_volume_name ) select upper(drv) drv , count(distinct(database_id)) num_databases , count (file_id) data_fls , sum(case when type=1 then 1 else 0 end) log_fls , sum(case when type=0 then size_mb else 0 end) data_mb , sum(case when type=1 then size_mb else 0 end) log_mb , max(drive_total_mb) total_mb , max(avail_mb) avail_mb , max(volume_is_read_only) RO , max(volume_is_compressed) volume_is_compressed , max(file_system_type) file_system_type , max(logical_volume_name) logical_volume_name from #file_summary group by drv if @debug_level > 3 select * from #drv_summary declare db_cursor cursor fast_forward read_only for select database_id, [db_name] from #db where @filter_drive_symmary_by_db_likes = 0 or selected = 1 declare @database_id int , @db_name sysname declare @base_trancount int open db_cursor fetch db_cursor into @database_id, @db_name while @@FETCH_STATUS=0 begin begin try set @base_trancount = @@TRANCOUNT -- begin tran sp_storage_inner_tran set @SQL = ' WITH fg AS ( SELECT fg.filegroup , fg.data_space_id , fg.num_files , CONVERT(NUMERIC(18,3), fg.file_size_mb) file_size_mb , CONVERT(NUMERIC(18,3), au.total_mb) used_mb , CONVERT(NUMERIC(18,2), 100.0 * total_mb/fg.file_size_mb) pct_full FROM (SELECT fg.NAME [filegroup] , fg.data_space_id , COUNT(df.data_space_id) num_files , CONVERT(NUMERIC(18,3), SUM(size/128.0)) file_size_mb FROM [].sys.filegroups fg LEFT OUTER JOIN [].sys.database_files df ON fg.data_space_id = df.data_space_id WHERE fg.type = ''FG'' GROUP BY fg.name, fg.data_space_id HAVING count(df.data_space_id) >0 ) fg LEFT OUTER JOIN ( SELECT au.data_space_id , SUM(CAST(au.total_pages/128.0 AS NUMERIC(18,3))) total_mb , SUM(CAST(au.data_pages/128.0 AS NUMERIC(18,3))) data_mb FROM [].sys.allocation_units au GROUP BY au.data_space_id ) au ON fg.data_space_id = au.data_space_id ) update m set m.filegroup = fg.filegroup , m.full_pct = coalesce(fg.pct_full, 0.0) , m.used_mb = (coalesce(fg.pct_full, 0.0)/100.0) * size_mb /* fg.used_mb*/ from #file_summary m inner join fg on m.data_space_id = fg.data_space_id where m.database_id = @database_id ' set @sql = replace (@sql, '', @db_name) exec sp_executesql @stmt = @sql , @parameters= N'@database_id int' , @database_id = @database_id -- commit tran sp_storage_inner_tran end try begin catch print 'error retrieving detailed information on database - ' + @db_name + ' - ' + error_message(); end catch fetch db_cursor into @database_id, @db_name end -- while close db_cursor deallocate db_cursor -- Fill in the Filegroup for the logs UPDATE #file_summary SET [filegroup] = 'Log' WHERE type_desc = 'LOG' -- fill in the percent full for the log files, detail is available update m set m.full_pct = pc.cntr_value , m.used_mb = (pc.cntr_value/100.0) * size_mb from #file_summary m inner join sys.dm_os_performance_counters pc on db_name(m.database_id) = pc.instance_name WHERE m.type_desc = 'LOG' and pc.counter_name LIKE 'Percent Log Used%' -- dmv with percent used by file but only for tempdb update m set m.used_mb = (fsu.version_store_reserved_page_count + fsu.user_object_reserved_page_count + fsu.internal_object_reserved_page_count + fsu.mixed_extent_page_count) / 128.0 -- convert to megabytes from #file_summary m inner join sys.dm_db_file_space_usage fsu on m.database_id = fsu.database_id and m.file_id = fsu.file_id where m.database_id=2 -- There may be drives that have no files yet. Attempt to discover them using -- drive_info or xp_fixeddrives declare @drive_info_worked bit = 0 --- not yet begin try if exists (select * from master.sys.objects where [name] = 'drive_info' and schema_id = 1 and type = 'FT' ) begin insert into #drv_summary (drv, total_mb, avail_mb, data_mb, data_fls, log_mb, log_fls) select letter, total_mb, free_mb , 0, 0, 0, 0 from master.dbo.drive_info() di where di.letter not in (select drv from #drv_summary) and di.[type] = 'Fixed' set @drive_info_worked=1 end end try begin catch print 'error using drive_info - ' + error_message(); set @drive_info_worked = 0 -- did not end catch if @drive_info_worked = 0 begin begin try if object_id ('tempdb..#xpfixeddrives') is not null drop table #xpfixeddrives create table #xpfixeddrives (drv char(1), mb_free int) insert into #xpfixeddrives (drv, mb_free) exec master..xp_fixeddrives insert into #drv_summary (drv, avail_mb) select Drv, mb_free from #xpfixeddrives where Drv not in (Select drv from #drv_summary) end try begin catch print 'Error attempting to use master..xp_fixeddrives - ' + error_message(); end catch END -- if @drive_info_worked = 0 -- Update the non-sql usage update #drv_summary set non_sql_mb = total_mb - (coalesce(data_mb, 0) +coalesce(log_mb, 0) + coalesce(avail_mb, 0) ) if object_id('tempdb..#db_summary') is not null drop table #db_summary -- Now create the DB Summary CREATE TABLE #db_summary( presentation_order int null default (1), [database] sysname not null, [database_id] int not null, warning_indicator char(1) null default (' '), data_fls int null, data_fls_formatted varchar(10) null, log_fls int null, log_fls_formatted varchar(10) null, data_mb numeric(18,3) null, data_mb_formatted varchar(16) null, unused_mb numeric(18,3) null, unused_formatted varchar(16) null, data_pct float null, data_pct_formatted varchar(16) null, log_mb numeric(18,3) null, log_mb_formatted varchar(16) null, log_pct float null, log_pct_formatted varchar(16) null, total_mb numeric(18,3) null, total_formatted varchar(16) null, is_read_only bit null, read_only_formatted char(2) null, last_backup_date datetime null, last_backup_date_formatted varchar(16) null, recovery_model varchar(16), is_allow_snapshot bit null, is_allow_snapshot_formatted char(1) null, is_read_committed_snapshot bit null, is_read_committed_snapshot_formatted char(1) null, is_encrypted bit null, is_encrypted_formatted char(1) null, is_trustworthy bit null, is_trustworthy_formatted varchar(16) null, [page_verify] varchar(16) null, [state] nvarchar(64) null, warning nvarchar(256) default ('') ) insert into #db_summary ( [database], [database_id], data_fls, log_fls, data_mb, -- unused_mb, -- full_pct, log_mb, log_pct, unused_mb, data_pct, total_mb ) select db_name(m.database_id) , m.database_id , sum(case when type=0 then 1 else 0 end) data_fls , sum(case when type=1 then 1 else 0 end) log_fls , sum(case when type=0 then size_mb else 0 end) data_mb , sum(case when type=1 then size_mb else 0 end) log_mb , sum(full_pct * case when type=1 then size_mb else 0 end) / sum(case when type=1 then size_mb else 0 end) log_percent_full , sum(case when type=0 then size_mb else 0 end) - sum(case when type=0 then used_mb else 0 end) unused_mb , 100.0 * sum(case when type=0 then used_mb else 0 end) / sum(case when type=0 then size_mb else 0 end) full_pct , sum(size_mb) total_mb from #file_summary m inner join #db d on m.database_id = d.database_id where d.selected=1 group by m.database_id -- update the #db_summary with information from other sources. update s set s.is_read_only = d.is_read_only , s.is_read_committed_snapshot = d.is_read_committed_snapshot_on , s.is_allow_snapshot = case when d.snapshot_isolation_state in (1, 3) then 1 else 0 end , s.is_encrypted = d.is_encrypted , s.recovery_model = d.recovery_model_desc , s.[state] = d.state_desc + case when d.user_access_desc='SINGLE_USER' then '(Sng User)' else '' end , s.[page_verify] = case when d.page_verify_option_desc like 'TORN%' then 'TORN_PAGE' else page_verify_option_desc end , s.is_trustworthy = d.is_trustworthy_on from #db_summary s inner join master.sys.databases d on s.database_id = d.database_id update s set s.last_backup_date = d.last_backup_date from #db_summary s inner join (select d.database_id , MAX(b.backup_finish_date) last_backup_date FROM master.sys.databases d LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D' WHERE d.database_id NOT IN ( 2, 3 ) GROUP BY d.database_id ) d on s.database_id = d.database_id if @debug_level > 3 select * from #db_summary declare @width_num_databases int , @width_data_fls int , @width_log_fls int , @width_total int , @width_available int , @width_non_sql_use int , @width_data_files_size int , @width_log_files_size INT , @width_nxt_grow INT = 11 declare @num_databases int , @data_fls int , @log_fls int , @total numeric(18,3) , @available numeric(18,3) , @non_sql_use numeric(18,3) , @data_mb numeric(18,3) , @log_mb numeric(18,3) select @num_databases = (select count(*) from sys.databases) , @data_fls = sum(data_fls) , @log_fls = sum(log_fls) , @total = sum(total_mb) , @available = sum(avail_mb) , @non_sql_use = sum(non_sql_mb) , @data_mb = sum(data_mb) , @log_mb = sum(log_mb) from #drv_summary if @debug_level > 3 select @num_databases db, @data_fls df, @log_fls, @total tot, @available avail, @non_sql_use nonsql, @data_mb datasize, @log_mb logsize --if @supppress_totals = 0 begin insert into #drv_summary (presentation_order, drv, num_databases, data_fls, log_fls, total_mb, avail_mb , data_mb, log_mb, non_sql_mb) values (-1, 'total', @num_databases, @data_fls, @log_fls, @total, @available , @data_mb, @log_mb, @non_sql_use); --end if @output_style = 0 and @result_sets in (1, 3, 5, 7) begin select drv as [Drv] , avail_mb avail_mb , data_mb data_mb , data_fls as data_fls , log_mb log_mb , log_fls as log_fls , non_sql_mb non_sql_mb , total_mb total_mb , [RO] from #drv_summary order by presentation_order, drv end else if @output_style = 2 and @result_sets in (1, 3, 5, 7) begin set @SQL = 'insert into ' + @save_to_table + ' (' + @save_column_list + ') SELECT ' + @save_column_list + ' from #drv_summary ' exec (@sql) end else begin select @width_num_databases = 3 + case when @num_databases = 0 then 0 else log10(@num_databases) + (log10(@num_databases)/3) END , @width_data_fls = 6 + case when @data_fls = 0 then 0 else log10(@data_fls) + (log10(@data_fls)/3) END , @width_log_fls = 5 + case when @log_fls = 0 then 0 else log10(@log_fls) + (log10(@log_fls)/3) END , @width_total = 0 + case when @total = 0 then 0 else log10(@total) + (log10(@total)/3) END , @width_available= 0 + case when @available = 0 then 0 else log10(@available) + (log10(@available)/3) END , @width_non_sql_use = 3 + case when @non_sql_use = 0 then 0 else log10(@non_sql_use) + (log10(@non_sql_use)/3) END , @width_data_files_size = 2+ case when @data_mb = 0 then 0 else log10(@data_mb) + (log10(@data_mb)/3) END , @width_log_files_size = 0 + case when @log_mb = 0 then 0 else log10(@log_mb) + (log10(@log_mb)/3) END select @width_num_databases = case when @width_num_databases < 6 then 6 else @width_num_databases END , @width_data_fls = case when @width_data_fls < 5 then 5 else @width_data_fls END , @width_log_fls = case when @width_log_fls < 5 then 5 else @width_log_fls END , @width_total = case when @width_total < 9 then 9 else @width_total END , @width_available = case when @width_available < 9 then 9 else @width_available END , @width_non_sql_use = case when @width_non_sql_use < 7 then 7 else @width_non_sql_use END , @width_data_files_size = case when @width_data_files_size < 7 then 7 else @width_data_files_size END , @width_log_files_size = case when @width_log_files_size < 5 then 5 else @width_log_files_size END if @debug_level > 3 select @width_num_databases [db], @width_data_fls files, @width_log_fls [log], @width_total tot, @width_available avail, @width_non_sql_use nonsql update #drv_summary set num_databases_formatted = RIGHT( CASE WHEN num_databases IS NULL THEN SPACE(@width_num_databases) WHEN num_databases = 0 THEN SPACE(@width_num_databases-1)+ '0' ELSE STUFF( STUFF ( RIGHT (SPACE(11) + CONVERT(VARCHAR(11), num_databases), 11) , 8, 0, CASE WHEN LOG10(num_databases)>3 THEN ',' ELSE '' END) , 5, 0, CASE WHEN LOG10(num_databases)>6 THEN ',' ELSE '' END) END , @width_num_databases) , data_fls_formatted = RIGHT( CASE WHEN data_fls IS NULL THEN SPACE(@width_data_fls) WHEN data_fls = 0 THEN SPACE(@width_data_fls-1)+ '0' ELSE STUFF( STUFF ( RIGHT (SPACE(11) + CONVERT(VARCHAR(11), data_fls), 11) , 8, 0, CASE WHEN LOG10(data_fls)>3 THEN ',' ELSE '' END) , 5, 0, CASE WHEN LOG10(data_fls)>6 THEN ',' ELSE '' END) END , @width_data_fls) , log_fls_formatted = RIGHT( CASE WHEN log_fls IS NULL THEN SPACE(@width_log_fls) WHEN log_fls = 0 THEN SPACE(@width_log_fls-1)+ '0' ELSE STUFF( STUFF ( RIGHT (SPACE(11) + CONVERT(VARCHAR(11), log_fls), 11) , 8, 0, CASE WHEN LOG10(log_fls)>3 THEN ',' ELSE '' END) , 5, 0, CASE WHEN LOG10(log_fls)>6 THEN ',' ELSE '' END) END , @width_log_fls) , data_mb_formatted = RIGHT(CASE WHEN data_mb IS NULL THEN SPACE(@width_data_files_size) WHEN data_mb = 0 then SPACE(@width_data_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, data_mb)), 11) , 9, 0, CASE WHEN LOG10(data_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(data_mb) > 6 THEN ',' ELSE '' END) END , @width_data_files_size) , log_mb_formatted = RIGHT(CASE WHEN log_mb IS NULL THEN SPACE(@width_log_files_size) WHEN log_mb = 0 then SPACE(@width_log_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, log_mb)), 11) , 9, 0, CASE WHEN LOG10(log_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(log_mb) > 6 THEN ',' ELSE '' END) END , @width_log_files_size) , total_formatted = RIGHT(CASE WHEN total_mb IS NULL THEN SPACE(@width_total) WHEN total_mb = 0 then SPACE(@width_total-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, total_mb)), 11) , 9, 0, CASE WHEN LOG10(total_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(total_mb) > 6 THEN ',' ELSE '' END) END , @width_total) , avail_mb_formatted = RIGHT(CASE WHEN avail_mb IS NULL THEN SPACE(@width_available) WHEN avail_mb = 0 then SPACE(@width_available-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, avail_mb)), 11) , 9, 0, CASE WHEN LOG10(avail_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(avail_mb) > 6 THEN ',' ELSE '' END) END , @width_available) , non_sql_mb_formatted = RIGHT(CASE WHEN non_sql_mb IS NULL THEN SPACE(@width_non_sql_use) WHEN non_sql_mb = 0 then SPACE(@width_non_sql_use-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, non_sql_mb)), 11) , 9, 0, CASE WHEN LOG10(non_sql_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(non_sql_mb) > 6 THEN ',' ELSE '' END) END , @width_non_sql_use) , read_only_formatted = case when RO is null then '' when RO = 1 then 'RO' else ' ' end if @result_sets in (1, 3, 5, 7) begin select upper(drv) [drv] , avail_mb_formatted avail_mb , data_mb_formatted as [Data_MB] , data_fls_formatted as [data_fls] , log_mb_formatted [log_MB] , log_fls_formatted as [log_fls] , non_sql_mb_formatted Non_SQL_mb , total_formatted total_mb , read_only_formatted [RO] -- diagnostic , total_mb - (avail_mb + data_mb + log_mb + non_sql_mb) from #drv_summary order by presentation_order, drv end end -- process the database warnings. These warn about some issue in the maintenance of the database -- suspect_pages update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'Suspect_pages check msdb..suspect_pages|' from #db_summary s inner join (SELECT distinct database_id FROM msdb..suspect_pages) db on s.database_id = db.database_id -- db_mirroring has repaired pages update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'mirroring used to repair corrupt pages|' from #db_summary s inner join (SELECT distinct database_id FROM sys.dm_db_mirroring_auto_page_repair) db on s.database_id = db.database_id update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'Page Verify set to TORN_PAGE_DETECTION|' from #db_summary s where s.page_verify = 'TORN_PAGE' update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'No database backup in 10 days|' from #db_summary s where datediff(day, s.last_backup_date, getdate()) > 10 and database_id not in (2,3) update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'No database backup in 10 days|' from #db_summary s where datediff(day, s.last_backup_date, getdate()) > 10 and database_id not in (2,3) update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'No database backup in 10 days|' from #db_summary s where datediff(day, s.last_backup_date, getdate()) > 10 and database_id not in (2,3) update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'Missing Transaction Log Backup|' FROM #db_summary s LEFT OUTER JOIN msdb.dbo.backupset b ON s.[database] = b.database_name AND b.type = 'L' WHERE s.recovery_model in ('Full', 'Bulk-logged') AND b.type IS NULL AND s.database_id NOT IN ( 2, 3 ) if OBJECT_ID('tempdb..#multi_log') is not null drop table #multi_log select drv, database_id into #multi_log from #file_summary m where type = 1 group by drv, database_id having count(*)>1 declare multi_log_cursor cursor fast_forward for select distinct database_id from #multi_log order by database_id declare @drive_list varchar(2000) open multi_log_cursor fetch multi_log_cursor into @database_id while @@FETCH_STATUS=0 begin set @drive_list = '' select @drive_list = drv + ', ' from #multi_log where database_id = @database_id set @drive_list = case when len(@drive_list)=0 then @drive_list else left(@drive_list, len(@drive_list)-2) end update #db_summary set warning_indicator = '*' , warning = coalesce(warning, '') + 'Drive(s) (' + @drive_list + ') has multiple log files|' fetch multi_log_cursor into @database_id end close multi_log_cursor deallocate multi_log_cursor update s set warning_indicator = '*' , warning = coalesce(warning, '') + 'Mul|' FROM #db_summary s where exists (select drv, database_id from #file_summary m where type = 1 and s.database_id = m.database_id group by drv, database_id having count(*)>1 ) -- Check for the last DBCC CHECKDB. We might not have permission to run DBCC DBINFO -- BEGIN TRY; -- DBCC Checkdb script comes from: -- http://sqlserverpedia.com/wiki/Last_clean_DBCC_CHECKDB_date -- if OBJECT_ID('tempdb..#dbcc_fields') is not null drop table #dbcc_fields CREATE TABLE #dbcc_fields ( ParentObject VARCHAR(255) , [Object] VARCHAR(255) , Field VARCHAR(255) , [Value] VARCHAR(255) ) if OBJECT_ID('tempdb..#DBCCResults') is not null drop table #DBCCResults CREATE TABLE #DBCCResults ( ServerName VARCHAR(255) , DBName VARCHAR(255) , LastCleanDBCCDate DATETIME ) EXEC master.dbo.SP_MSFOREACHDB @Command1 = 'USE [?] INSERT INTO #dbcc_fields EXECUTE (''DBCC DBINFO WITH TABLERESULTS, NO_INFOMSGS'')' , @Command2 = 'INSERT INTO #DBCCResults SELECT @@SERVERNAME, ''?'', value FROM #dbcc_fields WHERE field = ''dbi_dbccLastKnownGood''' , @Command3 = 'TRUNCATE TABLE #dbcc_fields' --Delete duplicates due to a bug in SQL Server 2008 ;WITH DBCC_CTE AS ( SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DBName, LastCleanDBCCDate ORDER BY LastCleanDBCCDate) RowID FROM #DBCCResults ) DELETE FROM DBCC_CTE WHERE RowID > 1; update s set warning_indicator = '*' , warning = coalesce(warning, '') + CASE WHEN LastCleanDBCCDate = '1900-01-01 00:00:00.000' THEN 'Never ran DBCC CHECKDB' when datediff(day, convert(datetime, LastCleanDBCCDate) , getdate()) > @dbcc_checkdb_days_ago_threashold then 'Last DBCC CHECKDB was ' + convert(varchar(30), datediff(day, convert(datetime, LastCleanDBCCDate) , getdate())) + ' days ago' ELSE '' END FROM #db_summary s inner join #DBCCResults r on s.[database] = r.DBName where datediff(day, convert(datetime, LastCleanDBCCDate) , getdate()) > @dbcc_checkdb_days_ago_threashold AND database_id != 2 -- no warning for tempdb END TRY BEGIN CATCH Raiserror ('Warnings on Last DBCC CHECKDB suppressed due to lack of permission to run DBCC DBINFO', 0, 1) WITH NOWAIT; -- If we lack permissions, it ignore the erorr and omit he warning IF ERROR_NUMBER() != 2571 BEGIN print 'error running DBCC DBINFO (' + CONVERT(VARCHAR(30), error_number()) + '): ' + error_message(); end else begin Raiserror ('Warnings on Last DBCC CHECKDB suppressed due to lack of permission to run DBCC DBINFO', 0, 1) WITH NOWAIT; END END CATCH -- output the database summary if @output_style = 0 and @result_sets in (2, 3, 6, 7) begin select [database] , warning_indicator W , data_fls , data_mb , unused_mb , data_pct , log_fls , log_mb , log_pct , total_mb , last_backup_date [last_backup] , recovery_model [model] , is_allow_snapshot snap , is_read_committed_snapshot rcs , is_encrypted ECR , is_trustworthy TRST , [Page_verify] , [state] , database_id [db_id] , Warning from #db_summary order by presentation_order, [database] end else if @output_style = 2 and @result_sets in (2, 3, 6, 7) begin set @sql = 'INSERT INTO ' + @save_to_table + ' (' + @save_column_list + ') select ' + @save_column_list + ' from #db_summary order by presentation_order, [database] ' exec (@sql) end else begin update #db_summary set data_fls_formatted = RIGHT( CASE WHEN data_fls IS NULL THEN SPACE(@width_data_fls) WHEN data_fls = 0 THEN SPACE(@width_data_fls-1)+ '0' ELSE STUFF( STUFF ( RIGHT (SPACE(11) + CONVERT(VARCHAR(11), data_fls), 11) , 8, 0, CASE WHEN LOG10(data_fls)>3 THEN ',' ELSE '' END) , 5, 0, CASE WHEN LOG10(data_fls)>6 THEN ',' ELSE '' END) END , @width_data_fls) , log_fls_formatted = RIGHT( CASE WHEN log_fls IS NULL THEN SPACE(@width_log_fls) WHEN log_fls = 0 THEN SPACE(@width_log_fls-1)+ '0' ELSE STUFF( STUFF ( RIGHT (SPACE(11) + CONVERT(VARCHAR(11), log_fls), 11) , 8, 0, CASE WHEN LOG10(log_fls)>3 THEN ',' ELSE '' END) , 5, 0, CASE WHEN LOG10(log_fls)>6 THEN ',' ELSE '' END) END , @width_log_fls) , data_mb_formatted = RIGHT(CASE WHEN data_mb IS NULL THEN SPACE(@width_data_files_size) WHEN data_mb = 0 then SPACE(@width_data_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, data_mb)), 11) , 9, 0, CASE WHEN LOG10(data_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(data_mb) > 6 THEN ',' ELSE '' END) END , @width_data_files_size) , log_mb_formatted = RIGHT(CASE WHEN log_mb IS NULL THEN SPACE(@width_log_files_size) WHEN log_mb = 0 then SPACE(@width_log_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, log_mb)), 11) , 9, 0, CASE WHEN LOG10(log_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(log_mb) > 6 THEN ',' ELSE '' END) END , @width_log_files_size) , total_formatted = RIGHT(CASE WHEN total_mb IS NULL THEN SPACE(@width_total) WHEN total_mb = 0 then SPACE(@width_total-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, total_mb)), 11) , 9, 0, CASE WHEN LOG10(total_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(total_mb) > 6 THEN ',' ELSE '' END) END , @width_total) , unused_formatted = RIGHT(CASE WHEN unused_mb IS NULL THEN SPACE(@width_data_files_size) WHEN unused_mb = 0 then SPACE(@width_data_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, unused_mb)), 11) , 9, 0, CASE WHEN LOG10(unused_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(unused_mb) > 6 THEN ',' ELSE '' END) END , @width_data_files_size) , read_only_formatted = case when is_read_only is null then '' when is_read_only = 1 then 'RO' else ' ' end , is_allow_snapshot_formatted = case when is_allow_snapshot=1 then 'Y' else ' ' end , is_read_committed_snapshot_formatted = case when is_read_committed_snapshot=1 then 'Y' else ' ' end , is_encrypted_formatted = case when is_encrypted=1 then 'Y' else ' ' end , is_trustworthy_formatted = case when is_trustworthy=1 then 'TR' else ' ' end , last_backup_date_formatted = stuff(stuff(convert (varchar(16), last_backup_date, 112), 7, 0, '-'), 5, 0, '-') , data_pct_formatted = coalesce(right (space(5) + convert(varchar(5), convert(int, data_pct)), 5), ' ') , log_pct_formatted = coalesce(right (space(5) + convert(varchar(5), convert(int, log_pct)), 5), ' ') if @result_sets in (2, 3, 6, 7) begin select [database] , warning_indicator W , data_fls_formatted data_fls , data_mb_formatted data_mb , unused_formatted unused_mb , data_pct_formatted data_pct , log_fls_formatted log_fls , log_mb_formatted log_mb , log_pct_formatted log_pct , total_formatted total_mb , last_backup_date_formatted [last_backup] , recovery_model [model] , read_only_formatted RO , is_allow_snapshot_formatted SNAP , is_read_committed_snapshot_formatted RCS , is_encrypted_formatted ECR , is_trustworthy_formatted TRST , [Page_verify] , [state] , [database_id] [db_id] , Warning from #db_summary order by presentation_order, [database] end end declare @max_size_mb numeric(18,3) declare @width_max_size int select @max_size_mb = max(max_size_mb) from #file_summary if @output_style=1 begin select @width_max_size = 3 + case when @max_size_mb=0 then 0 else log10(@max_size_mb) + (log10(@max_size_mb)/3) END select @width_max_size = case when @width_max_size < 11 then 11 else @width_max_size END update #file_summary set size_formatted = RIGHT(CASE WHEN size_mb IS NULL THEN SPACE(@width_data_files_size) WHEN size_mb = 0 then SPACE(@width_data_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, size_mb)), 11) , 9, 0, CASE WHEN LOG10(size_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(size_mb) > 6 THEN ',' ELSE '' END) END , @width_data_files_size) , max_size_formatted = case when max_size_mb is null or max_size_mb < 0 then SPACE(@width_max_size) else RIGHT(CASE WHEN max_size_mb IS NULL THEN SPACE(@width_max_size) WHEN max_size_mb = 0 then SPACE(@width_max_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, max_size_mb)), 11) , 9, 0, CASE WHEN LOG10(max_size_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(max_size_mb) > 6 THEN ',' ELSE '' END) END , @width_max_size) end , avail_mb_formatted = RIGHT(CASE WHEN avail_mb IS NULL THEN SPACE(@width_available) WHEN avail_mb = 0 then SPACE(@width_available-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, avail_mb)), 11) , 9, 0, CASE WHEN LOG10(avail_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(avail_mb) > 6 THEN ',' ELSE '' END) END , @width_available) , used_formatted = RIGHT(CASE WHEN used_mb IS NULL THEN SPACE(@width_data_files_size) WHEN used_mb = 0 then SPACE(@width_data_files_size-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, used_mb)), 11) , 9, 0, CASE WHEN LOG10(used_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(used_mb) > 6 THEN ',' ELSE '' END) END , @width_data_files_size) , read_only_formatted = case when coalesce(RO, 0) = 1 OR coalesce(volume_is_read_only, 0)=1 then 'RO' ELSE ' ' end , next_file_growth_status = case when next_file_growth_status != '' then next_file_growth_status else RIGHT(CASE WHEN nxt_grow_mb IS NULL THEN SPACE(@width_nxt_grow) WHEN nxt_grow_mb = 0 then SPACE(@width_nxt_grow-1) + '0' ELSE STUFF(STUFF(RIGHT(SPACE(11) + CONVERT (VARCHAR(30), convert(int, nxt_grow_mb)), 11) , 9, 0, CASE WHEN LOG10(nxt_grow_mb) > 3 THEN ',' ELSE '' END) , 6, 0, CASE WHEN LOG10(nxt_grow_mb) > 6 THEN ',' ELSE '' END) END , @width_nxt_grow) end , filegroup = case when coalesce(filegroup, '') = '' and type_desc != 'ROWS' then type_desc else coalesce(filegroup, '') end , full_pct_formatted = right (space(8) + convert(varchar(8), convert(int, full_pct)), 8) if @result_sets in (4, 5, 6, 7) begin set @sql = 'select UPPER(drv) as drv ' + case when @num_db > 1 then ' , [database] ' else '' end + ' , [filegroup] , logical_file , size_formatted size_mb , used_formatted used_mb , full_pct_formatted [full_pct] , max_size_formatted max_size_mb , avail_mb_formatted avail_mb , next_file_growth_status nxt_grow_mb , read_only_formatted RO , state_desc [state] , [file_id] , physical_name from #file_summary m inner join #db d on m.database_id = d.database_id where d.selected = 1 order by ' + case when @sort_files_by = 0 then '1 '+ case when @num_db>1 then ', [Database]' else '' end + ' , case when [type]=1 then 1 else 0 end -- logs at end , case when [filegroup] = ''PRIMARY'' then 0 else 1 end -- PRIMARY first , 3, 4' else ' db_name(database_id) , case when [type] = 1 then 1 else 0 end -- logs at end , case when [filegroup] = ''PRIMARY'' then 0 else 1 end -- PRIMARY first , [filegroup] ' END if @debug_level > 3 print @sql; exec (@sql) end end else if @output_style = 2 and @result_sets in (4, 5, 6, 7) begin set @sql = 'INSERT INTO ' + @save_to_table + ' (' + @save_column_list + ') select ' + @save_column_list + ' from #file_summary' exec (@sql) end else if @output_style = 0 and @result_sets in (4, 5, 6, 7) begin set @sql = 'select UPPER(drv) as Drv , [database] , [filegroup] , logical_file , size_mb , used_mb , full_pct , max_size_mb , avail_mb , nxt_grow_mb , RO , state_desc [state] , [file_id] , physical_name from #file_summary m inner join #db d on m.database_id = d.database_id where d.selected = 1 order by ' + case when @sort_files_by = 0 then '1, 2, 3 ' + case when @num_db>1 then ', 4 ' else '' end else ' db_name(database_id) , case when type = 1 then 1 else 0 end -- logs at end , case when [filegroup] = ''PRIMARY'' then 0 else 1 end -- PRIMARY first , [filegroup] ' END if @debug_level > 3 print @sql; exec (@sql) end if @debug_level > 3 select * from #file_summary order by drv, db_name(database_id), [filegroup] exit_proc: return @rc go EXEC sys.sp_MS_marksystemobject sp_storage go