Monitoring database space usage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 28 Mar 2008 15:58 GMT

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

Reply to this Message

Sean - 28 Mar 2008 16:31 GMT

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

> 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
[quoted text clipped - 8 lines]
> Thanks,
> Mark

Reply to this Message

Jeffrey Williams - 28 Mar 2008 20:56 GMT

> Mark,
>
[quoted text clipped - 33 lines]
>
> - 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

Reply to this Message