[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

HELP! MSSQL 7.0 database market suspect

Anyone know a) how to recover this quickly, and b) how I can find the events that caused my database to be marked suspect?  (MSSQL 7.0)

Thanks
0
dooner1
Asked:
dooner1
  • 7
  • 2
  • 2
1 Solution
 
jboydCommented:
From the FAQ at www.mssqlserver.com:

My SQL Server database has been marked "suspect" - what can I do?

In addition to these ideas, also check out www.microsoft.com/support   for the MS Knowledgebase. Specifically Q165918.

Firstly look in <sql>\LOG and look at all recent errorlog(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server. To change the database status, and to get more information on recovery, look up the sp_resetstatus sp in the Books Online.

If you don't have access to sp_resetstatus information, then the short version of this is :-

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = <dbname>

If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into emergency mode. If you do this, extract the data/objects out with bcp/transfer manager and then rebuild the database. Note that the data may be corrupt or transactionally inconsistent.

Issue the following command to put the database into emergency mode (you'll need to allow updates first)

UPDATE master..sysdatabases SET status=32768 WHERE name='<dbname>'
0
 
dooner1Author Commented:
OK, before I go into BCP (my database is ~ 12Gb), the Books Online recommends the following (from "Resetting the Suspect Status":

1. Execute sp_resetstatus.
2. Use ALTER DATABASE to add a data file or log file to the database.
3. Stop and restart SQL Server.

With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

Free disk space and rerun recovery.

-- My question is, how the ALTER DATABASE is performed.  I presume that simply adding a database anywhere will not solve this, but obviosly I cannot add more space to the suspect database.... !

any thoughts?
0
 
jboydCommented:
Have you verified that there was a 9002 or 1105 error in error log? If not can you post the error(s) that you do see in you SQL Error log?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
dooner1Author Commented:
I've posted the sqlerror to:

http://www.dooner.com/sqlerr.txt

I do not see either a 9002 or 1105 error...HELP!

thanks
0
 
dooner1Author Commented:
Adjusted points to 400
0
 
dooner1Author Commented:
Sorry to reject, jboyd, but after further investigation and a hefty call to Microsoft, I found the answer.  

I installed Service Pack 1 and that solved most of my problem.

Thanks again for your help
0
 
amaganganCommented:
The reason that there is no entry in the error log is because your transaction log of your database is damaged or corrupted, so SQL cannot recover the database when the server is started. Applying the service pack is not actually fixing the problem, although you may have been lucky. Basically what you should do is the following - in master:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 0x8000 where name='pubs'
/*replace 'pubs' with your suspect db name*/
go

--you now dump the transaction log to get rid of the corrupt data

still in master:
dump tran pubs with no_log
go


-- then set the status back to normal
update sysdatabases
set status = 0 where name='pubs'
/*replace 'pubs' with your suspect db name*/
go

sp_configure 'allow updates',1
reconfigure with override
go

If it is not your transaction log that is corrupt, then the error will appear in the SQL error log files. It could be a missing file, or corrupted data file.
0
 
dooner1Author Commented:
thanks anyway, but the problem was already fixed (see comment preceeding your proposed answer)
0
 
dooner1Author Commented:
amagangan,

apologies, but after reading your proposed answer, it looks like the best solution. Unfortunately, I've already restarted the procedure by BCP'ing out everything (12Gb data!) and back in.  What do you think would have caused this corrupt log file?
0
 
amaganganCommented:
Transaction log files will get corrupted for a number of reasons. 1) The disk may have a physical error. If your log is on a seperate physical drive to your data, then check the disk.
2) if the server shut down unexpectedly during a long transaction, the log may become corrupt.
3) On very large transaction logs (which it sounds like you have), this can happen from time to time, especially if the hardware is not up to scratch. Ideally your disks should be run on a RAID 5 (hardware) config.
4) Sometimes the log will become corrupt for no apparent reason!

If you are not too far with your BCP, try my fix - it is very quick as oppose to BCPing 12 GB !
0
 
dooner1Author Commented:
thanks -- but app already trashed and repopulated.  I'll know for future!
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 7
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now