mode database> suspect

good morning

I should do when this mode suspect bd
The version is SQL Server 2008R2
bd-modo-SUSPECT.png
enrique_aeoAsked:
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.

Mayank GairolaSr. Application Support Engg.Commented:
Not sure but looks like you are looking for this.

http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html
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
enrique_aeoAuthor Commented:
I do not know whether to activate the operating system license, have enabled some options that have blocked the databases.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anoo S PillaiCommented:
The following is a structured approach to handle a suspect database. Don't rush to run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

Recommended actions for corrupt or suspect databases

Check SQL error log first, Post your finding from SQL error log
0
enrique_aeoAuthor Commented:
this is errorLOG
ERRORLOG
0
enrique_aeoAuthor Commented:
i am trying this

DBCC CHECKDB('ION_Data') WITH NO_INFOMSGS

but i have this error
Msg 926, Level 14, State 3, Line 1
Database 'ION_Data' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
0
enrique_aeoAuthor Commented:
i am trying this

ALTER DATABASE ION_Data SET EMERGENCY

Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database 'ION_Data', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
0
enrique_aeoAuthor Commented:
i try this

ALTER DATABASE ION_Data SET EMERGENCY

Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 9001, Level 21, State 4, Line 1
The log for database 'ION_Data' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
0
enrique_aeoAuthor Commented:
NOW

i have this error
ALTER DATABASE ION_Data SET EMERGENCY

Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

this is the version of sql server
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
More than 60 hours passed. How things looks today?
0
enrique_aeoAuthor Commented:
I this again

ALTER DATABASE ION_SystemLog SET EMERGENCY

Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 9001, Level 21, State 4, Line 1
The log for database 'ION_SystemLog' is not available. Check the event log for related error messages. Resolve any errors and restart the database.


the database is supect mode
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The log for database 'ION_SystemLog' is not available.
You or someone may deleted/moved the transaction log file. Do you know what was the original path and filename?
Or if the file was on another disk check if the disk is visible in the operating system. You may facing a disk issue.
0
enrique_aeoAuthor Commented:
i can see
view attached
files-log.png
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
One thing is what you can see, another thing is what the SQL Server is expecting.
Can you check by the logs if those files are the one SQL Server is expecting?
By the way, why do you have 2 data and 2 transaction log files? And they have different dates.
0
enrique_aeoAuthor Commented:
One thing is what you can see, another thing is what the SQL Server is expecting. I agree.

Can you check by the logs if Those files are the one SQL Server is expecting?
As I do?

By the way, why do you have 2 data and 2 transaction log files? And They Have different dates. These two databases are in supect way, I have another in the same instance as the ONLINE
0
enrique_aeoAuthor Commented:
name      state_desc
master      ONLINE
tempdb      ONLINE
model      ONLINE
msdb      ONLINE
ION_Data      SUSPECT
ION_Network      ONLINE
ION_SystemLog      SUSPECT
ApplicationModules      ONLINE
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, ION_SystemLog is a database name, not the transaction log filename.

Check if this command returns information:
sp_helpdb 'ION_SystemLog' 

Open in new window

0
enrique_aeoAuthor Commented:
it runs successfully but does not return records
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you detach the database?
0
enrique_aeoAuthor Commented:
i can not

TITLE: Microsoft SQL Server Management Studio
------------------------------

Detach database failed for Server 'SISGESTENERGY\ION'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Detach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot detach a suspect or recovery pending database. It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=3707&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try first to run EXEC sp_resetstatus 'ION_SystemLog' ?
If so, the only alternative that I can see now is to stop the SQL Server service, change the data and transaction log filenames, start the SQL Server and try to create the database by attaching the files:
CREATE DATABASE 'ION_SystemLog'
    ON (FILENAME = 'C:\CorrectPath\'ION_SystemLog_Data.mdf'), 
    (FILENAME = 'C:\CorrectPath\'ION_SystemLog_Log.ldf') 
    FOR ATTACH; 

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Or you can provide only the data file and it will create a new transaction log file:
CREATE DATABASE 'ION_SystemLog'
    ON (FILENAME = 'C:\CorrectPath\'ION_SystemLog_Data.mdf')
    FOR ATTACH; 

Open in new window

0
enrique_aeoAuthor Commented:
more detail please, about
change the data and transaction log filenames
files-log-02.png
0
enrique_aeoAuthor Commented:
i try this (other name database

CREATE DATABASE ION_SystemLog02
    ON PRIMARY (FILENAME = 'C:\Program Files (x86)\Schneider Electric\Power Monitoring Expert\Database\SystemLog\ION_SystemLog_Data.mdf')
    FOR ATTACH;

 but i have this error
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files (x86)\Schneider Electric\Power Monitoring Expert\Database\SystemLog\ION_SystemLog_Data.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if the files is not marked as read only.
You should also add the .ldf file.
0
enrique_aeoAuthor Commented:
Finally delete both BD and restore from my last backup. Thank you very much for your support.
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.