?
Solved

DataBase in Suspect mode

Posted on 2010-04-06
18
Medium Priority
?
2,412 Views
Last Modified: 2012-05-09
One of my user database is showing suspect mode. To repair i have executed below scripts:
Step 1:
EXEC sp_resetstatus axlive’;

Step 2:
ALTER DATABASE axlive SET EMERGENCY

Step 3:
DBCC checkdb (axlive’)
(in this step i got error, please see Code Snippet).

Step 4:
ALTER DATABASE axlive SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 5:
DBCC CheckDB ('axlive'’, REPAIR_ALLOW_DATA_LOSS)
(in this step i got the error, please code snippet)

Step 6:
ALTER DATABASE axlive SET MULTI_USER

After step 6, my database (axlive) is not showing in server.

Please let me know how to repair my database.

NOTE:
1. i don't have latest backup.
2. I have backup of MDF & LDF file, I have tried to attach but i got error.
dbcc check db error

Msg 7985, Level 16, State 2, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:4206648) with latch type SH. Check statement terminated due to unrepairable error.
DBCC results for 'axlive'.
Msg 5256, Level 16, State 1, Line 1
Table error: alloc unit ID 0, page (1:4206648) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'axlive'.


DBCC CheckDB ('axlive', REPAIR_ALLOW_DATA_LOSS)

Warning: The log for database 'axlive' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. 
Msg 926, Level 14, State 1, Line 1
Database 'axlive' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
DBCC results for 'axlive'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'axlive'.


DBCC DBRECOVER ('axlive')

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:4089856; actual 0:0). It occurred during a read of page (1:4089856) in database ID 7 at offset 0x000007cd000000 in file 'E:\New Folder\axlive.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'axlive' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

Open in new window

0
Comment
Question by:srinivas_ganamur
  • 9
  • 4
  • 3
  • +2
18 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 29930697
try setting that to offline , then right click an detach, and try attaching it
0
 

Author Comment

by:srinivas_ganamur
ID: 29932716
I have tried that option also....i am able to detach but i am not able to attach (it was giving error)
0
 
LVL 15

Expert Comment

by:MohammedU
ID: 29935379
You should not detach the suspected database any until you resolve the issue...

As you mentioned you have the .mdf and .ldf file backup...

On different server create the database with same name, same logical filename and same physical file name as the database in question.
Stop the SQL Service and rename the new .mdf file to _new and copy the old backup of .mdf file and restart the sql server then...take the db into emergency and single user mode and run the dbcc checkdb...
0
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.

 
LVL 15

Expert Comment

by:MohammedU
ID: 29935704
0
 

Author Comment

by:srinivas_ganamur
ID: 29938404
Hi Mohammedu,

I will try your option tomorrow and i will let you know the result.

Can you some idea of how to resolve DBCC Checkdb error (please see attached DBCC page error)
0
 
LVL 15

Expert Comment

by:MohammedU
ID: 29939155
Based on the error i think system table object id = 4 got corrupted and I don't think it is repairable corruption becasue you have to deallocate the corrupt page mean you loose most of your meta data and it is not the realistic solution.
0
 

Author Comment

by:srinivas_ganamur
ID: 29999390
Hi Mohammedu,

the output of check db:

use axlive
go
DBCC CheckDB
GO

Output:

Msg 7985, Level 16, State 2, Line 2
System table pre-checks: Object ID 4. Could not read and latch page (1:4206648) with latch type SH. Check statement terminated due to unrepairable error.
DBCC results for 'AXLIVE'.
Msg 5256, Level 16, State 1, Line 2
Table error: alloc unit ID 0, page (1:4206648) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'AXLIVE'.
0
 

Author Comment

by:srinivas_ganamur
ID: 30000041
please find the error log files
ERRORLOG
SQLDump0005.txt
SQLDUMPER-ERRORLOG.log
0
 

Author Comment

by:srinivas_ganamur
ID: 30000602
the output of below query:

DBCC CHECKDB (axlive, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AXLIVE_log.LDF' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
Failed to restart the current database. The current database is switched to master.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\SQLDATA\AXLIVE_log.LDF'.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1.  Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
0
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 1500 total points
ID: 30006288
I think the problem could be in this line:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\SQLDATA\AXLIVE_log.LDF'.

You checked who have permissions to create file in that folder? At least SQL Server service user should have.
0
 

Author Comment

by:srinivas_ganamur
ID: 30007082
please find the attached image.
defa/administrator---full control (defa --server name)
sqldata-users.bmp
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 30007618
I'm sorry but I can't see there that's having full control on the folder.
And who's the SQL Server service user?
0
 

Author Comment

by:srinivas_ganamur
ID: 30007761
how to check SSS user?
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 30007840
In Services check the user for SQL Server service.
0
 

Author Comment

by:srinivas_ganamur
ID: 30008112
it wa showing local system account (chk the attached image)
untitled.bmp
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 30008383
That's not recomended. You should change it to a specific use (better is to create one in Active Directory). But for now you can change it to an user that is part of DEFA\Administrators and restart the service and try again to recover the DB.

Good luck
0
 
LVL 1

Expert Comment

by:espanolanthony
ID: 30030354
try this

USE master;
GO

EXEC sp_resetstatus 'abcd';
GO

USE abcd
DBCC CHECKDB WITH NO_INFOMSGS;
GO


USE master;
GO
ALTER DATABASE abcd SET EMERGENCY
GO
ALTER DATABASE abcd SET SINGLE_USER
GO
DBCC CHECKDB (abcd, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

 
USE abcd;
DBCC CHECKDB WITH NO_INFOMSGS;
GO


DBCC UPDATEUSAGE (abcd)
ALTER DATABASE abcd SET multi_user
0
 

Author Closing Comment

by:srinivas_ganamur
ID: 32867360
--
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

601 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