Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dbcc shrinkfile Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 10:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 419, Visits: 2,121
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?
Post #1374483
Posted Friday, October 19, 2012 2:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1374678
Posted Friday, October 19, 2012 5:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 126, Visits: 1,091
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]
Post #1375063
Posted Saturday, October 20, 2012 3:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1375092
Posted Saturday, October 20, 2012 4:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1375094
Posted Sunday, October 21, 2012 10:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 126, Visits: 1,091
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]
Post #1375244
Posted Sunday, October 21, 2012 11:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1375252
Posted Sunday, October 21, 2012 11:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 126, Visits: 1,091
Thanks Perry.

[li][/li]
Post #1375253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse