sp_checksize

,

This stored procedure checks the sizes of one or all databases (including total data, total log, data used/free, data used/free percentage, log used/free, log used/free percentage). If a database name is provided, it will only check the given db. If no parameters provided, all dbs will be checked and the total size of all dbs on the server is summed up at the bottom. This is an equivalent to the Taskpad view of Enterprise Manager. It applies to both SQL 7 and 2000.

use master
go

if exists (select name from sysobjects where name = 'sp_checksize' and type = 'p' )
    drop proc sp_checksize
go

create proc sp_checksize @dbname sysname = null
as
if @dbname is not null and @dbname not in (select name from sysdatabases)
		begin
			raiserror('relax!... you''re just one step away from the results. please verify the database name is correct and try again. ', 16, 1)
			return (1)
		end

set nocount on
if exists (select * from sysobjects where name = '#sizeinfo' and type = 'u')
		drop table #sizeinfo
create table #sizeinfo 
(
	db_name varchar(30) not null primary key clustered, 
	total dec (7, 1),
	data dec (7, 1), 
	data_used dec (7, 1),
	[data (%)] dec (7, 1),
	data_free dec (7, 1), 
	[data_free (%)] dec (7, 1),
	log dec (7, 1), 
	log_used dec (7, 1), 
	[log (%)] dec (7, 1),
	log_free dec (7, 1),
	[log_free (%)] dec (7, 1),
	status dec (7, 1)
)	
set nocount on
insert #sizeinfo ( db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')
print '' print ''
if @dbname is null
		declare dbname cursor for select name from sysdatabases order by name asc
else if @dbname is not null
begin
		delete from #sizeinfo where db_name <> @dbname
		declare dbname cursor for select name from sysdatabases where name = @dbname	
end
open dbname
fetch next from dbname into @dbname
while @@fetch_status = 0
	begin					--	adding .0 at the end of interger to avoid divide by zero error
		exec (	' use ' + @dbname + '  declare @total dec(7, 1),
				@data dec (7, 1),
				@data_used dec (7, 1),
				@data_percent dec (7, 1),
				@data_free dec (7, 1),
				@data_free_percent dec (7, 1),
				@log dec (7, 1),
				@log_used dec (7, 1),
				@log_used_percent dec (7, 1),
				@log_free dec (7, 1),
				@log_free_percent dec (7, 1)				
				set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 / 1048576.0
				set @data = (select sum(size) from sysfiles where (status & 64 = 0)) * 8192.0 / 1048576.0
				set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255)) * 8192.0 / 1048576.0
				set @data_percent = (@data_used * 100.0 / @data)
				set @data_free = (@data - @data_used)
				set @data_free_percent = (@data_free * 100.0 / @data )
				set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')
				set @log_used_percent = (select [log (%)] from #sizeinfo where db_name = '''+@dbname+''')
				set @log_used = @log * @log_used_percent / 100.0
				set @log_free = @log - @log_used
				set @log_free_percent =  @log_free * 100.0 / @log
				update #sizeinfo set total = @total, 
															data = @data , 
															data_used = @data_used, 
															[data (%)] = @data_percent,
															data_free = @data_free,
															[data_free (%)] = @data_free_percent,
															log_used = @log_used, 
															log_free = @log_free,
															[log_free (%)] = @log_free_percent
																			where db_name = '''+@dbname+'''' )
		fetch next from dbname into @dbname
	end
close dbname
deallocate dbname

print '***  note: all numbers are in mb. if you see unreasonable or negative values, it is time to run dbcc updateusage.  ***'
print '' 
if ((select count(*) from #sizeinfo	) <> 1)
	select db_name, total, data, data_used, [data (%)], data_free, [data_free (%)], log, log_used, [log (%)], log_free, [log_free (%)] 
			from #sizeinfo order by db_name asc compute sum(total)	
else 
	select db_name, total, data, data_used, [data (%)], data_free, [data_free (%)], log, log_used, [log (%)], log_free, [log_free (%)] 
			from #sizeinfo	

drop table #sizeinfo
go

Rate

Share

Share

Rate