Post reply

TempDB Test question

  • December 13, 2011 at 10:52 am

    #391115

    Hi,

    We're planning on testing performance of TempDB on a new storage device. If we update master DB with the new location for TempDB, restart SQL Services and for some reason the device fails, (TempDB does not get initialized) what are the options for bringing SQL back up with the orginal TempDB location?

    Thanks

  • December 13, 2011 at 10:54 am

    #1420751

    Err, you don't update master DB you alter tempdb location.

    Assuming this is a test server just come back here to debug. This is a relatively low risk action.

  • December 13, 2011 at 11:13 am

    #1420773

    Ninja's_RGR'us (12/13/2011)

    Err, you don't update master DB you alter tempdb location.

    Assuming this is a test server just come back here to debug. This is a relatively low risk action.

    That's what I meant. What if the server does not come back up? If you can't connect to the server to alter tempdb location, what are the options?

  • December 13, 2011 at 11:17 am

    #1420777

    Lexa (12/13/2011)

    Ninja's_RGR'us (12/13/2011)

    Err, you don't update master DB you alter tempdb location.

    Assuming this is a test server just come back here to debug. This is a relatively low risk action.

    That's what I meant. What if the server does not come back up? If you can't connect to the server to alter tempdb location, what are the options?

    I don't remember the error/procedure offhand. But it's easy to find in google and lots of us have faced this before.

    Easy test is to create a db in the folder in question. If you can access that db, you shouldn't have any permissions issues with tempdb either so that's 99.99% proof that you're fine.

  • December 13, 2011 at 11:47 am

    #1420799

    Lexa (12/13/2011)

    What if the server does not come back up? If you can't connect to the server to alter tempdb location, what are the options?

    You can start up the server in single user restricted mode with a specific traceflag (that I don't recall offhand). That way SQL only recovers Master, not any of the other databases and you can re-issue the ALTER DATABASE for TempDB.

    Best thing is not to make such a mistake in the first place. Double check before you restart that the path for TempDB is correct and SQL has full control of the new folder location and that the SQL service can see it (important on a cluster).

    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

  • December 13, 2011 at 4:45 pm

    #1420939

    GilaMonster (12/13/2011)

    Lexa (12/13/2011)

    What if the server does not come back up? If you can't connect to the server to alter tempdb location, what are the options?

    You can start up the server in single user restricted mode with a specific traceflag (that I don't recall offhand). That way SQL only recovers Master, not any of the other databases and you can re-issue the ALTER DATABASE for TempDB.

    Best thing is not to make such a mistake in the first place. Double check before you restart that the path for TempDB is correct and SQL has full control of the new folder location and that the SQL service can see it (important on a cluster).

    I'm thinking about having a copy of working master.mdf and mastlog.ldf in case I need to replace those.

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

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