TempDB Deleted Accidentally

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Comments posted to this topic are about the item TempDB Deleted Accidentally

  • Mr. hoek

    Old Hand

    Points: 378

    Why not just 'create' the G: drive?

    - ex: subst g: <anypath>

    Then one does not have to run the SQL in Single User mode and such....

  • sqlsc-790406

    SSC Rookie

    Points: 45

    SQL server doesn't care much where you put stuff, only that the path exist

    so you could also use good old dos command SUBST e.g.:

    Create a folder c:\temp\data whith the correct ntfs permissions

    SUBST g: c:\tempStart SQL server Service

    Start SSMS

    move tempdb to somewhere safe

    etc.

    when finished:

    SUBST g: /d

    Rob.

  • Perry Whittle

    SSC Guru

    Points: 233859

    the article is correct, subst will not work especially if the service runs under a user account. The steps are perfectly reasonable, although i have to say this topic (starting in single user mode) has been done to death.

    For reference, there is no MOVE parameter for ALTER DATABASE you should use MODIFY to alter a file or filegroup. Otherwise very tidy article, well done

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Mr. hoek

    Old Hand

    Points: 378

    Of course - thanks for mentioning this (sometimes one types faster then on thinks :-P)

  • Resender

    SSCertifiable

    Points: 7285

    I've been training to get 70-432 certification and came across this problem as well.

    My personal opinion is that not only should you try and move tempdb to another location the default but his must be well documented.

  • M&M

    SSC-Insane

    Points: 21699

    Nice article, but I thought there would be something more than starting sql server in single user mode 🙂

    M&M

  • Peter Pirker

    Ten Centuries

    Points: 1079

    Handy article, thanks.

    One cannot but admire all the brave DBA's out there who have to live with "a constant fear of losing data because of the mistakes of some developer or support person". Personally, I'm sure I couldn't handle such stress!

    Although, by the sound of it, that's not exactly what happended here. More like errare humanum est, eh?

    😉

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Thankyou All for the comments and views.

    @ Perry Whittle . I just ment to move the files physically and not syntactically.

    @ Grasshopper. I don not think that will be possible as pointed out already.

    Tushar

  • abhijit.vedak 76782

    Valued Member

    Points: 64

    This reminded me of something that happened a few years ago when working on SQL Server 2000. Same scenario. Following is what I did to resolve the issue:

    a) Create a dummy folder (say "X") on a drive having ample space.

    b) Created the same folder hierarchy (\X\Foldername) inside this folder as the path of the folder where tempdb was supposed to be located.

    c) Mapped this newly created folder (X) as the drive (G-drive in this case).

    On starting the SQL Server Service, it created the tempdb on this mapped G-drive which in turn was folder "\X\Foldername".

  • Mr. hoek

    Old Hand

    Points: 378

    - That's using NTFS mounting points a presume?

  • Jeff Moden

    SSC Guru

    Points: 996858

    I [font="Arial Black"]really [/font]like the point of this article whether or not it's been illustrated using a problem that's been done many times or not. Heh... and if Google picks it up along with 100 other instances of the same problem, that's just confirmation to someone that the methods contained may be correct, especially this one since it shows screen shots to give the nice warm fuzzies that someone actually used the steps given. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996858

    BTW... for all you hopeful DBA's looking for a job. This IS a very common interview question. I recommend you sit down on a test box and practice these steps until you have them memorized. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DeadScorpius

    SSC Enthusiast

    Points: 191

    I hate that the "teaser" for the article mentions that if tempdb somehow gets deleted then SQL won't start. Yet, this isn't true, and the article is actually discussing what to do if the location of the tempdb gets deleted.

  • Resender

    SSCertifiable

    Points: 7285

    This isn't that hard few weeks ago I was confronted with the weirdest problem yet.

    The hard disk that contained one of our non-critical databases had been removed by tech support and they ghosted the information onto a new harddrive that got the same drive letter.

    When I arrived at the office and checked the databases the database was both offline and inaccessible, meaning that when I tried to change the mapping back to the files I got SQL Server nagging me that the database was offline and when I tried to online it I got the message that it could not do this cause it had no idea where the files were.

    I tried recreating the database using the existing files but then I got the message that it already existed in the instance, trying to recreate it from the backups resulted in same error.

    So I deleted the database files and all and then I was able to recreate it using the backup.

    So what do you do if tempdb get deleted and not yet its location, the article states what happens if the partition is removed not if the data on that partition was removed.

Viewing 15 posts - 1 through 15 (of 25 total)

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