July 20, 2011 at 12:55 pm
Who said anything about sp_resetstatus?
Is that the DB's name? Is that the name that appears in the list? Are you running queries on the same server as that list of databases is from?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2011 at 12:55 pm
also tried
DBCC CHECKDB(CheckListDB_Data, REPAIR_ALLOW_DATA_LOSS)
failed with
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'CheckListDB_Data'. No entry found with that name. Make sure that the name is entered correctly.
July 20, 2011 at 12:57 pm
GilaMonster (7/20/2011)
Who said anything about sp_resetstatus?Is that the DB's name? Is that the name that appears in the list? Are you running queries on the same server as that list of databases is from?
DB name is CheckListDB_Data
Some post suggests do a sp_resetstatus on the DB
July 20, 2011 at 12:57 pm
halifaxdal (7/20/2011)
also triedDBCC CHECKDB(CheckListDB_Data, REPAIR_ALLOW_DATA_LOSS)
failed with
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'CheckListDB_Data'. No entry found with that name. Make sure that the name is entered correctly.
Seriously, Gail is the best person I know to help you out with this, it might be easier to just do what she asks instead of guessing at this!
July 20, 2011 at 1:00 pm
This is from error log of the SQL 2000 server:
2011-07-20 12:02:46.81 spid14 Starting up database 'CheckListDB'.
2011-07-20 12:02:46.84 spid5 Starting up database 'tempdb'.
2011-07-20 12:02:46.98 spid14 Error: 9004, Severity: 21, State: 10
2011-07-20 12:02:46.98 spid14 An error occurred while processing the log for database 'CheckListDB'..
July 20, 2011 at 1:02 pm
Ninja's_RGR'us (7/20/2011)
halifaxdal (7/20/2011)
also triedDBCC CHECKDB(CheckListDB_Data, REPAIR_ALLOW_DATA_LOSS)
failed with
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'CheckListDB_Data'. No entry found with that name. Make sure that the name is entered correctly.
Seriously, Gail is the best person I know to help you out with this, it might be easier to just do what she asks instead of guessing at this!
I am doing every exact steps she suggested
unluckily I am stuck here, or I should say "we are stuck here"?
July 20, 2011 at 1:03 pm
halifaxdal (7/20/2011)
This is from error log of the SQL 2000 server:2011-07-20 12:02:46.81 spid14 Starting up database 'CheckListDB'.
2011-07-20 12:02:46.84 spid5 Starting up database 'tempdb'.
2011-07-20 12:02:46.98 spid14 Error: 9004, Severity: 21, State: 10
2011-07-20 12:02:46.98 spid14 An error occurred while processing the log for database 'CheckListDB'..
There's no underscore in that name >>> 'CheckListDB'.
Try with that dbname when doing the update in the system tables.
July 20, 2011 at 1:04 pm
halifaxdal (7/20/2011)
Ninja's_RGR'us (7/20/2011)
halifaxdal (7/20/2011)
also triedDBCC CHECKDB(CheckListDB_Data, REPAIR_ALLOW_DATA_LOSS)
failed with
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'CheckListDB_Data'. No entry found with that name. Make sure that the name is entered correctly.
Seriously, Gail is the best person I know to help you out with this, it might be easier to just do what she asks instead of guessing at this!
I am doing every exact steps she suggested
unluckily I am stuck here, or I should say "we are stuck here"?
IIRC Gail never suggested checkdb with repair. There's a very specifc series of steps to follow to have any kind of hope in your situation. She doesn't see hat you're doing and any extra steps you had might be the one that throws her off.
July 20, 2011 at 1:29 pm
Thanks Ninja.
I didn't know that Gail was unhappy with this. Gail: if you are still reading this post, I want to say sorry to you, being tired I don't know what I am doing here.
OK, I re-did everything from scratch: here is the steps and finding:
Create a new DB called CheckListDB
Stop server
Swap the files
Start server
CheckListDB exists in the list but appears Suspect
Tried this:
ALTER DATABASE CheckListDB SET EMERGENCY
Failed with this:
Server: Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'EMERGENCY'.
Do this (followed Gail's steps):
sp_configure 'Allow updates',1
go
reconfigure with override
go
update sysdatabases set status= 32767 where name = 'CheckListDB'
(1 row(s) affected)
So it seems the DB is still revivable? But I don't know what table in that DB.
Thank you.
July 20, 2011 at 1:36 pm
I never meant to imply that she's unhappy. I'm sure she'll be back soon but then again it's close to midnight now where she lives.
In SQL 2000 the "ALTER DATABASE CheckListDB SET EMERGENCY" command is invalid (was introduced in 2k5).
That's why she had to go with the system table update instead.
Now you just need to try to select data from any tables. You can find out the table names with : Select * FROM dbo.sysobjects WHERE XType = 'U'
Then try doing a simple select statement.
If that works you can try doing a DBCC CHECKDB() WITH NO_INFOMSGS, ALL_ERRORMSGS and post the list of error messages here (assuming you have corruption).
July 20, 2011 at 1:46 pm
Ninja's_RGR'us (7/20/2011)
I never meant to imply that she's unhappy. I'm sure she'll be back soon but then again it's close to midnight now where she lives.Now you just need to try to select data from any tables. You can find out the table names with : Select * FROM dbo.sysobjects WHERE XType = 'U'
Midnight?! Where is she from? Europe?
I tried the select, however, it returns only 10 tables, 9 start with spt_ and one is MSreplication_options
I also made a query directly to a table that I happen to know its name:
use CheckListDB
GO
SELECT * FROM DBCheckListDataTable
it failed even on the use:
Server: Msg 945, Level 14, State 2, Line 1
Database 'CheckListDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I didn't see anything in errorlog for around this timestamp
July 20, 2011 at 1:48 pm
halifaxdal (7/20/2011)
Ninja's_RGR'us (7/20/2011)
I never meant to imply that she's unhappy. I'm sure she'll be back soon but then again it's close to midnight now where she lives.Now you just need to try to select data from any tables. You can find out the table names with : Select * FROM dbo.sysobjects WHERE XType = 'U'
Midnight?! Where is she from? Europe?
I tried the select, however, it returns only 10 tables, 9 start with spt_ and one is MSreplication_options
I also made a query directly to a table that I happen to know its name:
use CheckListDB
GO
SELECT * FROM DBCheckListDataTable
it failed even on the use:
Server: Msg 945, Level 14, State 2, Line 1
Database 'CheckListDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I didn't see anything in errorlog for around this timestamp
South Africa.
Hopefully this is something that can be fixed. Please run the checkdb command (DO NOT allow data loss) and post the results here.
July 20, 2011 at 1:53 pm
Ninja's_RGR'us (7/20/2011)
halifaxdal (7/20/2011)
Ninja's_RGR'us (7/20/2011)
Please run the checkdb command (DO NOT allow data loss) and post the results here.
How to run (DO NOT allow data loss)?
July 20, 2011 at 1:55 pm
DBCC CHECKDB() WITH NO_INFOMSGS, ALL_ERRORMSGS
July 20, 2011 at 1:57 pm
success!
What's the next step? Thanks.
Viewing 15 posts - 46 through 60 (of 130 total)
You must be logged in to reply to this topic. Login to reply