Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Application Database flagged as 'SUSPECT' with no back-up to restore from

Avatar of cjake2299
cjake2299Flag for United States of America asked on
Microsoft SQL Server
8 Comments1 Solution2507 ViewsLast Modified:
I have a SunFire X2100 running Windows 2003 Server (w/o SP1) with SQL Server 200 w/SP3a.

I had an issue with starting the server, but that has since been resolved.  My orginal issue can be found here;
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22084881.html


My current issue is that this is a production seerver, hosting our accounting application, and several of the tables required by that application have been marked as 'SUSPECT'.

I have tried the following;

EXEC sp_resetstatus 'applicationdb'

When performing this action I recieved the following message;

Prior to updating sysdatabases entry for database 'mas500_sys', mode = 0 and status = 1073742096 (status suspect_bit = 256).
For row in sysdatabases for database 'mas500_sys', the status bit 256 was forced off and mode was forced to 0.
Warning: You must recover this database prior to access.

Ok, so recover the database...last good back-up is 5 and 1/2 months old.  This is a last resort.

Second attempt was to shutdown server and remove the log file, hoping that SQL would recreate the log file (assuming that the log file was corrupted and not the database itself), no luck.  When server was restarted SQL did not recreate the log file, and status was reset to 'SUSPECT'.

I attempted to detach the database, move the app_data.mdf and app_log.ldf, create a new database with the same name, detah it, replace the app_data.mdf that was just recently created with the original app_data.mdf.  Same issue, status set to 'SUSPECT'.

I am begining to think that I am SOL, and should be typing my resume as opposed to typing on the forums, but I have faith that somebody has an absolutely brilliant idea (or absurd, don't really care at this point) that will return my databases to their previous running states, or at least restore them with a LARGE portion of the data still intact.

I am a newb as far as SQL is concerned, and we paid a consultant to come in and setup this application and the database for us.  She automated the backup to occur daily, but it stopped after July 2 for one reason or another.

here is an excerpt from the SQL Log, concerning the tables in question;

2006-12-07 12:15:12.23 spid11    Error: 9003, Severity: 20, State: 1
2006-12-07 12:15:12.23 spid11    The LSN (15:4150:1) passed to log scan in database 'mas500_iapp' is invalid..
2006-12-07 12:15:12.29 spid5     Clearing tempdb database.
2006-12-07 12:15:12.31 spid11    Error: 3414, Severity: 21, State: 1
2006-12-07 12:15:12.31 spid11    Database 'mas500_iapp' (database ID 7) could not recover. Contact Technical Support..

in the meantime, I'll update my resume.
ASKER CERTIFIED SOLUTION
Avatar of Sirees
Sirees

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answers