data object allocation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

data object allocation

hi, i struct in finding out the data object allocation to specific files present in the defaualt file group. Let me explain with example there is a filegroup having two data files with it. i.e a_file and b_file the configuration of file is as follows:- 1.) a_file = it max size is ristricted some limit 2.) b_file = it max size is unlimited. After the size is full in a_file , though it is ristricted data started allocating into b_file. After some time we deleted some data which is present in a_file resulting in free space available in a_file. Now let me know how we can switch over from b_file to a_file . (not from enterprize manager, need some what automated solution, wanna create program for this)
With Regards

Try DBCC SHRINKFILE(‘b_file’, EMPTYFILE). That assumes that you have enough free space on the a_file to hold the contents of the b_file. And also, after using the EMPTYFILE command, data will not be written to the b_file so you might as well delete the b_file. Karl Grambow
hi Karl Grambow i got ur point but my requirement is not for shifting of data from one file to another, i just want to change the allocation of further data from b)file to a_file so that the free space present in the a_file is occupied. shiv

Ah ok, in that case all you need to do is shrink the b_file down as much as is physically possible and then specify that you do NOT want the file to auto-grow. In that way, all of the data will be forced into the a_file. I don’t know if there’s another way of forcing data to be allocated to a certain file – don’t think there is. Karl Grambow
thanx karl i got ur poibt.
Without EM, you have to use ALTER DATABASE. Check all options in BOL.
Luis Martin
Moderator One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.