Technical Article

sp_dbsize

,

I'm trying to repost the same script submitted on 7/9/2003 which didn't get posted completely for some reason.

use master
go

if exists (select name from sysobjects (nolock) where name = 'sp_CHI_dbsize' and type = 'P' )
  drop proc dbo.sp_CHI_dbsize
go

create proc dbo.sp_CHI_dbsize @dbname sysname = null
as

/*  Date: 11/18/2002
**  Author: Richard Ding
**  Important change: Use "DBCC SHOWFILESTATS" that can report real time figure on data file.
**  Purpose: Equivalent to Taskpad view. It reports realtime figures of db size info. No need 
**  to run "DBCC UPDATEUSAGE". It shows the following figures: 
**    1. Total database size in MB.
**    2. Data and log sizes in MB.
**    3. Data being used in MB and percentage.
**    4. Data free in MB and percentage.
**    5. Log being used in MB and percentage.
**    6. Log free in MB and percentage.
**  Test code:
**    exec sp_CHI_dbsize 
**    exec sp_CHI_dbsize pubs
**    exec sp_CHI_dbsize bogus     --  bogus is not a valid database
*/
set nocount on
if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))
  begin
    raiserror('Incorrect database name. ', 16, 1)
    return (1)
  end
create table #datafilestats 
(dbname varchar(25), 
flag bit default 0, 
Fileid tinyint, 
[FileGroup] tinyint, 
TotalExtents dec (7, 1), 
UsedExtents dec (7, 1), 
[Name] varchar(50), 
[FileName] sysname )

declare @string sysname, @dbname1 sysname
set @string = ''
if @dbname is not null
set @dbname1 = @dbname
else 
set @dbname1 = ''

  set @dbname = ''

while 1=1
begin
  select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc  
  if @@rowcount = 0
    break
  set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)
  update #datafilestats set dbname = @dbname, flag = 1 where flag = 0 
update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)
where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname
update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)
where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname
end

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) )
insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')
update #sizeinfo set 
  data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1 
update #sizeinfo set
  data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1 
update #sizeinfo set
  total = (data + log)
update #sizeinfo set
[data (%)] = (data_used * 100.0 / data)
update #sizeinfo set
 data_free = (data - data_used)
update #sizeinfo set
 [data_free (%)] = (100 - [data (%)])
update #sizeinfo set
log_used = (log * [log (%)] / 100.0)
update #sizeinfo set
log_free = (log - log_used)
update #sizeinfo set
[log_free (%)] = (log_free * 100.0 / log)
print ''
if @dbname1 = ''
begin
  print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)
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)
end
else 
begin
print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)
select db_name, 
         total, 
         data, 
         data_used, 
         [data (%)], 
         data_free, 
         [data_free (%)], 
         log, 
         log_used, 
         [log (%)], 
         log_free, 
         [log_free (%)] 
    from #sizeinfo where db_name = @dbname1
end
go

Read 2,673 times
(13 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating