TempDB Deleted Accidentally

Question

Post reply

TempDB Deleted Accidentally

Tushar Kanti

Ten Centuries

Points: 1145
More actions
May 3, 2011 at 10:57 pm

#239544

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

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

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

Mr. hoek Old Hand Points: 378 More actions · Answer 1

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 More actions · Answer 2

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: 234013 More actions · Answer 3

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 More actions · Answer 4

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

Resender SSCertifiable Points: 7443 More actions · Answer 5

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: 21700 More actions · Answer 6

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 More actions · Answer 7

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: 1145 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

Mr. hoek

Old Hand

Points: 378

May 4, 2011 at 5:19 am

#1319645

- That's using NTFS mounting points a presume?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 12

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)

DeadScorpius SSC Veteran Points: 221 More actions · Answer 13

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: 7443 More actions · Answer 14

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.