Monitoring disk space

I am trying to write a script to properly monitor disk space for MSSQL
server.
Is there a stored procedure that tells me the separate space usage of
the database and the log?
The procedure sp_spaceusage seems to give an "overall" figure.
However, is it not possible (albeit unwise) that the user could create
the log on a separate filesystem, maybe even with a fixed file size,
so the log could potentially run out of space whilst there is still
space available overall.
Any thoughts?
thanks,
NeilConsider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
so use at your own risk...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:3631594f-2ecb-44db-8dd5-c0e1346ed7e4@.m34g2000hsb.googlegroups.com...
>I am trying to write a script to properly monitor disk space for MSSQL
> server.
> Is there a stored procedure that tells me the separate space usage of
> the database and the log?
> The procedure sp_spaceusage seems to give an "overall" figure.
> However, is it not possible (albeit unwise) that the user could create
> the log on a separate filesystem, maybe even with a fixed file size,
> so the log could potentially run out of space whilst there is still
> space available overall.
> Any thoughts?
> thanks,
> Neil|||> Consider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
> so use at your own risk...
>
Thanks, that got it.
I am using DBCC SQLPERF(LOGSPACE) and a query on the SYSFILES table.

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. Is
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, Pancho
You can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho
|||Hi
There is a script on
http://www.sqlservercentral.com/Scri...0&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:

> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily. Is
> there an automated way or Administrative Tool to monitor the decline of free
> disk space on 4 logical drives? Thanks, Pancho
|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...[vbcol=seagreen]
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scri...0&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. I
s
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, PanchoYou can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho|||Hi
There is a script on
http://www.sqlservercentral.com/Scr...20&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:

> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from m
y
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
Is
> there an automated way or Administrative Tool to monitor the decline of fr
ee
> disk space on 4 logical drives? Thanks, Pancho|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...[vbcol=seagreen]
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scr...20&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:
>

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. Is
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, PanchoYou can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho|||Hi
There is a script on
http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=Maintenance%20and%20Management%20&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily. Is
> there an automated way or Administrative Tool to monitor the decline of free
> disk space on 4 logical drives? Thanks, Pancho|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=Maintenance%20and%20Management%20&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:
>> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
>> manually checking it each day and comparing growth of the .bak file from
>> my
>> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
>> Also, I have 4 other drives with non-DB image files that are added daily.
>> Is
>> there an automated way or Administrative Tool to monitor the decline of
>> free
>> disk space on 4 logical drives? Thanks, Pancho

Monitoring Disk activity

Whats the best perfmon counter to use to check how many
bytes are being read and written to disk/sec ?
I know most of the read and writes take place within a
virtual buffer cache, but what triggers a read/write from
disk .. I am currently running SQL 2000 if that helps
The Physical Disk: Bytes Read(and Write) / Sec will give you just that. You
can also use fn_virtualfilestats() to get totals.
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps
|||Hi,
Add on, Have a look into this link.
http://www.sql-server-performance.co...ounters_io.asp
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#bLPn2sXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> The Physical Disk: Bytes Read(and Write) / Sec will give you just that.
You
> can also use fn_virtualfilestats() to get totals.
> --
> Andrew J. Kelly SQL MVP
>
> "Jamie" <anonymous@.discussions.microsoft.com> wrote in message
> news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
>
|||I generally start off by observing Physical Disk queue length counters as
this tells you whether the disk subsystem is keeping up with the i/o
requests made by sql (or other apps on the same box) through windows. This
is because i/o requests are queued by windows against the i/o subsystem and
if that queue is too large (more than a few i/o's per phsyical disk) or
growing, you have a clear indication that the disk is a bottleneck (not
keeping up).
If you do see a problem here, you next need to ascertain whether the amount
of work being sent to the disk is excessive, due to inefficient query plans
etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps

Monitoring Disk activity

Whats the best perfmon counter to use to check how many
bytes are being read and written to disk/sec ?
I know most of the read and writes take place within a
virtual buffer cache, but what triggers a read/write from
disk .. I am currently running SQL 2000 if that helpsThe Physical Disk: Bytes Read(and Write) / Sec will give you just that. You
can also use fn_virtualfilestats() to get totals.
--
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps|||Hi,
Add on, Have a look into this link.
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#bLPn2sXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> The Physical Disk: Bytes Read(and Write) / Sec will give you just that.
You
> can also use fn_virtualfilestats() to get totals.
> --
> Andrew J. Kelly SQL MVP
>
> "Jamie" <anonymous@.discussions.microsoft.com> wrote in message
> news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> > Whats the best perfmon counter to use to check how many
> > bytes are being read and written to disk/sec ?
> >
> > I know most of the read and writes take place within a
> > virtual buffer cache, but what triggers a read/write from
> > disk .. I am currently running SQL 2000 if that helps
>|||I generally start off by observing Physical Disk queue length counters as
this tells you whether the disk subsystem is keeping up with the i/o
requests made by sql (or other apps on the same box) through windows. This
is because i/o requests are queued by windows against the i/o subsystem and
if that queue is too large (more than a few i/o's per phsyical disk) or
growing, you have a clear indication that the disk is a bottleneck (not
keeping up).
If you do see a problem here, you next need to ascertain whether the amount
of work being sent to the disk is excessive, due to inefficient query plans
etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps

Monitoring Disk activity

Whats the best perfmon counter to use to check how many
bytes are being read and written to disk/sec ?
I know most of the read and writes take place within a
virtual buffer cache, but what triggers a read/write from
disk .. I am currently running SQL 2000 if that helpsThe Physical Disk: Bytes Read(and Write) / Sec will give you just that. You
can also use fn_virtualfilestats() to get totals.
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx
.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps|||Hi,
Add on, Have a look into this link.
http://www.sql-server-performance.c...counters_io.asp
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#bLPn2sXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> The Physical Disk: Bytes Read(and Write) / Sec will give you just that.
You
> can also use fn_virtualfilestats() to get totals.
> --
> Andrew J. Kelly SQL MVP
>
> "Jamie" <anonymous@.discussions.microsoft.com> wrote in message
> news:23a5a01c45ecc$0b2044e0$a301280a@.phx
.gbl...
>|||I generally start off by observing Physical Disk queue length counters as
this tells you whether the disk subsystem is keeping up with the i/o
requests made by sql (or other apps on the same box) through windows. This
is because i/o requests are queued by windows against the i/o subsystem and
if that queue is too large (more than a few i/o's per phsyical disk) or
growing, you have a clear indication that the disk is a bottleneck (not
keeping up).
If you do see a problem here, you next need to ascertain whether the amount
of work being sent to the disk is excessive, due to inefficient query plans
etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx
.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps

Monitoring Cache

In most of the projects/companies these days, Disk space is not an issue, but
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PP
You can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>

Monitoring Cache

In most of the projects/companies these days, Disk space is not an issue, bu
t
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PPYou can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>

Monitoring Cache

In most of the projects/companies these days, Disk space is not an issue, but
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PPYou can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
--
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>