HELP! MSSQL 7.0 database market suspect

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>'

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?

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?

I've posted the sqlerror to:

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

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

thanks

Adjusted points to 400

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

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.

thanks anyway, but the problem was already fixed (see comment preceeding your proposed answer)

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?

thanks -- but app already trashed and repopulated.  I'll know for future!