Tuesday, February 14, 2012

Database Corruption?

Hi,
I have a 36GB database that has failed it's integrity checks as part of a
scheduled maintenance plan over the weekend. The plan history and job step
history did not provide any useful information. I've been trying to run a
DBCC CHECKDB on the database (no options) for the last 2 hours and it is
still running. In the past I believe the DBCC CHECKDB statement required
less than 30 minutes to complete. What are my options at this point outside
of restoring from a backup?
Thanks
Jerry
Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
recommendations?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> I have a 36GB database that has failed it's integrity checks as part of a
> scheduled maintenance plan over the weekend. The plan history and job
> step history did not provide any useful information. I've been trying to
> run a DBCC CHECKDB on the database (no options) for the last 2 hours and
> it is still running. In the past I believe the DBCC CHECKDB statement
> required less than 30 minutes to complete. What are my options at this
> point outside of restoring from a backup?
> Thanks
> Jerry
>
|||Hi
Rather wait the DBCC out and see what it shows. You rather find out now than
in another 24 hours that the DB is corrupt.
In the mean time, fine the last good backup and restore it to another server
to see if it is also corrupt and if you can actually restore it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
> recommendations?
> Thanks
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
>
|||Thanks Mike. I was just starting that process as well as examining the
event logs to see if this could be a HW issue.
Will keep you posted.
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uj25DxVvFHA.3792@.TK2MSFTNGP10.phx.gbl...
> Hi
> Rather wait the DBCC out and see what it shows. You rather find out now
> than in another 24 hours that the DB is corrupt.
> In the mean time, fine the last good backup and restore it to another
> server to see if it is also corrupt and if you can actually restore it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||Ok...event log shows a SQL Server Assertion error and a Error 3624 from a
couple of days ago. Also, an Error 8648 "Index entry for row ID was not
found in index ID 2 of table 1403152044. However, a SELECT OBJECT_NAME with
this number reveals a stored procedure. Any ideas? The DBCC is still
running and the last backup should be on the test box in about 30 minutes.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u1PqWzVvFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks Mike. I was just starting that process as well as examining the
> event logs to see if this could be a HW issue.
> Will keep you posted.
> Jerry
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uj25DxVvFHA.3792@.TK2MSFTNGP10.phx.gbl...
>
|||Problem solved. Corrupt NC index on table with apx 150 million rows.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eaOzlIWvFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Ok...event log shows a SQL Server Assertion error and a Error 3624 from a
> couple of days ago. Also, an Error 8648 "Index entry for row ID was not
> found in index ID 2 of table 1403152044. However, a SELECT OBJECT_NAME
> with this number reveals a stored procedure. Any ideas? The DBCC is
> still running and the last backup should be on the test box in about 30
> minutes.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:u1PqWzVvFHA.3500@.TK2MSFTNGP09.phx.gbl...
>
|||FWIW, the extra time you're seeing comes from DBCC doing extensive
non-clustered index to base table cross checks to determine which NC index
rows have no matching data rows, or which data rows have no matching NC
index rows. This only happens when we find corruptions in the database,
and, as you have found, is quite time-consuming.
Thanks,
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u0kgwaXvFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Problem solved. Corrupt NC index on table with apx 150 million rows.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eaOzlIWvFHA.3720@.TK2MSFTNGP14.phx.gbl...
>

No comments:

Post a Comment