InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:01:58 GMT20http://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxYou may like to see some TF like 3609 / 8 / 7 these come handy.Sat, 10 Mar 2012 08:04:41 GMTyuphttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspx@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.TusharWed, 04 May 2011 20:21:32 GMTtushkieeeeehttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxNice 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. :-DWed, 04 May 2011 12:48:06 GMTtigermethttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxIn-addition to above, i think this needs a correction:Article says: [b]I stopped the SQL Server Agent from the configuration manager. [/b]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 SinghWed, 04 May 2011 12:34:09 GMTSarab_SQLGeekhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxWell 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.Wed, 04 May 2011 10:11:12 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspx[quote][b]Resender (5/4/2011)[/b][hr]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.[/quote]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.Wed, 04 May 2011 09:43:33 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxNice 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.Wed, 04 May 2011 09:26:19 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxFor 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 [url=http://msdn.microsoft.com/en-us/library/ms190737.aspx]http://msdn.microsoft.com/en-us/library/ms190737.aspx[/url] for moreWed, 04 May 2011 08:22:28 GMTAldo Gonzalezhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxThanks for posting! The article was nicely laid out and easy to read for those of us who weren't aware of such things.Wed, 04 May 2011 08:16:27 GMTmpeters 68241http://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxThanks for sharing your experienceWed, 04 May 2011 08:06:47 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxThis 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.Wed, 04 May 2011 07:24:10 GMTResenderhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxI 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.Wed, 04 May 2011 07:06:14 GMTLance Regerhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxBTW... 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. ;-)Wed, 04 May 2011 07:00:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxI [font="Arial Black"][i]really [/i][/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. :-)Wed, 04 May 2011 06:58:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspx- That's using NTFS mounting points a presume?Wed, 04 May 2011 05:19:52 GMTMr. hoekhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxThis 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".Wed, 04 May 2011 03:49:49 GMTabhijit.vedak 76782http://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxThankyou 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. TusharWed, 04 May 2011 02:56:50 GMTtushkieeeeehttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxHandy 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?;-) Wed, 04 May 2011 02:23:25 GMTPeter Pirkerhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxNice article, but I thought there would be something more than starting sql server in single user mode :)Wed, 04 May 2011 02:19:11 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxI'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.Wed, 04 May 2011 01:57:10 GMTResenderhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxOf course - thanks for mentioning this (sometimes one types faster then on thinks :-P)Wed, 04 May 2011 01:54:56 GMTMr. hoekhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxthe 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 doneWed, 04 May 2011 01:40:27 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxSQL server doesn't care much where you put stuff, only that the path existso you could also use good old dos command SUBST e.g.:Create a folder c:\temp\data whith the correct ntfs permissionsSUBST g: c:\temp\Start SQL server ServiceStart SSMSmove tempdb to somewhere safeetc.when finished:SUBST g: /dRob.Wed, 04 May 2011 00:59:47 GMTsqlsc-790406http://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxWhy not just 'create' the G: drive?- ex: subst g: &lt;anypath&gt;Then one does not have to run the SQL in Single User mode and such....Wed, 04 May 2011 00:52:34 GMTMr. hoekhttp://www.sqlservercentral.com/Forums/Topic1102852-2690-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Administration/72835/">TempDB Deleted Accidentally</A>[/B]Tue, 03 May 2011 22:57:08 GMTtushkieeeee