| Author |
Topic |
|
rnoll01
Starting Member
4 Posts |
 Posted - 2010-09-27 : 11:17:16
|
| Msg 926, Level 14, State 1, Line 1Database 'OnBaseHR' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-27 : 11:48:04
|
| And did you check the SQL Server error log for more information? Do you have a backup of this database?--Gail ShawSQL Server MVP |
 |
|
|
rnoll01
Starting Member
4 Posts |
Posted - 2010-09-27 : 11:56:40
|
| 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 |
|
|
|
rnoll01
Starting Member
4 Posts |
Posted - 2010-09-27 : 12:12:42
|
| DESCRIPTION: Error: 3456, Severity: 21, State: 1Could 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 |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2010-09-29 : 10:48:57
|
| 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. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 12:26:32
|
| 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 ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 12:28:33
|
| 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 ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-29 : 14:42:00
|
| 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 ...) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 15:16:43
|
| 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 ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-30 : 04:05:00
|
| Thanks Gail, so almost certainly an I/O fault to be found & fixed to stop it occurring again. |
|
|
|
rnoll01
Starting Member
4 Posts |
Posted - 2010-10-05 : 09:37:56
|
| yep must be i/o.. i restored from a backup ran for a few days no problem, now having problems again. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-05 : 12:47:45
|
| Do some investigation of that IO subsystem. Preferably after getting the database onto different drives.--Gail ShawSQL Server MVP |
|
|
|
vaddesuresh
Starting Member
5 Posts |
Posted - 2010-11-15 : 01:33:42
|
| use DAC user account and try to rebuild the database which is in suspect mode |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-15 : 04:53:24
|
| There's no rebuild database feature in SQL and repair (which you may have meant) does not require use of the DAC.--Gail ShawSQL Server MVP |
|
|
|
|
suspect recovery