I/O error (Torn page)

I run a sp in sqlserver 2000 and got this error message.
(1 row affected)
Msg 823, Level 24, State 2, Server DBINT02, Procedure
KundAvpris_Insert, Line 13
I/O error (torn page) detected during read at offset
0x0000013a29a000 in file
'E:\Program Files\Microsoft SQL
Server\MSSQL\data\MARKISDATA_Data.MDF'.
Does anybody know how i do to correct this error?I suggest you perform a log backup. Then restore the latest clean database backup and all subsequent
log backups (including this last one). This will most probably give you zero data loss.
If you don't have log backups in place, then just go for the last clean database backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Robert
Ask your system
administrator to check for disk corruption.You should make sure to run DBCC
CHECKDB or DBCC CHECKTABLE on that table.
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Torn pages does most likely occur because a partially performed write operation. CHECKDB or
CHECKTABLE does not help here, as it will only confirm what we already know: a corruption in the
database. Also, this does, unfortunately, temp some to try the repair options (which in most cases
doesn't help), and possibly hinder the ability to do the vital last log backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uhUFzm4dDHA.2340@.TK2MSFTNGP09.phx.gbl...
> Robert
> Ask your system
> administrator to check for disk corruption.You should make sure to run DBCC
> CHECKDB or DBCC CHECKTABLE on that table.
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||That might not all be necessary. If the torn page is in a non-clustered
index you can just rebuild the index and everything will be fine. If it is
in a clustered index or it is a page that is used by SQL Server internally,
Tibor's method is the safest way to go.
DBCC CHECKDB will tell you in which object the torn page is located. You can
run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
which can otherwise take a long time.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> I suggest you perform a log backup. Then restore the latest clean database
backup and all subsequent
> log backups (including this last one). This will most probably give you
zero data loss.
> If you don't have log backups in place, then just go for the last clean
database backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine.
Does above apply to torn pages as well?
I thought that torn pages are "corrupted beyond repair", even if a page can, technically, be dropped
as part of an index...
I.e., a torn page marks a "hands off - something is fishy here" to SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine. If it is
> in a clustered index or it is a page that is used by SQL Server internally,
> Tibor's method is the safest way to go.
> DBCC CHECKDB will tell you in which object the torn page is located. You can
> run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
> which can otherwise take a long time.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > I suggest you perform a log backup. Then restore the latest clean database
> backup and all subsequent
> > log backups (including this last one). This will most probably give you
> zero data loss.
> >
> > If you don't have log backups in place, then just go for the last clean
> database backups.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > I run a sp in sqlserver 2000 and got this error message.
> > >
> > > (1 row affected)
> > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > KundAvpris_Insert, Line 13
> > > I/O error (torn page) detected during read at offset
> > > 0x0000013a29a000 in file
> > > 'E:\Program Files\Microsoft SQL
> > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > >
> > > Does anybody know how i do to correct this error?
> >
> >
>|||Only thing a torn page tells you as far as I understand it, is that it was
written to disk partially but not completely, i.e. the check bits for all
the 512 byte sectors is the page are not the same, which means that some of
the sectors have changed the last time the page was written and some
haven't. It's a "logical" rather than a physical error, it doesn't tell you
anything about the current physical state of the page only about the current
logical state of the page (inconsistent) and that the last write operation
on that page didn't succeed completely. The page being torn in itself
doesn't make the harddisk space where it is located unusable. (The torn page
can ofcourse be caused by a harddisk problem which makes the disk space
unusable, but that's a separate issue.)
If the torn page has been cause by a power failure or a similar problem,
that is not a permanent hardware problem, like a bad sector on a disk, I see
no reason why you could not reuse the page?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine.
> Does above apply to torn pages as well?
> I thought that torn pages are "corrupted beyond repair", even if a page
can, technically, be dropped
> as part of an index...
> I.e., a torn page marks a "hands off - something is fishy here" to SQL
Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine. If it
is
> > in a clustered index or it is a page that is used by SQL Server
internally,
> > Tibor's method is the safest way to go.
> >
> > DBCC CHECKDB will tell you in which object the torn page is located. You
can
> > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
process,
> > which can otherwise take a long time.
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > I suggest you perform a log backup. Then restore the latest clean
database
> > backup and all subsequent
> > > log backups (including this last one). This will most probably give
you
> > zero data loss.
> > >
> > > If you don't have log backups in place, then just go for the last
clean
> > database backups.
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > I run a sp in sqlserver 2000 and got this error message.
> > > >
> > > > (1 row affected)
> > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > KundAvpris_Insert, Line 13
> > > > I/O error (torn page) detected during read at offset
> > > > 0x0000013a29a000 in file
> > > > 'E:\Program Files\Microsoft SQL
> > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > >
> > > > Does anybody know how i do to correct this error?
> > >
> > >
> >
> >
>|||I agree, Jacco. My point as only the SQL Server code (design of-). Whether SQL Server will never
re-uses/repairs a torn page or not, even though it can safely drop the page (because the HW might be
OK). I guess the answer is inside the SQL Server code, which I don't have access to... ;-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> Only thing a torn page tells you as far as I understand it, is that it was
> written to disk partially but not completely, i.e. the check bits for all
> the 512 byte sectors is the page are not the same, which means that some of
> the sectors have changed the last time the page was written and some
> haven't. It's a "logical" rather than a physical error, it doesn't tell you
> anything about the current physical state of the page only about the current
> logical state of the page (inconsistent) and that the last write operation
> on that page didn't succeed completely. The page being torn in itself
> doesn't make the harddisk space where it is located unusable. (The torn page
> can ofcourse be caused by a harddisk problem which makes the disk space
> unusable, but that's a separate issue.)
> If the torn page has been cause by a power failure or a similar problem,
> that is not a permanent hardware problem, like a bad sector on a disk, I see
> no reason why you could not reuse the page?
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine.
> >
> > Does above apply to torn pages as well?
> > I thought that torn pages are "corrupted beyond repair", even if a page
> can, technically, be dropped
> > as part of an index...
> > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> Server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine. If it
> is
> > > in a clustered index or it is a page that is used by SQL Server
> internally,
> > > Tibor's method is the safest way to go.
> > >
> > > DBCC CHECKDB will tell you in which object the torn page is located. You
> can
> > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> process,
> > > which can otherwise take a long time.
> > >
> > > --
> > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > Database Administrator
> > > Eurostop Ltd.
> > >
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > I suggest you perform a log backup. Then restore the latest clean
> database
> > > backup and all subsequent
> > > > log backups (including this last one). This will most probably give
> you
> > > zero data loss.
> > > >
> > > > If you don't have log backups in place, then just go for the last
> clean
> > > database backups.
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > >
> > > > > (1 row affected)
> > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > KundAvpris_Insert, Line 13
> > > > > I/O error (torn page) detected during read at offset
> > > > > 0x0000013a29a000 in file
> > > > > 'E:\Program Files\Microsoft SQL
> > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > >
> > > > > Does anybody know how i do to correct this error?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry Tibor, your post is not really clear to me...
I don't think that you can't drop an index because it contains a torn page,
if that is what you mean? (Too bad the problem is quite difficult to
replicate). Dropping an index only deallocates the index pages and deletes
the rows from the system tables and doesn't do anything to the actual pages,
so whether they are torn or not should not make any difference.
> I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
But you have access to people who have access (or at least have access to
people who have access) ;-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyZU7n5dDHA.1448@.TK2MSFTNGP12.phx.gbl...
> I agree, Jacco. My point as only the SQL Server code (design of-). Whether
SQL Server will never
> re-uses/repairs a torn page or not, even though it can safely drop the
page (because the HW might be
> OK). I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> > Only thing a torn page tells you as far as I understand it, is that it
was
> > written to disk partially but not completely, i.e. the check bits for
all
> > the 512 byte sectors is the page are not the same, which means that some
of
> > the sectors have changed the last time the page was written and some
> > haven't. It's a "logical" rather than a physical error, it doesn't tell
you
> > anything about the current physical state of the page only about the
current
> > logical state of the page (inconsistent) and that the last write
operation
> > on that page didn't succeed completely. The page being torn in itself
> > doesn't make the harddisk space where it is located unusable. (The torn
page
> > can ofcourse be caused by a harddisk problem which makes the disk space
> > unusable, but that's a separate issue.)
> >
> > If the torn page has been cause by a power failure or a similar problem,
> > that is not a permanent hardware problem, like a bad sector on a disk, I
see
> > no reason why you could not reuse the page?
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine.
> > >
> > > Does above apply to torn pages as well?
> > > I thought that torn pages are "corrupted beyond repair", even if a
page
> > can, technically, be dropped
> > > as part of an index...
> > > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> > Server.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine. If
it
> > is
> > > > in a clustered index or it is a page that is used by SQL Server
> > internally,
> > > > Tibor's method is the safest way to go.
> > > >
> > > > DBCC CHECKDB will tell you in which object the torn page is located.
You
> > can
> > > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> > process,
> > > > which can otherwise take a long time.
> > > >
> > > > --
> > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > > Database Administrator
> > > > Eurostop Ltd.
> > > >
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > > I suggest you perform a log backup. Then restore the latest clean
> > database
> > > > backup and all subsequent
> > > > > log backups (including this last one). This will most probably
give
> > you
> > > > zero data loss.
> > > > >
> > > > > If you don't have log backups in place, then just go for the last
> > clean
> > > > database backups.
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > > ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > > >
> > > > > > (1 row affected)
> > > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > > KundAvpris_Insert, Line 13
> > > > > > I/O error (torn page) detected during read at offset
> > > > > > 0x0000013a29a000 in file
> > > > > > 'E:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > > >
> > > > > > Does anybody know how i do to correct this error?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql