SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 suspect recovery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rnoll01
Starting Member

USA
4 Posts

Posted - 09/27/2010 :  11:17:16  Show Profile  Reply with Quote
Msg 926, Level 14, State 1, Line 1
Database 'OnBaseHR' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/27/2010 :  11:48:04  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
And did you check the SQL Server error log for more information?

Do you have a backup of this database?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

rnoll01
Starting Member

USA
4 Posts

Posted - 09/27/2010 :  11:56:40  Show Profile  Reply with Quote
i have a backup but this has been going on for a couple weeks.
i would do a checkdb would have errors run checkdb again 0 errors
Go to Top of Page

rnoll01
Starting Member

USA
4 Posts

Posted - 09/27/2010 :  12:12:42  Show Profile  Reply with Quote
DESCRIPTION: Error: 3456, Severity: 21, State: 1
Could not redo log record (99110:144:5), for transaction ID (0:13516122), on page (3:16039), database 'OnBaseHR' (8). Page: LSN = (99110:113:6), type = 2. Log: OpCode = 3, context 19, PrevPageLSN: (99107:49:6).
getting this now
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 09/29/2010 :  10:48:57  Show Profile  Reply with Quote
Look up sp_attach_single_file_db in BOL. It appears you may lose some data being applied from the log.

Try something like:

EXEC sp_detach_db @dbname = 'OnBaseHR'
EXEC sp_attach_single_file_db @dbname = 'OnBaseHR',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\OnBaseHR.mdf' --whatever the path to your data file is.

After the detach, I personally would copy the mdf and ldf files for that database to another server/directory for safe keeping, then do the attach. Sometimes I get a little too anal but better safe then sorry if something really goes wrong - IMO.


Terry

-- You can't be late until you show up.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/29/2010 :  12:26:32  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
No, please don't.

If that database is detached it will not reattach. It's a redo log record, that means it was encountered during restart-recovery. If the database is detached then , when SQL goes to reattach it it will look for the log, to do restart-recovery, and since it's not there it will refuse to attach the database at all.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/29/2010 :  12:28:33  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
My gut feel is that the best option here is restore from backup.

If that's not an option, try setting the database into Emergency mode and running an emergency mode repair. That should rebuild the log and fix up any damage in the data file caused by the damaged log.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/29/2010 :  14:42:00  Show Profile  Reply with Quote
Need to Find & Fix the underlying cause too?

Gail: is this likely to be some sort of hardware failure / fault? or could it be some other cause that won't reoccur? (Can't think what ... user attached MDF from one file with an LDF from another, or attached a file from a DB that had not been properly shut down, or restored from a tape backup that was a disk-dump whilst SQL Server was still running ...)
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/29/2010 :  15:16:43  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Misbehaving IO subsystem, like just about any other form of corruption. This could be a corrupt page in the DB or a corrupt log record. Can't tell which with the limited info present.

Mix & match ldf and mdf and you'll get an error saying that the files belong to different database.
Attach a file from a DB not properly shut down will either work fine (if log present) or fail the attach with a missing file error

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/30/2010 :  04:05:00  Show Profile  Reply with Quote
Thanks Gail, so almost certainly an I/O fault to be found & fixed to stop it occurring again.
Go to Top of Page

rnoll01
Starting Member

USA
4 Posts

Posted - 10/05/2010 :  09:37:56  Show Profile  Reply with Quote
yep must be i/o.. i restored from a backup ran for a few days no problem, now having problems again.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/05/2010 :  12:47:45  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Do some investigation of that IO subsystem. Preferably after getting the database onto different drives.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

vaddesuresh
Starting Member

India
5 Posts

Posted - 11/15/2010 :  01:33:42  Show Profile  Reply with Quote
use DAC user account and try to rebuild the database which is in suspect mode
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 11/15/2010 :  04:53:24  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
There's no rebuild database feature in SQL and repair (which you may have meant) does not require use of the DAC.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000