What happens when one disk fills in a filegroups files

Question

What happens when one disk fills in a filegroups files

boringusername

SSC Enthusiast

Points: 185
More actions
September 19, 2012 at 9:34 am

#281787

Take a filegroup with files on two drives F & G.
What is the expected behavior when drive G fills but drive F has plenty of capacity. Will SQLServer still be able to insert to tables in the filegroup even though G is full by using the space in F ?
Taking over a production SQL2005 server, I have a background in DB2, Informix, Postgres, MySQL and Vertica but not a lot of hands on SQLServer2005 yet.
We are expecting to see a drive fill before we can get the file relocated. I'm just trying to figure out whether we should plan on system halting at that point or not. Obviously planning what files to move and to where ASAP.
Thanks!

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

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

SQLmountain SSCommitted Points: 1505 More actions · Answer 1

Without knowing the details of your configuration it is hard to say for sure. I do know that in general, once a file is full, SQL can shift to a new file in the filegroup that has free space.

Once thing that I would like to caution you about is the disk space issue. All sorts of other problems could crop up should the hard disk fill up. Assuming you have autogrowth turned on for the filegroup, you might want to place an upper limit on the growth so that the whole disk does not fill up.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

bitznbitez (9/19/2012)
Will SQLServer still be able to insert to tables in the filegroup even though G is full by using the space in F ?

Yes. Set the file on G not to autogrow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

boringusername SSC Enthusiast Points: 185 More actions · Answer 3

"in general, once a file is full, SQL can shift to a new file in the filegroup that has free space."

Thats what I was looking for. And thanks for the $0.02 on capping auto growth. Realize lots of assumptions in your response and my layout sketch is light.

In other DBMS, DB2 for instance using DMS managed containers, growth can stop if any container/file in a tablespace/filegroup fills up hence my concern.

Thanks!

SQLmountain SSCommitted Points: 1505 More actions · Answer 4

Please consider the option that Gail presents as well.