Hi I was wondering how would I be able to calculate the total size
allocated to a database device in MSSQL?

I've used the following query:

select cntrltype, low, high, name
from sysdevices
where cntrltype = 0
order by low

This however (correct me if I'm wrong), gives me the size database
used on that particular device. This gets me half way correct,as I
still need to ascertain what space has not been allocated in order to
calculate the full size of the device?

I am very new to all this, so I am trying my best to learn and dig
around. Your advise and help is much appreciated.

Regards

What version of SQL Server are you using? The system table sysdevices
is only kept around past SQL Server 2000 for backward compatibility.
Even in 2000 the concept of "devices" was just a holdover from the bad
old days when a device was a file that could (shudder!) have bits of
different databases on it.

If you really want to know what is going on inside things find a
system stored procedure that shows you what you want, (sp_helpdb,
sp_spaceused, etc) and inspect the source code.

Roy Harvey
Beacon Falls, CT

On Thu, 4 Sep 2008 20:05:11 -0700 (PDT), ph3ng <jthlcf@gmail.com>
wrote:

>Hi I was wondering how would I be able to calculate the total size
>allocated to a database device in MSSQL?
>
>I've used the following query:
>
>select cntrltype, low, high, name
>from sysdevices
>where cntrltype = 0
>order by low
>
>This however (correct me if I'm wrong), gives me the size database
>used on that particular device. This gets me half way correct,as I
>still need to ascertain what space has not been allocated in order to
>calculate the full size of the device?
>
>I am very new to all this, so I am trying my best to learn and dig
>around. Your advise and help is much appreciated.
>
>Regards

>--- On Sun, 9/7/08, ph3ng <jthlcf@gmail.com> wrote:
>
>> Thanks for your reply. I believe I am currently using:
>>
>> Microsoft SQL Server 2000.
>>
>> I am not too well versed with the the history of sysdevices
>> - and
>> appreciate the background info that you've given me.
>>
>> When I use sp_helpdb (which I have done previously),
>> doesn't this
>> generate a list of all the databases currently on the
>> server as well
>> as the size for each db? If this is correct, then I would
>> still only
>> have 'space used' and not total space allocated on
>> the device. Is this
>> correct?

Please reply to the newsgroup so that the discussion may help others
too.

If you just run sp_helpdb without any parameter you get a list of
databases with some information on each. If you execute it with a
database name as a parameter it returns considerably more detail about
that specific database, including the size (total space allocated) of
each database file. In SQL Server 2000 the source to sp_helpdb can be
found in the master database.

Roy Harvey
Beacon Falls, CT

On Sep 8, 11:20*am, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> >--- On Sun, 9/7/08, ph3ng <jth...@gmail.com> wrote:
>
> >> Thanks for your reply. I believe I am currently using:
>
> >> Microsoft SQL Server *2000.
>
> >> I am not too well versed with the the history of sysdevices
> >> - and
> >> appreciate the background info that you've given me.
>
> >> When I use sp_helpdb (which I have done previously),
> >> doesn't this
> >> generate a list of all the databases currently on the
> >> server as well
> >> as the size for each db? If this is correct, then I would
> >> still only
> >> have 'space used' and not total space allocated on
> >> the device. Is this
> >> correct?
>
> Please reply to the newsgroup so that the discussion may help others
> too.
>
> If you just run sp_helpdb without any parameter you get a list of
> databases with some information on each. *If you execute it with a
> database name as a parameter it returns considerably more detail about
> that specific database, including the size (total space allocated) of
> each database file. *In SQL Server 2000 the source to sp_helpdb can be
> found in the master database.
>
> Roy Harvey
> Beacon Falls, CT

Hi Roy

Thanks for that. sorry about replying it privately. I will give that a
try. Thank you.

Regards

Justin