Moving TempDB - SQL School Video

  • Brian Knight

    One Orange Chip

    Points: 27249

    Comments posted to this topic are about the item Moving TempDB - SQL School Video

  • Anipaul

    SSC-Insane

    Points: 24681

    Good one...:)

  • maurine

    Mr or Mrs. 500

    Points: 550

    Thanks, Brian. Very easy to understand and follow based on your presentation.

  • touchwithme2

    Grasshopper

    Points: 19

    Hi,

    It was quite good to follow...i have query on this, whether we can move the temp db in production server on business hours.

    Plz update on this ....

    Waiting for your reply.

    Thanks and Regards

    Eswari.S

  • Shane Clarke

    SSC-Addicted

    Points: 443

    Hi

    Thanks. Easy and straight to the point.

    How would you do it on a SQL cluster (active/passive)? Would you need to run it while each node is active?

    Thanks

    Shane

  • dineshvishe

    SSCrazy

    Points: 2248

    Nice..............

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88053

    touchwithme2 (11/18/2009)


    Hi,

    It was quite good to follow...i have query on this, whether we can move the temp db in production server on business hours.

    Plz update on this ....

    Waiting for your reply.

    Thanks and Regards

    Eswari.S

    No, you cannot move the tempdb (or any database files) without an outage. For normal databases, moving the files just requires that database to be offline. For system databases, moving the files requires a restart (at a minimum) of SQL Server.

    For tempdb, you can make the changes to the system tables at any time - then at the next restart of SQL Server the files will be created in the new location. Be aware that if you make a mistake on the path for tempdb (e.g. typo in the directory name) - SQL Server will not start and you will have to start SQL Server in single-user mode to fix.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Shane Clarke (6/21/2012)


    How would you do it on a SQL cluster (active/passive)? Would you need to run it while each node is active?

    Same way. No, just once. The settings are stored in master and master will be on shared storage.

    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

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

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