Directing traffic off a specfic file in a filegroup

• ngreene

  SSC-Addicted

  Points: 461

  More actions

  July 29, 2011 at 8:00 am

  #254487

  Greetings,

  I need to shrink a file by reorganizing the pages in it. I need to do this due to disk space requirments. I do not want to affect transactions running to this database. This file exists in a filegroup that has multiple files. Only one of the files in this filegroup do I need to shrink. Is there a way I can tell SQL server to stop sending new tranasctions to a single file in a filegroup?

  I would think if i can direct transactions away from this file, when i do my shrink it will not impact new transactions coming in because they will be going to a different file in that filegroup.

  Thank You

• Jannie-186227

  SSCrazy

  Points: 2319

  More actions

  July 31, 2011 at 5:26 pm

  #1361334

  Hi

  You can do a shrinkfile with emptyfile.

  It will move the data in your file two the other two files and mark it as read only.

  Depending on your kit it will be noticable or not.

  I've done it on relatively busy systems without too much trouble. This operation is a bit permanent and prepares the file to be removed.

  Once it's marked for read only you have to remove it and add a new one back if you want to use this drive again.

  Cheers

  Jannie