Script to get DB space usage Info.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

csajid@gmail.com - 04 Jul 2008 14:21 GMT

Hi All,

I am looking for a Script which will give me complete space usage
information of a particular DB in SQL Server 2000.

It would be of great help if I get the script which will give same
results as we get in Database diagram.

Thanks in advance.

Thanks & Regards,
Sajid C.

Reply to this Message

John Bell - 05 Jul 2008 09:27 GMT

> Hi All,
>
[quoted text clipped - 8 lines]
> Thanks & Regards,
> Sajid C.

Hi

I am not sure what you have usage in your database diagram as the diagram is
for the schema not a monitoring tool! There may be some estimate of database
size with some tools but SQL Server's inbuilt diagram tool does not give
that.

If you want to see space used see sp_spaceused procedure, you can also look
at the code for this procedure to tailor it your requirements.

John

Reply to this Message

Uri Dimant - 06 Jul 2008 06:23 GMT

In additon on what John said , take a look at
SELECT * FROM sysfiles

> Hi All,
>
[quoted text clipped - 8 lines]
> Thanks & Regards,
> Sajid C.

Reply to this Message

csajid@gmail.com - 07 Jul 2008 14:58 GMT

> In additon on what John said , take a look at
> SELECT * FROM sysfiles
[quoted text clipped - 17 lines]
>
> - Show quoted text -

Hi John & Uri,

Thanks for your reply.

I got the script which gratifies my requirement. Below is the script
for your reference.

select
[FileSizeMB] =
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB] =
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/
128.,2))) ,
[UnusedSpaceMB] =
convert(numeric(10,2),sum(round((a.size-
fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log'
else 'Data' end,
[DBFileName] = isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1
end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name

Thanks & Regards,
Sajid C.

Reply to this Message