July 15, 2014 at 9:05 am
I/O related. If you've had two, then this means there is almost certainly s significant issue with some area of the I/O stack. Have you had any 823, 824, 825 alarm messages (assuming you have them set up) or in the logs (SQL Server or box), they may give more information.
Restore from backup, and not onto the same discs until you've managed to identify the problem and fix, as you may well get the same again.
Edit: Unless one of the Rockstars suggests something better
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 15, 2014 at 9:12 am
andrew gothard (7/15/2014)
I/O related. If you've had two, then this means there is almost certainly s significant issue with some area of the I/O stack. Have you had any 823, 824, 825 alarm messages (assuming you have them set up) or in the logs (SQL Server or box), they may give more information.Restore from backup, and not onto the same discs until you've managed to identify the problem and fix, as you may well get the same again.
Edit: Unless one of the Rockstars suggests something better
Yes thanks, I see a bunch of these 824 errors in the Event Viewer from almost 2 weeks ago:
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:206645; actual 0:0). It occurred during a read of page (1:206645) in database ID 8 at offset 0x00000064e6a000 in file 'd:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DWRepository.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.
I'll have to run a DBCC against all the other dbs on that drive (all of them) now..
July 15, 2014 at 9:25 am
do you have a team responsibe for your storage, or is it you?
If you have, give them the date and time of the first messages and ask htem to check their logs to see what happened. Unfortunately there's a very good chance the system logs on the box will have rolled over long ago and overwritten that information, but there may be messages in there pointing to the problem.
I would also highly recommend implementing automatic alerting on those IO errors and also on SQL Server errors 17-25. Plus a regular DBCC CHECKDB job. Nightly if at all possible.
What's your Page Verify set to?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 15, 2014 at 9:30 am
andrew gothard (7/15/2014)
do you have a team responsibe for your storage, or is it you?If you have, give them the date and time of the first messages and ask htem to check their logs to see what happened. Unfortunately there's a very good chance the system logs on the box will have rolled over long ago and overwritten that information, but there may be messages in there pointing to the problem.
I would also highly recommend implementing automatic alerting on those IO errors and also on SQL Server errors 17-25. Plus a regular DBCC CHECKDB job. Nightly if at all possible.
What's your Page Verify set to?
Its our storage team who handles our storage.
We do have a weekly maintenance job to do CHECKDB. Our page verify for this database is set to CHECKSUM.
Oh by the way (and this is a nasty one), we don't have a backup of this database as it was installed 'secretly' as part of a POC and now they've called in the DBA team to ask for help b/c something in their app seems broken...go figure
July 15, 2014 at 9:43 am
msandico 57892 (7/15/2014)
andrew gothard (7/15/2014)
do you have a team responsibe for your storage, or is it you?If you have, give them the date and time of the first messages and ask htem to check their logs to see what happened. Unfortunately there's a very good chance the system logs on the box will have rolled over long ago and overwritten that information, but there may be messages in there pointing to the problem.
I would also highly recommend implementing automatic alerting on those IO errors and also on SQL Server errors 17-25. Plus a regular DBCC CHECKDB job. Nightly if at all possible.
What's your Page Verify set to?
Its our storage team who handles our storage.
We do have a weekly maintenance job to do CHECKDB. Our page verify for this database is set to CHECKSUM.
Oh by the way (and this is a nasty one), we don't have a backup of this database as it was installed 'secretly' as part of a POC and now they've called in the DBA team to ask for help b/c something in their app seems broken...go figure
I have a feeling I know what's coming here, but I'll ask anyway. Do they have the scripts they used to build it in the first place in Source Control, so they can re-build it?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 15, 2014 at 10:41 am
This is not repairable. You need to restore from backup....
Did the DB correctly switch into Emergency Mode?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2014 at 11:14 am
@gothard - it is a db used by System Centre Service Manager so im sure someone can re-install the app but since it's part of a collection of dbs, I don't think having an empty schema will do me any good..unfortunately.
@gila - yes the db switched fine into emergency mode (it's current state at the moment)
July 15, 2014 at 11:22 am
Recover from the full backups and log backups to the point is the best bet here.
If you don't have the backups then pretty much , you have to pull the maximum data out of corrupted mdf.
In emergency mode, you should be able to select the data from the tables and export it to another another database if lucky(if no system catalog tables are corrupted ) , I also have recovered some stored procedures previously for one of the client as they don't care about the data but some custom store procs.
Also, there are third party tools those can recover the data from the corrupted tables but don't guarantee 100 % recovery, even Microsoft stopped dealing with corrupted databases support as per my knowledge.
July 15, 2014 at 11:38 am
@SQLFRNDZ (7/15/2014)
Recover from the full backups and log backups to the point is the best bet here.If you don't have the backups then pretty much , you have to pull the maximum data out of corrupted mdf.
In emergency mode, you should be able to select the data from the tables and export it to another another database if lucky(if no system catalog tables are corrupted ) , I also have recovered some stored procedures previously for one of the client as they don't care about the data but some custom store procs.
Also, there are third party tools those can recover the data from the corrupted tables but don't guarantee 100 % recovery, even Microsoft stopped dealing with corrupted databases support as per my knowledge.
According to my dbcc checkdb output, it looks like a system table index is corrupted and cannot be repaired. I can't even run a DBCC PAGE on the page in the error as I get this error:
Msg 2514, Level 16, State 5, Line 1
A DBCC PAGE error has occurred: Invalid page type - dump style 3 not possible.
Then I try to use Export/Import wizard to export the data into a new database, but when I click 'Next' after selecting the DWRepositoy database (in emergency mode), it fails, saying "cannot open database requested by login XXX.."
July 15, 2014 at 11:42 am
Yep, it happens, since it got corrupted internally on system indexes , it hard to repair and all user tables do use the system catalog to locate the data. if you don't have backups, then try the third party tools those can repair and pull the max data you can get but don't guarantee 100 % and it is big mess of data and you can not say we recovered 100%.
July 15, 2014 at 11:44 am
Or you may need to find where the original database and data came from and recreate it from scratch.
July 15, 2014 at 11:44 am
Don't use the import/export wizard.
Script the objects, some will probably fail. Query the data bit by bit (filter on the clustered index of each table), some will probably fail. Recreate the DB from what you got out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2014 at 12:11 pm
Thanks everyone for the quick responses, I really appreciate it and almost have resigned myself to say 'too bad' to the app owner..which I really don't want to do..
@gila - when I try to script the objects (or whole database), I keep running into an error such as :
"Database "DWRepository' is already open and can only have one user at a time".
I'm sure there is nobody else connected to this except myself and I thought that in Emergency mode you can have a number of users (with sysadmin privilege) accessing/reading the data.
The other 'bad' thing is even in a query window, if I do 'sp_help' i'll get this error:
Msg 824, Level 24, State 2, Procedure sp_help, Line 16
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:206645; actual 0:0). It occurred during a read of page (1:206645) in database ID 8 at offset 0x00000064e6a000 in file 'd:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DWRepository.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.
Viewing 13 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply