TempDB Deleted Accidentally

Question

TempDB Deleted Accidentally

Viewing 10 posts - 16 through 24 (of 24 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for sharing your experience

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mpeters 68241 SSC Enthusiast Points: 121 More actions · Answer 2

Thanks for posting! The article was nicely laid out and easy to read for those of us who weren't aware of such things.

Aldo Gonzalez SSC Enthusiast Points: 149 More actions · Answer 3

For reference, we should clarify that -f starts SQL Server minimally configured and in single user mode. Both features are needed to the maintenance work being done here, that is, moving tempdb. However, there is a startup option just for single user mode, -m, but that would not be enough for the work being done here.

See http://msdn.microsoft.com/en-us/library/ms190737.aspx for more

SanDroid SSChampion Points: 10068 More actions · Answer 4

Nice article on how to save you self with single user mode.

As has already been menationed know to use the -f switch is a question every DBA should ready for when in an interview.

This used to be something I would help developers do often before we started using a single "real" SQL server for development databses.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

Resender (5/4/2011)
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.

If the files get renamed/deleted it won't make a difference at all since SQL recreates the database on start. So you're set as long as SQL can write to the location.

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 6

Well done, thank you! I don't care how many times single user mode has been written about. We all can use another reminder, because this is not something people use on a daily basis.

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 7

In-addition to above, i think this needs a correction:

Article says: I stopped the SQL Server Agent from the configuration manager.

Actually, the status of SQL Server Agent should be stopped anyways :hehe:(remember there is a dependency on SQLServer)

In-fact you should make it disable or manual to save that single connection of SQL Server.

Thanks,

Sarabpreet Singh

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

tigermet Grasshopper Points: 15 More actions · Answer 8

Nice article. I've been a DBA for about 15 years, and it's good to see stuff like this, even if it has already been written about many times. Well done.

I also managed a DBA group for about 5 years, and yes, little questions like this do come up in the interviews. 😀

Tushar Kanti Ten Centuries Points: 1145 More actions · Answer 9

@Lance Reger.. The thing is that the tempdb is created every time the SQL Server restarts taking the database template from tempdb. But if the location is not present then the tempdb cannot be re-created. Matter of fact that the tempdb is deleted everytime SQL Server is shutdown. So accidently deleting it means there is no re-covery back directly. Hope I answered the question put forward.

Tushar

yup SSCarpal Tunnel Points: 4490 More actions · Answer 10

You may like to see some TF like 3609 / 8 / 7 these come handy.