KS-Soft. Network Management Solutions
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister    ProfileProfile    Log inLog in 

Determine actual size of MS SQL Server database

 
Post new topic   Reply to topic    KS-Soft Forum Index -> Library
View previous topic :: View next topic  
Author Message
jrweiss



Joined: 01 Mar 2003
Posts: 39

PostPosted: Sat Mar 08, 2003 1:43 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
rathimeena



Joined: 09 Jan 2013
Posts: 3

PostPosted: Wed Jan 09, 2013 9:55 pm    Post subject: Reply with quote

Thanks for sharing
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    KS-Soft Forum Index -> Library All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group

KS-Soft Forum Index