April 13, 2015 at 5:07 am
My database went into suspected mode. and after we had run some script, it came out from the suspected mode. but we encountered this error while opening table in database.
2009-11-02 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2.
2009-11-02 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:\MSSQL.SQL2008\MSSQL\DATA\my_db.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.
What is the reason?
How can I rectify this Problem?
April 13, 2015 at 5:13 am
Restore from a clean backup, one you took 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
April 13, 2015 at 5:58 am
the main problem is we don't have clean backup.
we have this problematic DB Only..
April 13, 2015 at 6:04 am
No backups? Really?
Run the following, post the full and complete output.
DBCC CheckDB('<database name>') WITH No_INFOMSGS, ALL_ERRORMSGS
You will lose data in the process of fixing this, so let the business owner know that there's going to be missing, inconsistent data or similar problems afterwards.
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
April 13, 2015 at 7:06 am
Looks like the exact same question over at ASKSSC. I gave very similar advice there.
If ever you wondered why so much emphasis is placed on backups, testing backups, ensuring backups are consistent, knowing how to use your backups to restore, how to restore to a point in time, all the stuff around backups, well, now you know.
With no backups, you're facing data loss, possibly. You might get lucky. It might be a nonclustered index that you can simply drop and recreate. Do what I said. Do what Gail said. Run CHECKDB to understand where the problem lies. Once you know, alert the business to the possibility of data loss.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2015 at 5:10 am
Hi
Can I use this command to check my databases every night. (in a job)
(Before I get caught)
And I should read the report every morning ?
April 14, 2015 at 5:24 am
MotivateMan1394 (4/14/2015)
Can I use this command to check my databases every night. (in a job)
Absolutely, yes. Every night if you can, less often if you don't have the time every night
And I should read the report every morning ?
Depends on whether you want to know about corruption from the job that checks for corruption or whether you prefer to have the users tell you about strange errors they're getting.
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
April 14, 2015 at 5:53 am
MotivateMan1394 (4/14/2015)
HiCan I use this command to check my databases every night. (in a job)
(Before I get caught)
And I should read the report every morning ?
Piling on with Gail.
Yes. Consistency checks should be part of every full and differential backup. Otherwise, you don't know if you're backing up a well structured database or something with a problem.
I would only read the report if it found errors.
For more on ensuring your backups are good, read this article[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2015 at 11:19 pm
Thank you
But as far as I Concern ,in Job History I found Job success or job failour.
"The dbcc run successfull or not "
And where Do I read the reaport ?
April 15, 2015 at 2:32 am
MotivateMan1394 (4/14/2015)
"The dbcc run successfull or not "And where Do I read the reaport ?
In the job history. When it's successful that's all the report there is.
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
April 15, 2015 at 4:22 am
If you schedule this to run through SQL Agent, the most common method. You can do two things, make sure that on an error it sends an email message is the first. Second, have it output the results to a file. Both options are pretty easily set up through Agent.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply