TempDB Deleted Accidentally

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

  • 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....

  • 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.

  • 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" 😉

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

  • 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.

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

    M&M

  • 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?

    😉

  • 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

  • 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".

  • - That's using NTFS mounting points a presume?

  • 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.


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

  • 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.


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

  • 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.

  • 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 24 total)

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