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,
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 helps,
-Sean
On Mar 28, 10:58Xam, "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. XIf one or more exists, I get an email. XI don't
> need/want it to monitor constantly as our business processes do not require
> that. XI do not want to have to manually monitor. XFor 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:31Xam, 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:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
>
>
>
> - 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

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

Monitor for 60% server utilization...

I want to monitor a SQL server to alert me when the
server is at 60% utilization. Ideally I want to know
which counters to look for, such as total memory, %
Processor Time, Available memory, anything you can think
of that is relevant. I know sql performance jumps but
maybe over a 15 or 30 second interval, average the
counters and if they average over 60% to send out an
alert.
Question one: Which Perfmon counters would you watch for
to determine what percentage utilized the server is
running at.
Question two: Can you think of an easier way to find
server utilization. Where I can say, "Check this, if its
at this level your server is at 60% utilized."
Question three: Can you tell perfmon to monitor counters
and average them over 30 second intervals and send alerts
when any consecutive 30 seconds maintains 60% utilized.
Are you looking to identify a problem? 60% of what? Disk? Memory? CPU? ?
"Tim" <anonymous@.discussions.microsoft.com> wrote in message
news:326e01c47e89$58f00200$a401280a@.phx.gbl...
>I want to monitor a SQL server to alert me when the
> server is at 60% utilization. Ideally I want to know
> which counters to look for, such as total memory, %
> Processor Time, Available memory, anything you can think
> of that is relevant. I know sql performance jumps but
> maybe over a 15 or 30 second interval, average the
> counters and if they average over 60% to send out an
> alert.
> Question one: Which Perfmon counters would you watch for
> to determine what percentage utilized the server is
> running at.
> Question two: Can you think of an easier way to find
> server utilization. Where I can say, "Check this, if its
> at this level your server is at 60% utilized."
> Question three: Can you tell perfmon to monitor counters
> and average them over 30 second intervals and send alerts
> when any consecutive 30 seconds maintains 60% utilized.
|||Memory and CPU. Just trying to figure out when the
server is going to needs some help, Either more memory or
another SQL Server.
>--Original Message--
>Are you looking to identify a problem? 60% of what?
Disk? Memory? CPU? ?
>
>"Tim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:326e01c47e89$58f00200$a401280a@.phx.gbl...
think[vbcol=seagreen]
for[vbcol=seagreen]
its[vbcol=seagreen]
counters[vbcol=seagreen]
alerts[vbcol=seagreen]
utilized.
>
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Memory and CPU. Just trying to figure out when the
> server is going to needs some help, Either more memory or
> another SQL Server.
Or more tuning...
David G.
|||Hi,
1] I often use the following counters to get an initial overview of CPU
and memory :
For CPU :
Processor.%ProcessorTime, and Process.%ProcessorTime[SQLServr]. This
tells me how busy the server is, and whether it is being used by SQL Server.
System.ProcessorQueueLength. Should not be more than 2 per processor.
For Memory :
Memory.PageFaults/s. This should be low, otherwise your system is
constantly going to disk for memory
SQLServer:BufferManager.BufferCacheHitRatio. This should be high,
meaning SQL is finding all its data in memory.
Total Memory and Available memory are not that meaningfull, as SQL
doesnt release memory until another application needs it.
2] There are some really good tools out there that monitor these
counters, and raise alerts when thresholds are reached. I've used Quest
Spotlight, and BMC's DBXray.
3] Some of the perfmon counters are averaged over your sample interval,
others are a 'snapshot' at the time the sample is taken. I dont think
this is configurable.
If you want to be alerted when a threshold value is exceeded, you can
configure perfmon "alerts" to alert you.
thanks
Ian
iank@.iworks.co.za
Tim wrote:
> I want to monitor a SQL server to alert me when the
> server is at 60% utilization. Ideally I want to know
> which counters to look for, such as total memory, %
> Processor Time, Available memory, anything you can think
> of that is relevant. I know sql performance jumps but
> maybe over a 15 or 30 second interval, average the
> counters and if they average over 60% to send out an
> alert.
> Question one: Which Perfmon counters would you watch for
> to determine what percentage utilized the server is
> running at.
> Question two: Can you think of an easier way to find
> server utilization. Where I can say, "Check this, if its
> at this level your server is at 60% utilized."
> Question three: Can you tell perfmon to monitor counters
> and average them over 30 second intervals and send alerts
> when any consecutive 30 seconds maintains 60% utilized.