dbcc shrinkfile

  • 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?

  • 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" 😉

  • 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

  • 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
  • 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" 😉

  • 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?

  • 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" 😉

  • Thanks Perry.

  • Viewing 8 posts - 1 through 7 (of 7 total)

    You must be logged in to reply to this topic. Login to reply