SQL Server – Database Size information

Leave a comment (0) Go to comments

DBA must monitor Database Size information which should tell how much space is being used internally by SQL Server files to store data and how much space is currently free inside the database files. Used space and free space is critical area for operational support. Operational DBA must know,

  • What is the Total Database Occupied Size (Physical File)
  • Data File Size (Physical .mdf,.ndf file size)
  • How Much space is actually used by actual data in data files (Size, which is used by data pages to store data)
  • What % of space is actually used by actual data in data files (Size, which is used by data pages to store data) [(data used space / physical  data file size)*100]
  • How Much space is currently free in data files (Size, which is not used, free pages size)
  • What % of space is currently free in data files (Size, which is not used, free pages size) [(data free space / physical data file size)*100]
  • How Much space is actually used by actual data in log files (Size, which is used by physical log file)
  • What % of space is actually used by actual data in log files (Size, which is used by current transactions ) [(used space / physical log file size)*100]
  • How Much space is currently free in log files (Size, which is not used, free pages size)
  • What % of space is currently free in log files (Size, which is not used, free pages size) [(data free space / physical log file size)*100]

Here is the script, which will give all this information.


**** Some characters might have change, and you might get syntax error. To avoid all this issues, Click Here to download the script file .txt format

use master

go

declare @dbname
sysname

set @dbname = null

if @dbname
is not null and @dbname
not in (select name from
master.dbo.sysdatabases)


begin


raiserror(‘You”re just one step away
from the results. please verify the database name is correct and try again. www.sqlServer-Training.com’
, 16,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(100) 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 master.dbo.sysdatabases where
not status
& 32 = 32
and not status & 512
= 512 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 master.dbo.sysdatabases where
not status
& 32 = 32
and not status & 512
= 512 and
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

if ((select count(*) from #sizeinfo
) <> 1)

select @@servername as
‘ServerName’,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

else

select @@servername as
‘ServerName’,db_name, total, data, data_used, [data
(%)], data_free, [data_free (%)],
log,
log_used, [log (%)], log_free,
[log_free (%)]


from #sizeinfo

drop table #sizeinfo

OUTPUT

This Script will give result like this table.

ServerName db_name total data data_used data (%) data_free data_free (%) log log_used log (%) log_free log_free (%)
TestServer CMD_DEV 3 2 1.1 55 0.9 45 1 0.5 46.1 0.5 50
TestServer master 4.8 4 2.6 65 1.4 35 0.7 0.3 42.1 0.4 57.1
TestServer model 1.7 1.2 1.1 91.7 0.1 8.3 0.5 0.4 80.2 0.1 20
TestServer msdb 8.2 6.4 4.6 71.9 1.8 28.1 1.7 0.6 36.1 1.1 64.7
TestServer SQLServer-Training-TEMP 3 2 1.1 55 0.9 45 1 0.4 35.4 0.6 60
TestServer tempdb 8.5 8 1.1 13.8 6.9 86.3 0.5 0.3 63.1 0.2 40

Screen Shot for ready reference

SQL Server   Database Size information SQL Server Database Size information space used by SQL Server space used by logfile space used by datafile Database Size information

EOF - SQL Server – Database Size information, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.