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,
Mark

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 s,
-Sean

On Mar 28, 10:58am, "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. 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,
> Mark

On Mar 28, 8:31am, 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 s,
> -Sean
>
> On Mar 28, 10:58am, "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. 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,
> > 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