The script below determines the actual size of a database of a MS SQL 2000 Server (should also run with SQL 7).
You will have to create the Stored Procedure at the end of this message first in every database that you wanna monitor and give the user account used in the script execute access to the stored procedure. Works very well in HM.
------- VB Script --------------------------
'-----------------------------------------------------------------------------
'File : SQL2k-DBspace.vbs
'Purpose : Determines the size in MB of database x on SQL Server z
'Language: VBScript
'Version : 1.0
'Author : Logical Networks (PTY) Ltd. - JRW
'-----------------------------------------------------------------------------
Option Explicit
const statusAlive = "Host is alive:"
const statusDead = "No answer:"
const statusUnknown = "Unknown:"
const statusNotResolved = "Unknown host:"
const statusOk = "Ok:"
const statusBad = "Bad:"
const statusBadContents = "Bad contents:"
const adCmdStoredProc = &H0004
const adBoolean = 11
const DSN = "Provider=SQLOLEDB; Data Source=MyServer; Initial Catalog=MyDB; User ID=MyAccount; Password=MyPwd;"
Function PerformTest()
DIM rs, conn, cmd, param, sStatus, sDBSize, sUnallocSpace, sUsedSpace
sStatus = ""
Set conn = CreateObject("ADODB.Connection")
If (Err.Number <> 0) then sStatus = statusUnknown
If (sStatus = "") Then Set cmd = CreateObject("ADODB.Command")
If (Err.Number <> 0) then sStatus = statusUnknown
If (sStatus = "") Then Set rs = CreateObject("ADODB.Recordset")
If (Err.Number <> 0) then sStatus = statusUnknown
If (sStatus = "") Then conn.Open(DSN)
If (Err.Number <> 0) then sStatus = statusUnknown
If (sStatus = "") Then
cmd.ActiveConnection = conn
cmd.CommandText = "GetDBSpace"
cmd.CommandType = adCmdStoredProc
' Create, add and set parameters
'Set param = cmd.CreateParameter("updateusage", adBoolean, adParamInput, 1, False)
'cmd.Parameters.Append(param)
cmd.ActiveConnection = conn
End If
If (sStatus = "") Then Set rs = cmd.Execute()
If (Err.Number <> 0) then sStatus = statusUnknown
If (sStatus = "") Then
If NOT (rs.Eof OR rs.Bof) Then
sDBSize = rs("DBsize")
sUnallocSpace = rs("UnallocatedSpace")
sUsedSpace = CStr(CInt(sDBSize) - CInt(sUnallocSpace)) + " Mb"
Else
sStatus = statusUnknown
End If
End If
If (sStatus = "") Then
performtest = statusOk & sUsedSpace
Else
performtest = sStatus
End If
rs.Close()
conn.Close()
rs = ""
conn = ""
cmd = ""
End Function
------ End VB Script ------------------------
------ Stored Procedure ---------------------
create procedure GetDbSpace @updateusage varchar(5) = false -- Param. for specifying that usage info. should be updated.
as
declare @objname nvarchar(776)
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
database_name nvarchar(776) null,
database_size nvarchar(776) null,
unallocated_space nvarchar(776) null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
Insert into #spt_space (database_name, database_size, unallocated_space)
select db_name(),
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2)),
ltrim(str((@dbsize - (select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))) / @pagesperMB,15,2))
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
update #spt_space
set reserved = (select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select DBname = database_name,
DBsize = database_size,
UnallocatedSpace = unallocated_space,
Reserved = ltrim(str(reserved * d.low / 1024.,15,0)),
Data = ltrim(str(data * d.low / 1024.,15,0)),
IndexSize = ltrim(str(indexp * d.low / 1024.,15,0)),
Unused = ltrim(str(unused * d.low / 1024.,15,0))
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end
return (0) -- GetDbSpace
GO