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