"Prashant Thakwani" <th******@rediffmail.com> wrote in message
news:bf*************************@posting.google.co m...
Consider a scenario, I am a DBA for the Database server where the
size of the hard disk is 100 GB. On that database server, i have the
Database whose size is 40 GB. Also, i restrict the size of the
database file to 95 GB, by specifying the "maximum file size" to
"Restrict file growth" option , under the "file properties".
I then copied a DAT ( or some other file ) on the same database server
that is having the size of 45 GB.
Now when the Database file size increases, it will give an error not
because of setting the "Restrict file growth" option to 95 but there
is no space on the Hard Disk.
My question to you DBAs is that, how will i know that hard disk is
becoming full ? I want to get noticed when the Server's Hard Disk size
is at some specified threshold value.
Pls help in this regard.
There are (at least) two general options - address this as a generic systems
issue, or as a specific MSSQL issue. If you look at it as a systems issue,
you can use any monitoring tool which tracks disk usage, or you can set an
Alert on the LogicalDisk:Free Megabytes counter using Perfmon. You might
already have a tool in your organization that can do this, or you could
create a generic Perl/VB/VBScript etc. script to do it.
Alternatively, if you need to provide your own solution specific to MSSQL,
you could use xp_cmdshell and/or xp_fixeddrives to get the free space on the
drive, then do something (note - xp_fixeddrives is not documented, so it
would probably be a bad idea to rely on it). Personally, I would prefer the
first solution, as it is more generic, you can reuse it for non-MSSQL
servers, and getting OS-level information is often easier outside the
database.
Simon