Lost All databases
-
I'm a web developer and have been using SQL Server daily on my developement computer. This morning when I started up the computer and Enterprise Manager, under Local machine | Databases, all I got was (No items). My mdf and ldf files are still on the computer, but when I try to reattach them I get the message "Error 5123 CREATE FILE encountered operating system error 32 (the process cannot access the file because it is being used by another process.) while attempting to open or create the physical file c:\SQLSERVER\Data\MSSQL\nowandthen.mdf"
My error log shows in part:
2005-11-08 11:45:40.87 server Server Process ID is 156.
2005-11-08 11:45:40.87 server Logging SQL Server messages in file 'c:\sqlserver\data\MSSQL\log\ERRORLOG'.
2005-11-08 11:45:40.95 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2005-11-08 11:45:42.01 server SQL Server configured for thread mode processing.
2005-11-08 11:45:42.10 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2005-11-08 11:45:42.17 server Attempting to initialize Distributed Transaction Coordinator.
2005-11-08 11:45:42.31 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2005-11-08 11:45:43.25 spid4 Starting up database 'master'.
2005-11-08 11:45:45.10 server Using 'SSNETLIB.DLL' version '8.0.194'.
2005-11-08 11:45:45.10 spid5 Starting up database 'model'.
2005-11-08 11:45:45.14 spid4 Server name is 'DELL'.
2005-11-08 11:45:45.56 spid8 Starting up database 'msdb'.
2005-11-08 11:45:45.56 spid10 Starting up database 'Northwind'.
2005-11-08 11:45:45.56 spid11 Starting up database 'nowandthen'.
2005-11-08 11:45:45.56 spid9 Starting up database 'pubs'.
2005-11-08 11:45:45.56 spid12 Starting up database 'pictureperfectlive.com'.
2005-11-08 11:45:45.59 server SQL server listening on Shared Memory, Named Pipes.
2005-11-08 11:45:45.59 spid5 Clearing tempdb database.
2005-11-08 11:45:45.60 server SQL Server is ready for client connections
2005-11-08 11:45:48.39 spid5 Starting up database 'tempdb'.
2005-11-08 11:45:48.76 spid4 Recovery complete.
2005-11-08 11:46:16.06 spid51 Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'sp_MSgetversion'.
2005-11-08 11:46:23.84 spid51 Error: 602, Severity: 21, State: 13
2005-11-08 11:46:23.84 spid51 Could not find row in sysindexes for database ID 1, object ID 33, index ID 2. Run DBCC CHECKTABLE on sysindexes..
I'm not an expert on SQL Server and need to get the nowandthen.mdf table restored quickly as it's for a web site I'm developing. Any help would be greatly appreciated
-
did you run dbcc checkdb on master?
A.J.
DBA with an attitude -
I've been trying to do that, but I am having problems with the syntax since I've never run that command before. I don't want to have someone do this for me, but can you give me the proper syntax for that?
-
DBCC CHECKDB
A.J.
DBA with an attitude -
The results on this looked fine:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'nowandthen'.
But, I still can't see either the databases (northwind or any others), just SQL SERVER GROUP - (Local) (Win NT) - Databases - No items
-
The error log was complaining about DBID 1, this is the master database. Run DBCC CHECKDB against that database.
A.J.
DBA with an attitude -
Is there a way I can specify the database when I run CHECKTABLE in the query? When I open up thr query analyzer, I get an error message "ODBC SQL Server Driver[SQL SERVER]Could not find row in sysindexes for database ID 1, object ID 33, index ID 2. Run DBCC CHECKTABLE on sysindexes"
There's a dropdown to select the database in query analyzer and it's showing nowandthen, but when I pull it down to change to the master table, it goes blank.
-
I assume you've tried:
Use master
DBCC CHECKDB
GO
Sounds like your master database is fried somehow. You might want to think about rebuilding your master database. Do a search in BOL for Rebuild Master. It will explain how to use rebuildm.exe
A.J.
DBA with an attitude -
Yup, I can smell the smoke now...
Server: Msg 8966, Level 16, State 1, Line 3
Could not read and latch page (1:278) with latch type SH. sysindexes failed.
I'll take a look at Rebuild Master like you suggested. Strange, it was working fine all this week until this morning. I guess I've been lucky so far. I've been developing sites with SQL Server databases for about 7 or so years, but never had to get into the guts of the server for anything heavy. Many thanks!
-
Sometimes these things just happen, sometimes they are signs of bigger problems to come (failing hard drives, etc).
In my 8 years of dealing with SQL Server, I only ever had to use rebuildm.exe once. It's not a real common occurence.
A.J.
DBA with an attitude -
I finally ended up deleting the whole server, reinstalling it, then reattached the database. Works fine now! Many thanks for your help.
-
Just an afterthought ... since this is a development database environment and it seems losing things (or things getting corrupted) will cause you grief ... it might be a good time to get 'familiar' with (start using) the Maintenance Plan Wizard ....
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Oh no! You mean I gotta learn how to use this server also? ....lol! I've been using it for about 7 years and this was the first time it ever lost the databases. Your suggestion is a great one and I will certainly follow up on it, but right now I have 4 database-backed sites under development. Once I get thes cleared off my desk I will certainly follow up on your suggestion. I do thank you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply