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
Shiv

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 www.sqldbcontrol.com

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 www.sqldbcontrol.com

thanx karl i got ur poibt.

Without EM, you have to use ALTER DATABASE. Check all options in BOL.
Luis Martin
Moderator
SQL-Server-Performance.com 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.

]]>