FileGroup Out-Of-Space Issues

Question

FileGroup Out-Of-Space Issues

rew-370421

Hall of Fame

Points: 3640
More actions
September 16, 2009 at 2:20 pm

#133991

I have a second question regarding my SQL2005 VENDOR application/database referenced in:
http://www.sqlservercentral.com/Forums/Topic789115-146-1.aspx
This question relates to FileGroup #4 and it's DataFile. If I do a right-click on my Database and select "TASKs/SHRINK/FILES" and select FileGroup #4, it displays the following information:
Currently Allocated Space = 10457.69 MB
Available Free Space = 5421.88 MB (51%)
This means that there is about 5GB of space sQL can use within FG4. Correct?
I have DISALBED AUtoGrowth on FileGroup #4. What specifically happens when the 5GB has been used up and/or SQL wants more space in FileGroup #4?
FYI: I've checked and FileGroup #4 contains ONLY ONE Table (data space=2130mb), which has 5 indexes, 1 clustered and 4 non-clustered (index space=2900mb). The table has 29,638,008 records.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

- if a filegroup gets filled up and cannot auto extend any files , you'll get an errormessages stating "filegroup full".

So you nolonger can add data to it, rebuild indexes, ...

Best you can do is check data space usage for fixed size filegroups by:

- doing it yourself

- using the system alerts (check books online) that do this for you.

goal is to be alerted before the filegroup gets full and respond to it in the apropriate way. (contact application responsible, delete data, extend filegroup, dial 911, ...)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rew-370421 Hall of Fame Points: 3640 More actions · Answer 2

I've never setup an ALERT before and have been looking thru SQL2K5 BOL for specifically how to setup an alert when a specific FileGroup Datafile exceeds a certain threshold (e.g., alert me when FileGroupX is 90% FULL).

Can someone please point me in the right direction? Be it BOL or somewhere else?

thanks!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

have a look at Allens article : http://www.simple-talk.com/sql/backup-and-recovery/alert-based-transaction-log-backups---automate-your-database-maintenance-part-2/