SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dbcc shrinkfile


dbcc shrinkfile

Author
Message
muth_51
muth_51
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2579 Visits: 2930
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?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54735 Visits: 17694
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" ;-)
DBA328
DBA328
SSC Eights!
SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)

Group: General Forum Members
Points: 898 Visits: 1123
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

[li][/li]
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227219 Visits: 46335
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

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


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54735 Visits: 17694
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" ;-)
DBA328
DBA328
SSC Eights!
SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)

Group: General Forum Members
Points: 898 Visits: 1123
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?

[li][/li]
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54735 Visits: 17694
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" ;-)
DBA328
DBA328
SSC Eights!
SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)

Group: General Forum Members
Points: 898 Visits: 1123
Thanks Perry.

[li][/li]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search