DataBase in Suspect mode

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

srinivas_ganamurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
try setting that to offline , then right click an detach, and try attaching it
0
srinivas_ganamurAuthor Commented:
I have tried that option also....i am able to detach but i am not able to attach (it was giving error)
0
MohammedUCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

srinivas_ganamurAuthor Commented:
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
MohammedUCommented:
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
srinivas_ganamurAuthor Commented:
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
srinivas_ganamurAuthor Commented:
please find the error log files
ERRORLOG
SQLDump0005.txt
SQLDUMPER-ERRORLOG.log
0
srinivas_ganamurAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srinivas_ganamurAuthor Commented:
please find the attached image.
defa/administrator---full control (defa --server name)
sqldata-users.bmp
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
srinivas_ganamurAuthor Commented:
how to check SSS user?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
In Services check the user for SQL Server service.
0
srinivas_ganamurAuthor Commented:
it wa showing local system account (chk the attached image)
untitled.bmp
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
espanolanthonyCommented:
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
srinivas_ganamurAuthor Commented:
--
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.