December 13, 2011 at 2:21 am
For my database , i have 9 data files and 1 log file kept across 10 drives (1 file in 1 drive )
3 data files have touchec it maxm growth and now i have unchecked the autogrowth for these files .
What should be done to avoid any mishappening in the nearby future .
I dont want to opt for shrinking of data files as this is a prod database .
TIA ...\M/
December 13, 2011 at 3:02 am
Is all your nine data files in the same filegroup, or in different filegroups? SQL Server use a kind of round-robin algorithm called proportional fill to allocate disk space to files in a filegroup. If you have three data files in a filegroup, each of these will grow in a circular manner, and they will always grow according to their growth settings. To avoid having SQL Server filling up all the space on one volume, you'll have do do some maths. Again, if the drives of the three files above have the same amount of free space, they shall grow my the same amount (MB/GB) to avoid one disk running full first. If one of the volumes has twice as much free space, the file on this drive should grow with twice the amount of the two others..
December 13, 2011 at 8:25 am
The filegroups matter. If you have other files in the filegroups that contain the full files, then the other files will be receive data. SQL Server sends data to filegroups. The filegroups use the proportional algorithm listed above to send data to the files.
example:
Filegroup 1
- File 1 on D:
- File 2 on E:
Filegroup 2
- File 3 on F:
If File 2 fills the E: drive and there is no space, then all data additions will go to file 1.
If File 3 fills up, and there is an object that needs new data in Filegroup2, then there will be an error generated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply