Prev: SQL 2005 - Not responding - freezing
Next: New SQL Security problem coming next Tuesday
From: csajid on 4 Jul 2008 09:21
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.


From: John Bell on 5 Jul 2008 04:27

<csajid(a)gmail.com> wrote in message
news:a34ca3b6-483a-41b1-a9ba-0aa8f970d62a(a)p39g2000prm.googlegroups.com...
> 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.
>
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

From: Uri Dimant on 6 Jul 2008 01:23
In additon on what John said , take a look at
SELECT * FROM sysfiles



<csajid(a)gmail.com> wrote in message
news:a34ca3b6-483a-41b1-a9ba-0aa8f970d62a(a)p39g2000prm.googlegroups.com...
> 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.
>
>


From: csajid on 7 Jul 2008 09:58
On Jul 6, 10:23 am, "Uri Dimant" <u...(a)iscar.co.il> wrote:
> In additon on what John said , take a look at
> SELECT * FROM sysfiles
>
> <csa...(a)gmail.com> wrote in message
>
> news:a34ca3b6-483a-41b1-a9ba-0aa8f970d62a(a)p39g2000prm.googlegroups.com...
>
>
>
> > 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.- Hide quoted text -
>
> - 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.
 | 
Pages: 1
Prev: SQL 2005 - Not responding - freezing
Next: New SQL Security problem coming next Tuesday