June 25, 2013 at 9:44 pm
i get the following error when selecting:
sysobjects: I/O error (torn page) detected during read at offset 0*0000000000000 in file ‘D:\XXX.mdf’
tbl_a: Could not continue scan with NOLOCK due to data movement.
any idea? someone?
June 26, 2013 at 2:15 am
Please run the following and post the full and unedited output
DBCC CheckDB(<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
June 26, 2013 at 2:49 am
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:1610) with latch type SH. sysobjects failed.
June 26, 2013 at 2:58 am
Restore from a clean backup, your database is irreparably corrupt.
Once done, check your IO subsystem for 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
June 26, 2013 at 4:47 am
thank you very much!!!!
June 26, 2013 at 5:03 am
If it is a very big database then you can restore only page (1:1610) to save restoration time
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 26, 2013 at 8:28 am
psingla (6/26/2013)
If it is a very big database then you can restore only page (1:1610) to save restoration time
No, he can't.
Firstly because it's a SQL 2000 database and page restore didn't exist in SQL 2000, second because you can't do a page restore of the critical system tables, which is where the corruption is in this case.
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
June 26, 2013 at 8:36 am
GilaMonster (6/26/2013)
No, he can't.Firstly because it's a SQL 2000 database and page restore didn't exist in SQL 2000, second because you can't do a page restore of the critical system tables, which is where the corruption is in this case.
Thanks Gail,I was not aware that page restore doesn't work for critical system tables
but how you came to know this is SQL 2000?I haven't worked on 2000...
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 26, 2013 at 12:45 pm
psingla (6/26/2013)
but how you came to know this is SQL 2000?I haven't worked on 2000...
Because it's posted in the SQL 2000 forum, because the error messages are in the SQL 2000 form (they were all rewritten for SQL 2005) and because the corruption message references sysobjects, a table that does not exist in SQL 2005+
It could be SQL 7 too, but it's definitely not SQL 2005 or higher.
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
June 26, 2013 at 7:58 pm
GilaMonster (6/26/2013)
corruption message references sysobjects, a table that does not exist in SQL 2005+It could be SQL 7 too, but it's definitely not SQL 2005 or higher.
Thanks again,I missed that point it's posted in the SQL 2000 forum.
BTW sysobjects view exists in 2005+ versions also.So if somebody is trying to select data from sysobjects in later 2005+ then he can get the same error that is listed here.
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 27, 2013 at 2:12 am
psingla (6/26/2013)
BTW sysobjects view exists in 2005+ versions also.
The view does, but it's a view and not a table and since a view does not store data, sysobjects cannot become corrupt. Its underlying tables can, but not the view.
So if somebody is trying to select data from sysobjects in later 2005+ then he can get the same error that is listed here.
No he can't.
He cannot get this exact error for a number of reasons, not least because the errors were rewritten in SQL 2005 and no longer have the form that was posted here. Also because sysobjects is not a table in SQL 2005+, it's a view, and any corruption that affects the underlying tables will be reported as being in the underlying tables, not sysobjects.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply