?
Solved

Production database in SUSPECT mode

Posted on 2005-04-27
14
Medium Priority
?
1,173 Views
Last Modified: 2012-05-05
Hi,

This is very urgent.

One of my prodn database has gone in suspect mode.
The log file had grown up to 2 gigs.
I attached one more log file and the database was up again.
I restarted the SQL Server and the database is in SUSPECT mode again.
I dont want to restore it from backups.

Wat shud i do...
0
Comment
Question by:pai_prasad
  • 4
  • 4
  • 3
11 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13882145
Make sure that all data files related to the database still exist.  Also, look into your Event Log to see any messages that SQL Server has written there why this has happened.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13882561
"I attached one more log file and the database was up again."

What do you mean by this statement?

For your logfile to be that big, you definately have problems.  I would say you haven't been backing up and shrinking your logfiles properly.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13882591
Also, don't try attaching/detaching anything, you might not be able to bring the database back online.

Pretty good procedures here to get out of Suspect mode:

http://www.windowsitpro.com/Article/ArticleID/14047/14047.html
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:pai_prasad
ID: 13888685
When i attached one more log file i think the database was in ReadOnly/emergency mode
for we were able to query the tables..


here is the exact err msg

2005-04-27 20:33:00.76 spid3     I/O error (torn page) detected during read at offset 0x00000000f3e000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\equip.mdf'..                                                                                     0
2005-04-27 20:33:00.78 spid3     Error: 3313, Severity: 21, State: 2                                                                                                                                                                                            0
2005-04-27 20:33:00.78 spid3     Error while redoing logged operation in database 'equip'. Error at log record ID (5289:21640:3)..                                                                                                                                0
2005-04-27 20:33:00.78 spid3     Error: 3414, Severity: 21, State: 1                                                                                                                                                                                            0
0
 
LVL 34

Expert Comment

by:arbert
ID: 13890415
"When i attached one more log file i think the database was in ReadOnly/emergency mode
for we were able to query the tables.."


What do you mean by that statement?  How are you attaching more than one log file?  Do you mean multiple LDF files?
0
 
LVL 10

Author Comment

by:pai_prasad
ID: 13890849
yes multiple log files...
0
 
LVL 10

Author Comment

by:pai_prasad
ID: 13912988
i am still waiting for an answer........
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13919075
Given the error you received in your Event Log, the only recourse for you is to restore from back-up.  If you still don't want to restore from back-up, you can call-up Microsoft and ask them how to fix a primary data file with an I/O error.
0
 
LVL 10

Author Comment

by:pai_prasad
ID: 13921322
is there any way  2 know what caused that error..
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13921482
Look into your Event Log and see for any messages written by SQL Server.  And based on your previous posts, this is the reason why your database became suspect:

2005-04-27 20:33:00.76 spid3     I/O error (torn page) detected during read at offset 0x00000000f3e000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\equip.mdf'..

Check your hard drive using scan disk to check for anything related to this error.
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 13922736
"Given the error you received in your Event Log, the only recourse for you is to restore from back-up.  If you still don't want to restore from back-up, you can call-up Microsoft and ask them how to fix a primary data file with an I/O error."


You can also attempt to use DTS to export your data into a new database, you might only end up losing aportion of data...
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question