Post reply

move datafile to a different disk

  • February 28, 2014 at 5:04 am

    #286202

    Guys,

    I have a requirement to move one of the datafiles (140gb) to a different disk. Is there anything i need to be concerned of for this change. I'm planning to execute alter db modify file and putting new path and restarting sql.

    Please advise. This is a production server.

  • February 28, 2014 at 5:19 am

    #1693002

    ALTER DATABASE ... MODIFY FILE

    Take the DB offline (no need to restart SQL)

    Copy the file to its new location

    Bring the DB online

    Practice on a non-production server first.

    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

  • February 28, 2014 at 8:22 am

    #1693090

    Thanks Gila..do you think we need to keep the app offline for this operation.

  • February 28, 2014 at 9:14 am

    #1693137

    I'd keep the app offline yes. Or, you have to deal with killing connections until you can get the database offline, then the app will have X number of errors popping up constantly while it attempts to connect to the database. That's a pretty sloppy approach. Better to just have it offline.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 28, 2014 at 9:18 am

    #1693139

    Do you need to keep the app online? We occasionally move data in secondary files with the app online. painful,slow, but feasible.

  • February 28, 2014 at 9:26 am

    #1693147

    Thanks! I would go with your recommendation.

Viewing 6 posts - 1 through 5 (of 5 total)

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