December 5, 2005 at 9:05 am
I try to write some scripts to monitor the db space usage, i found i can use:
USE [mydbname]
sp_spaceused @updateusage = 'TRUE
to get the db usage info, but the db statistic update may take some time (say more than a minute) to finish.
since i would like to monitor all dbs space usage and would like to schedule it every 10 minutes, thus i can not afford the updateusage take so long to finish. Since i can find the space usage info from the db taskpad view in enterprise mananger, i'm wondering where that information coming from? is it coming from a sys tables?
sysindexes is also depends on the updateusage.
any idea about how to monitor the db space usage?
-----------------------------------------------------------------------------
Taskpad view gets data from sysfiles
Select CONVERT(VARCHAR(12),getdate(),111),Name,Size,FileName from Pubs.dbo.sysfiles
Kindest Regards,
Sameer Raval [DBA-Sql Server]
----------------------------------------------------------------------------------
I got the above reply but it's not a solution.
December 5, 2005 at 11:43 am
SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0
DBCC sqlperf(logspace)
DBCC showfilestats [undocumented]
December 5, 2005 at 12:57 pm
Thanks, it works. I just worried about the function DBCC showfilestats since I don't know if it will be cancelled in the future.
Anyway I can use it now.
December 5, 2005 at 1:31 pm
Why don't you just run sp_spaceused without the updateusage part?
-SQLBill
December 5, 2005 at 1:45 pm
It has been there since 6.5 - but I cannot guarantee about future versions.
December 6, 2005 at 10:01 am
Why do you need to monitor database space usage every 10 minutes ? I could see 1 or 2 times a day but every 10 minutes seems a bit on the overkill side. I'm just curious as to the reasoning behind this.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply