October 18, 2012 at 10:22 am
Does dbcc shrinkfile (filename, emptyfile) move the data to other file in the same filegroup thru online or the data will be offline during the moving process?
October 19, 2012 at 2:14 am
muthyala_51 (10/18/2012)
Does dbcc shrinkfile (filename, emptyfile) move the data to other file in the same filegroup thru online or the data will be offline during the moving process?
You may still access the objects during the emptyfile op
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2012 at 5:22 pm
DBCC shirinkfile may not move data to other files. you can virtually move the data from the table to a different filegroup by creating a clustered index and placing it wherever you want
October 20, 2012 at 3:48 am
DBA328 (10/19/2012)
DBCC shirinkfile may not move data to other files. you can virtually move the data from the table to a different filegroup by creating a clustered index and placing it wherever you want
Shrinkfile with the emptyfile option does move data to other files in the filegroup, which is what the OP asked about.
Per BoL:
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
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
October 20, 2012 at 4:15 am
DBA328 (10/19/2012)
DBCC shirinkfile may not move data to other files. you can virtually move the data from the table to a different filegroup by creating a clustered index and placing it wherever you want
DBCC SHRINKFILE won't move data across filegroups, which is what i believe you're referring to, for that you will need to move the actual index\object.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2012 at 10:01 pm
one question about EMPTYFILE paramters:
If i have 4 data files on one filegroup, then i use DBCC SHRINKFILE with emptyfile paramter to empty the first data file, which file these data on file1 will be moved to?
October 21, 2012 at 11:47 pm
DBA328 (10/21/2012)
one question about EMPTYFILE paramters:If i have 4 data files on one filegroup, then i use DBCC SHRINKFILE with emptyfile paramter to empty the first data file, which file these data on file1 will be moved to?
The obvious reason for emptying a file is so that you can delete it. You cannot remove the primary file from the primary file group.
When emptying a file the data will be dispersed to other files in the same file group that have available space.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2012 at 11:50 pm
Thanks Perry.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply