February 25, 2014 at 9:13 pm
First, when I run this sql : select * from mydatabase.dbo.mytable, I got this
Msg 824
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 5 at offset 0x000000012d8000 in file 'mydatabase.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.
Second, when I run DBCC CHECKDB ('mydatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS, I got same message
Msg 824
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 13 at offset 0x000000012d8000 in file 'mydatabase.mdf:MSSQL_DBCC13'. 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.
Third, I right click on database, Properties, and
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property Size is not available for Database 'mydatabase'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Can you suggest me to resolve this please…. I am using sql server 2008 r2
Can you please let me know if you need any other details….
Thank you in Advance…..
February 26, 2014 at 12:36 am
Restore from a clean backup (backup taken before the corruption occurred)
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
February 26, 2014 at 2:03 am
GilaMonster (2/26/2014)
Restore from a clean backup (backup taken before the corruption occurred)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
February 26, 2014 at 2:52 am
I hope that database isn't very important... It's very likely there will be some data loss here, it's possible that said data loss could be the entire DB.
Find some downtime, take the database into single user mode (make sure you get the single connection) and run the following (just the following, exactly as it is with no extra options, settings or switches)
DBCC CheckDB(<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK
Let's see if that will give anything useful in terms of the actual errors.
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
February 26, 2014 at 4:12 am
VIII (2/26/2014)
GilaMonster (2/26/2014)
Restore from a clean backup (backup taken before the corruption occurred)I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
One question: why is your customer taking (or not taking) backups? Isn't that your job as a DBA?
February 26, 2014 at 4:21 am
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
Since you don't have backup , try to repair the database,
TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
CAUTION : There is a high chance of data loss
If you mange to get database online, make sure you implement database backup strategy
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
February 26, 2014 at 6:00 am
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
Since you don't have backup , try to repair the database,
TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
NO! NO! NO!
Recommending repairing with no idea of what's actually wrong is insane! We don't even know what the errors are, a sensible recommendation cannot be made on no data. When dealing with corrupt databases the extent of the corruption is essential to know before any useful recommendations can be made. To be honest, since CheckDB's throwing errors I suspect repair will not be a option, it'll likely fail as well.
DO NOT attempt a repair at this point.
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
February 26, 2014 at 6:23 am
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
Since you don't have backup , try to repair the database,
TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
CAUTION : There is a high chance of data loss
If you mange to get database online, make sure you implement database backup strategy
Odd. I'd always heard that what's important is to have a recovery strategy.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 26, 2014 at 7:36 am
dwain.c (2/26/2014)
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
Since you don't have backup , try to repair the database,
TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
CAUTION : There is a high chance of data loss
If you mange to get database online, make sure you implement database backup strategy
Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.
FTFY 😀
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 26, 2014 at 5:22 pm
andrew gothard (2/26/2014)
dwain.c (2/26/2014)
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
Since you don't have backup , try to repair the database,
TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
CAUTION : There is a high chance of data loss
If you mange to get database online, make sure you implement database backup strategy
Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.
FTFY 😀
I stand corrected sir!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 26, 2014 at 6:06 pm
GilaMonster (2/26/2014)
I hope that database isn't very important... It's very likely there will be some data loss here, it's possible that said data loss could be the entire DB.Find some downtime, take the database into single user mode (make sure you get the single connection) and run the following (just the following, exactly as it is with no extra options, settings or switches)
DBCC CheckDB(<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCKLet's see if that will give anything useful in terms of the actual errors.
Nothing useful, same message
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 5 at offset 0x000000012d8000 in file 'mydatabase.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.
February 27, 2014 at 1:00 am
No good news then.
The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable. At this point I'd normally say 'restore from backup', but...
The DB can't be fixed, you're going to have to recreate it.
Script all objects from the damaged database. Don't forget permissions, constraints, indexes, etc. Some objects may well fail to script. Get what you can.
Export all data out. BCP out is probably a good option. Some tables will almost certainly fail. You may have to export tables in chunks, range filters on the clustered index key to avoid reading damaged pages. Get what data you can.
Assuming you can get anything at all out, use the scripts and exported data to create a new DB. This will not be quick, likely days of work.
Once you've got the new DB up, get a backup strategy in place and point out to the customer (politely), that all of this work could have been avoided if they'd had backups.
Additionally... Corruption doesn't just happen for no reason. There's likely something wrong somewhere in the IO subsystem. Do your investigations, check logs, update drivers, update firmware, etc, etc. Otherwise this could all happen again in a few months.
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
February 27, 2014 at 2:11 am
GilaMonster (2/27/2014)
No good news then.The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable. At this point I'd normally say 'restore from backup', but...
The DB can't be fixed, you're going to have to recreate it.
Script all objects from the damaged database. Don't forget permissions, constraints, indexes, etc. Some objects may well fail to script. Get what you can.
Export all data out. BCP out is probably a good option. Some tables will almost certainly fail. You may have to export tables in chunks, range filters on the clustered index key to avoid reading damaged pages. Get what data you can.
Assuming you can get anything at all out, use the scripts and exported data to create a new DB. This will not be quick, likely days of work.
Once you've got the new DB up, get a backup strategy in place and point out to the customer (politely), that all of this work could have been avoided if they'd had backups.
Additionally... Corruption doesn't just happen for no reason. There's likely something wrong somewhere in the IO subsystem. Do your investigations, check logs, update drivers, update firmware, etc, etc. Otherwise this could all happen again in a few months.
thanks, but I think the problem is worse than that
when I try to generate script, nothing to get
when I try to export data, same message 'SQL Server detected a logical consistency-based I/O error' again
when I expand folder table and store procedure, all gone
I must find another way to export data, please let me known if you have any idea
Is this solution worth to try ?
kenneth.mofokeng (2/26/2014)
Since you don't have backup , try to repair the database,TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
CAUTION : There is a high chance of data loss
If you mange to get database online, make sure you implement database backup strategy
February 27, 2014 at 2:37 am
VIII (2/27/2014)
thanks, but I think the problem is worse than thatwhen I try to generate script, nothing to get
when I try to export data, same message 'SQL Server detected a logical consistency-based I/O error' again
when I expand folder table and store procedure, all gone
Then the DB is a complete loss. That several month old backup may be your only option. Good luck.
Is this solution worth to try ?
kenneth.mofokeng (2/26/2014)
Since you don't have backup , try to repair the database
See my reply to kenneth above. Also, as I said to you
The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable.
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
February 27, 2014 at 3:36 am
dwain.c (2/26/2014)
andrew gothard (2/26/2014)
dwain.c (2/26/2014)
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago
Since you don't have backup , try to repair the database,
TRY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER
CAUTION : There is a high chance of data loss
If you mange to get database online, make sure you implement database backup strategy
Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.
FTFY 😀
I stand corrected sir!
I shall now correct myself and add "Documented". That's a biggie too. The best strategy in the world is no use if no-one knows what it is.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply