Database Properties

17 Feb 2006 6:47 AM

Prasad

Hi,

    From where can I get SpaceAvailableInMB and KB for a database for SQL
2000 and SQL 2005.
    sp_spaceused gives the Size of the Database, Data Space Used, Index
Space Used.
    SQL-DMO gives it.

Thanks
Prasad

17 Feb 2006 8:43 AM

Razvan Socol

Hi, Prasad

It seems that your goal is to re-write SQL-DMO using T-SQL/ADO. If
that's the case, your job will be easier if you use Profiler to see
what SQL-DMO is doing. For example, execute the following VB code while
Profiler is running:

Dim s As New SQLServer, d As Database
s.LoginSecure = True
s.Connect
Set d = s.Databases("SomeDatabase")
Debug.Print d.SpaceAvailableInMB

You will see that the last statements executed by SQL-DMO are:
exec sp_spaceused
DBCC SQLPERF(LOGSPACE)

In this scenario, it seems that the SpaceAvailableInMB is computed in
the following way:
SpaceAvailableInMB = "unallocated space"
+ "Log Size (MB)" / 1.024 * (1 - "Log Space Used (%)" / 100)
(some roundings may occur, since the documentation states that the
figure is accurate to two decimal places).

Razvan

17 Feb 2006 3:34 PM

Prasad

Thanks Razvan once again !!!!

Show quote

"Razvan Socol" <rso***@gmail.com> wrote in message
news:1140165780.578007.279570@g47g2000cwa.googlegroups.com...
> Hi, Prasad
>
> It seems that your goal is to re-write SQL-DMO using T-SQL/ADO. If
> that's the case, your job will be easier if you use Profiler to see
> what SQL-DMO is doing. For example, execute the following VB code while
> Profiler is running:
>
> Dim s As New SQLServer, d As Database
> s.LoginSecure = True
> s.Connect
> Set d = s.Databases("SomeDatabase")
> Debug.Print d.SpaceAvailableInMB
>
> You will see that the last statements executed by SQL-DMO are:
> exec sp_spaceused
> DBCC SQLPERF(LOGSPACE)
>
> In this scenario, it seems that the SpaceAvailableInMB is computed in
> the following way:
> SpaceAvailableInMB = "unallocated space"
> + "Log Size (MB)" / 1.024 * (1 - "Log Space Used (%)" / 100)
> (some roundings may occur, since the documentation states that the
> figure is accurate to two decimal places).
>
> Razvan
>