November 16, 2004 at 8:01 pm
On startup of MSSQLSERVER, I see the following logged:
Error: 615, Severity: 21, State: 1
Could not find database table ID 2, name 'tempdb'..
After this, the database seems to go out to lunch, and requires a reboot.
I am running on W2K3, SQL 2000 Dev Edition, 8 CPU, 16GB, -g512
Any ideas on why this would happen would be appreciated
November 16, 2004 at 8:18 pm
Sorry for asking the bloody obvious, but has tempdb been deleted? As you cannot start the SQL Server service, check in Windows Explorer for existence of C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf (well, this is the default path anyway). Or does the full error message suggest that SQL Server cannot find a certain system table within tempdb?
As you cannot reboot a database, I assume you mean the server itself - is that because it hangs?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 16, 2004 at 8:28 pm
It seems your sysdatabases table in master database doesn't have entry for tempdb. Is SQL Server Running? I had same errors when one of my collegue detached tempdb. If SQL Server is not starting then try to start it in single usermode with trace flag -t3608 and then enter database entry for tempdb.
If that doesn't work, and if you have cold backup of master database then try to replace those and then start sql server. If you have SQL Backup of master database then try to restore it on some test server and then replace master.mdf and mastlog.ldf file to your current server's master database files. Make sure you copy the old files before you replace.
If these two doesn't work then last option will be you can attach master database with master.mdf and mastlog.ldf files to different server try to enter entry for tempdb and then copy those files back or you can just extract all the details of your old master database from there if you need to rebuild master database. Rebuilding master will recreate all system databases so you might want to create one copy of msdb database files and master database files to some safe location.
these are all suggestions. I have tried some options, but not all. before you try anything make one copy of master.mdf and mastlog.ldf files.
November 16, 2004 at 8:51 pm
Actually, here is the more complete log. Temp db is present, the entry exists in sysdatabases. As you can see, it has trouble starting, but eventually starts up; have no idea what state tempdb is in when it finally finishes startup. Eventually the server runs, but slowly it seems to degrade in normal functions (like backups, etc). I'm thinking that something is causing the tempdb to startup in a dirty state.
Could it be the memory? I am more & more wary of using large memory on SQL Server & W2k3 configurations. I am using 16GB with /3GB option in W2K3. I applied hotfix Q834628, which is referenced in http://support.microsoft.com/default.aspx?scid=kb;EN-US;838765, but it doesn't seem to help.
2004-11-17 00:17:23.09 spid51 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:23.09 spid51 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:23.09 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:23.09 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:23.68 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:23.68 spid51 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:23.68 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:23.68 spid51 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:24.03 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:24.03 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:28.54 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:28.54 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:28.54 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:28.54 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:28.54 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:28.54 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:56.45 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:56.45 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:56.45 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:56.45 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:17:56.45 spid52 Error: 615, Severity: 21, State: 1
2004-11-17 00:17:56.45 spid52 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:18:00.18 spid54 Error: 615, Severity: 21, State: 1
2004-11-17 00:18:00.18 spid54 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:18:01.15 spid54 Error: 615, Severity: 21, State: 1
2004-11-17 00:18:01.15 spid54 Could not find database table ID 2, name 'tempdb'..
2004-11-17 00:18:23.34 spid5 Starting up database 'tempdb'.
2004-11-17 00:18:23.43 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2004-11-17 00:18:23.46 spid2 Recovery complete.
November 18, 2004 at 4:33 am
with windows 2003 I don't use the /3gb switch - 2003 handles memory without intervention - you might want to remove that statement - enable awe in sql server and set your min and max memory settings
exec dbo.sp_configure 'min server memory',14000 ( for example )
exec dbo.sp_configure 'max server memory',14000
exec dbo.sp_configure 'awe enabled',1
reconfigure with override
go
sp_helpfile ( run in tempdb ) will tell you where tempdb resides ( default the install data folder ) you might want to move tempdb to your normal data and log drives.
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'M:\databases\tempdb\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'L:\logs\templog.ldf')
go
( adjust paths to suit )
restart the sql service and tempdb will have moved, no other intervention required !!!
The only other thought I have is that you may have a permission resolution issue with the service account.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 18, 2004 at 6:13 am
I suggest shut down SQQL Server service and make sure that tempdb is deleted. I previously had a situation where tempdb was locked at the file level and wouldn't delete and thru a similar error. Setting SQL service to manual rebooting and manually deleting then starting service corrected the problem. Just make sure to reset SQL Server service back to automatic if you do this. Could be a corrupted version of tempdb is not deleteing and being read back into SQL at startup.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply