More on drive space

xp_fixeddrive works fine for getting the free space of a drive, but is there
a clean way to get the size of the drive? chkdsk and fsutil through
xp_cmdshell have been ruled out.There are couple of ways of doing that: You can use SMO/DMO based on SQL
Server version. You can use Powershell/VBScript and get a snapshot of "My
Computer"
Another ugly way of doing is (I dont' recommend) (You can run SRVINFO from
NT resource kit and spit the output in a textfile and get that info in SQL
using SP_ReadErrorLog)
However, there's a good script on how to get SQL Server data/log utilization
report from each drive @.
http://sqlcommunity.com/Default.aspx?grm2id=49&tabid=56
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQL Tips, Scripts, Articles, Forums, Radio and a lot of SQL Server fun.
"Jay" wrote:
> xp_fixeddrive works fine for getting the free space of a drive, but is there
> a clean way to get the size of the drive? chkdsk and fsutil through
> xp_cmdshell have been ruled out.
>
>|||Well Saleem, nice little script except for a couple things.
- It doesn't pull the capicity of the drive.
- You posted a copy with errors in it.
Intresting site though.
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:A8CDF944-53AB-4EC1-9CFC-A27AE1F9E0FE@.microsoft.com...
> There are couple of ways of doing that: You can use SMO/DMO based on SQL
> Server version. You can use Powershell/VBScript and get a snapshot of "My
> Computer"
> Another ugly way of doing is (I dont' recommend) (You can run SRVINFO from
> NT resource kit and spit the output in a textfile and get that info in SQL
> using SP_ReadErrorLog)
> However, there's a good script on how to get SQL Server data/log
> utilization
> report from each drive @.
> http://sqlcommunity.com/Default.aspx?grm2id=49&tabid=56
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
> SQL Tips, Scripts, Articles, Forums, Radio and a lot of SQL Server fun.
>
> "Jay" wrote:
>> xp_fixeddrive works fine for getting the free space of a drive, but is
>> there
>> a clean way to get the size of the drive? chkdsk and fsutil through
>> xp_cmdshell have been ruled out.
>>|||Hi Jay,
I think this is what you are looking for:
Get the facts about your drives and SQL Server space utilization from SQL
Server: http://www.sqlcommunity.com/Default.aspx?grm2id=53&tabid=56
Hope this helps.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"Jay" wrote:
> Well Saleem, nice little script except for a couple things.
> - It doesn't pull the capicity of the drive.
> - You posted a copy with errors in it.
> Intresting site though.
>
> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
> news:A8CDF944-53AB-4EC1-9CFC-A27AE1F9E0FE@.microsoft.com...
> > There are couple of ways of doing that: You can use SMO/DMO based on SQL
> > Server version. You can use Powershell/VBScript and get a snapshot of "My
> > Computer"
> >
> > Another ugly way of doing is (I dont' recommend) (You can run SRVINFO from
> > NT resource kit and spit the output in a textfile and get that info in SQL
> > using SP_ReadErrorLog)
> >
> > However, there's a good script on how to get SQL Server data/log
> > utilization
> > report from each drive @.
> > http://sqlcommunity.com/Default.aspx?grm2id=49&tabid=56
> >
> > Thank you,
> > Saleem Hakani
> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
> > SQL Tips, Scripts, Articles, Forums, Radio and a lot of SQL Server fun.
> >
> >
> > "Jay" wrote:
> >
> >> xp_fixeddrive works fine for getting the free space of a drive, but is
> >> there
> >> a clean way to get the size of the drive? chkdsk and fsutil through
> >> xp_cmdshell have been ruled out.
> >>
> >>
> >>
>
>sql

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.codecomments.com ***
Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:

>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks, Sue. This looks like what I need!
*** Sent via Developersdex http://www.codecomments.com ***

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.developersdex.com ***Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:
>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.developersdex.com ***

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.codecomments.com ***Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:

>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks, Sue. This looks like what I need!
*** Sent via Developersdex http://www.codecomments.com ***

Monitoring the db size

Hi All,

I'm wonderring if someone has the script which can run on each server to get all dbs size , free space on this server ? Curently I am using the enterprise manager to check the db space usage manually, but this is very frustrated due to a server has many dbs located on it

Thanks,Hi All,

I'm wonderring if someone has the script which can run on each server to get all dbs size , free space on this server ? Curently I am using the enterprise manager to check the db space usage manually, but this is very frustrated due to a server has many dbs located on it

Thanks,

This (http://www.dbforums.com/t1006334.html) might be what you are looking for...

[Edit: some caveats]
1. Consider also using DBCC SHOWCONTIG WITH TABLERESULTS. You could embed this in the sp_MSForEachDB.
2. Note that the data may not match reality; you may need to run dbcc updateusage before you run the sizing extract.
3. YMMV

Regards,

hmscott|||Thanks a lot hmscott, I'll try it. I found a website which has a few scripts , they are very good, for people referecn if need auto manage db size, drive size and free space. It is:

http://www.sqlservercentral.com/columnists/mnash/monitoringdriveanddatabasefreespace.asp

Monitoring remaining space?

Is there a way to send e-mail alerts on the remaining space based on the
percentage space remaining?
I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
transactions & tempdb queries don't chew up the server resources/disk
space), and I want to monitor the data files for percentage use as well
before they grow. I was also thinking about using raw devices for the
tranlogs.
Thanx in advance,
/lYou can get some stuff from www.dbmaint.com (free utilities). There are two stored procedures that
can be of interest. One warn you if there is less then n% free space. The other one warn if you had
autogrow. You might need to modify them to suit your needs.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>|||Thanx!!!|||Is probably overkill for you requirements but we use MOM to do just that...
--
HTH
Ryan Waight, MCDBA, MCSE
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>|||> I was also thinking about using raw devices for the tranlogs.
In general, that is not advisable. NTFS files are must easier to manage.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
Mark
SQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>
|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script thi
s?
> Thanks in advance.
> Mark
>
You may want to look at
[url]http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx[/u
rl]
John

Monitoring Filegroup Space

Does anyone know of a way to predict that the files in a filegroup do not
have enough space left to expand? For example, a file has 3753 MB out of a
maxsize of 4096, with a file growth of 10%. Adding 10% to 3753 will exceed
the max file size, so there is no room to expand even though technically
there is space available. There are 80 such files in the filegroup and so i
t
can be deceptive when you look at the total number of MB left.Hi Eydba,
Welcome to the MSDN newsgroup.
Regarding on the filegroup space question you mentiond, based on my
research, I'm afraid so far there is not such built-in feature which can
help detect the potential exceeding of the file size in filegroup. Also,
there is no similiar trigger or notification on this. For make the
filegroup filesize among appropriate range, you can have a look some
related performance tuning articles:
#Optimizing SQL Server Performance by using File and Filegroups
http://www.mssqlcity.com/Articles/T...leOptimTips.htm
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||As noted, there's no way SQL Server triggers this itself, but you can
set up monitoring around it.
Option 1: Write a stored procedure that checks this, run it from a
scheduled sql job, and monitor for failure. The info you need to do the
math is returned by sp_helpfile.
Option 2: Write a small .NET application that connects through the SMO.
Through the SMO you can retrieve the file size, growth setting, and max
size and then do some math and write to the event log or throw an alert
another way if it doesn't have room to grow.
Any way you did it, you need to run DBCC updateusage frequently for the
estimates of space used to be accurate.
-Kendra

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

Monitoring Data file size

I am looking to automate monitoring space used for each file in each
database on a SQL Server 2000. Does anybody have any SQL Scripts to do
this or to find the space used?I'm not aware of a good/easy way from TSQL. You can use sp_spaceused to
find out how much space is available in the database as a whole - since
MSSQL aims to use each file more or less evenly, that might be good
enough to give you an idea.

Using SQLDMO, however, the DBFile object has a SpaceAvailableInMB
property, which will tell you exactly how much space is free. Behind
the scenes, it calls DBCC SHOWFILESTATS, which is undocumented.

In any case, since MSSQL aims to use the files roughly equally, it's
often more important to know how much free space is in the database as
a whole (sp_spaceused), or in the transaction log (DBCC SQLPERF).

Simon

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
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.
Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>
|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>>I want an SQL script that will monitor free space on certain drives of
>> my SQL2000 server.
>> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
>> a
>> SELECT freespace FROM xxxx WHERE (mydrive='C')
>> method that I can use?
>>
>> TIA
>> Dave.
>