Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2002-06-07 : 20:40:58
|
| Hi,I lost Temdb and now I cannot start SQL Server. How do I recreate TempDb or start SQL Server?ThanksSarat |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-07 : 21:28:58
|
OK, you will have to explain HOW you LOST tempdb! Did you try to DROP DATABASE, or delete the .mdf file for it, or did the hard drive get corrupted? Whatever the hell happened, you'd better make sure that it can't happen again in the future BEFORE you worry about getting your SQL Server restarted.Take a look in Books Online under "startup options" for the available flags used to start SQL Server. You will probably need to use the -f flag for a minimal startup, and go from there. Also look under the "troubleshooting" section of Books Online. If none of these work, contact Microsoft technical support and indicate that your server is down and will not restart. Make sure that you can take the time to work the problem with them, because they WILL NOT let you hang up until the server is working.  |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-06-08 : 03:30:56
|
| Hey Sarat.Send me an email, let me know what happened. -Chad |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-08 : 09:47:50
|
| What version of SQl are you on ?Not that I'd recomend it but in SQL 7 and 2000 you can delete the tempdb files whilst SQL Service is not running and it will start up just fine. This is because every time SQL starts it recreates tempdb from the model database. Are you sure its tempdb thats missing as this should not cause SQL service to not start ?? I'd guess its more a problem with master or model however you need to provide detailsWhat errors are you getting (SQL Error Log\NT Event Log)My basic point is that if the tempdb files are not present this will NOT stop SQL Server Service from starting (SQL 7,2000).HTHJasper Smith |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2002-06-09 : 13:40:33
|
| It is hard disk issue:Our Sys admin forgot to backup tempdb files before adding more space (hard-disk upgrade) and so when he finished re-configuring, there were no mdf and ldf files in the tempdata drive. We are able to start sql server from cmd prompt but cannot fix tempdb because it says server in single user mode. How should we get it back in multi user mode or how to fix this?1.We did this on cmd-prompt:sqlservr -c -f -T3608 -T4022 2.We are now trying to do this in QAnalyzer:exec master..sp_resetstatus TempdbIt says: Tempdb not found.so i can't proceed to next step which is:pressing CTRL+C in the command prompt window. Restart the SQL Server service. This creates a new set of tempdb database files, and recovers the tempdb database. ***************Server Log:Here is the error msg:2002-06-09 10:02:02.69 spid52 FCB::CreateFile() failed with error 3 for file T:\TempData\tempdb.mdf. 2002-06-09 10:02:02.71 spid52 udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device T:\TempData\tempdb.mdf.2002-06-09 10:02:02.71 spid52 FCB::Open failed: Could not open device T:\TempData\tempdb.mdf for virtual device number (VDN) 1.2002-06-09 10:02:02.72 spid52 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.Thanks,Sarat |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-09 : 19:38:16
|
| You still don't say what version you are using but if its >=7 can you confirm what happened .1) SQL Service stopped by you2) Hard drives upgraded/removed which inadvertently removed tempdb files3) You started SQl from CMD prompt Did you try starting SQL service normally before trying to start it from the cmd prompt ?? As I said earlier the fact that the tempdb files are not present will not prevent SQL starting - it deletes them every time it starts anyway. I'm still confused as to why you started sql from the cmd prompt - can you confirm that you tried to start sql normally and it failed ? Also does the T:\Tempdata\ folder still exist in the new configuration ? |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-06-10 : 08:13:47
|
| I would check the availablity of the T:\ - I think you will find it doesn't exist or that you don't have the sub-dir or lack authority to create files there, if they are remote? I hope you've got something personal and painful belonging to the sysadm in a vice... |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-06-10 : 17:35:33
|
| Wanderer, You are correct. SubDir did not exist.Version is 2000-Chad |
|
|
|
|
|
|
|
|
Tempdb Lost - SQL Server doesnot Start