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 |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2007-04-13 : 09:16:31
|
| Hi all,we've been having this ancient database with old accounting data running in suspect mode since as long as I can remember (I started working here a year ago), and finally I had some time on my hands so I thought I'd try to get it online again. However I'm running in to problems:DBCC CHECKDB (myDBName) gives this error:Msg 926, Level 14, State 1, Line 1Database 'myDBName' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.Running sp_helpdb only does not display the suspect database and sp_helpdb 'myDBName' gives this error even though I'm a system administrator:No permission to access database 'myDBName'.It's possible that I might be able to dig up a backup but that would be quite tedious. Is it possible to bring the database to a state where I'm able to do a CHECKDB at least...?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-13 : 13:24:31
|
| Suspect databases are typically a disk issue, such as a file missing, drive not available, mount point not active, etc... Have you verified that everything is in working order on the disks where this database lives?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-04-13 : 14:55:19
|
| You're running SQL Server 2000, right?Is the information in this database actually worth your (and our) time recovering? If its been running in suspect mode for a year and its too much trouble for you to look for a backup, doesn't seem like its worth it to me.Paul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-04-16 : 03:08:46
|
You're right, it's on sql server 2000 and the disks are working fine, we have about 25 other databases running on the same instance and there are no problems with them whatsoever. I haven't done a surface scan or anything like that on the disks yet, maybe I should? It probably wount help me get the database back online but it would be nice to know...But at least it's valuable to know that a backup is the best (only?)option. I've never had to recover a suspect database before and I was hoping that someone could pull a nice "magic trick" from their sleeve and fix it  We are required by government policy to keep this data alive for 10 years and even though it's likely never to be used it would be good to have it online. Thanx for your help!--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-04-16 : 08:43:38
|
After talking to alot of people it seems that the data in this database is actually 12 years old, hence we are no longer required to keep the records. And the financial director has agreed to delete it so that's exactly what I'm gonna do! Ahhh...nothing like problems that just disappear by themselves  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-04-16 : 11:54:40
|
| There are plenty of magic tricks... I just didn't want to start going into them unless it was really worth it :-)Paul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
|
|
|
|
|
Suspect database - DBCC CHECKDB throws error