Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
MarkMark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that ar=e
> within 20% of capacity. =A0If one or more exists, I get an email. =A0I don='t
> need/want it to monitor constantly as our business processes do not requir=e
> that. =A0I do not want to have to manually monitor. =A0For other business
> reasons, our databases will be set to fixed size with autogrowth disabled,=
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark|||On Mar 28, 8:31=A0am, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
>
> > I'm interesting in monitoring database space usage in SQL Server 2005.
> > Ideally, once a day I'd like a job to run that looks for databases that =are
> > within 20% of capacity. =A0If one or more exists, I get an email. =A0I d=on't
> > need/want it to monitor constantly as our business processes do not requ=ire
> > that. =A0I do not want to have to manually monitor. =A0For other busines=s
> > reasons, our databases will be set to fixed size with autogrowth disable=d,
> > hence our interest in monitoring.
> > What do you recommend?
> > Thanks,
> > Mark- Hide quoted text -
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff