IAM page (0:0) is pointed to by the previous pointer of IAM page (

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
object ID 992722589 index ID 0 but was not detected in the scan.
Server: Msg 2577, Level 16, State 1, Line 1
Chain sequence numbers are out of order in IAM chain for object ID
992722589, index ID 0. Page (1:867) sequence number 1 points to page
(1:48122) sequence number 0.
I get this error every time I run DBCC CHECKALLOC(DBName). The page number
is always the same but, the location of the error in the output moves on each
subsequent run. The Object ID does not exist in sysindexes, syscolumns,
sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
Going to a previous backup is not an option so I am getting ready to bcp all
of the data to a new database. Are there any other options for this?
-Alan-Couple of things...
If the 'ID' column is still unique, then check if its got a unique
constraint on, if not then put it on.
If that doe't work take off the foreign key from all tables referencing the
table which ID is part of and do your CHECKALLOC again.
If that doesn't work then have a look at
http://support.microsoft.com/kb/816084
Peter
"sqlsleuth" wrote:
> Server: Msg 2576, Level 16, State 1, Line 1
> IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> object ID 992722589 index ID 0 but was not detected in the scan.
> Server: Msg 2577, Level 16, State 1, Line 1
> Chain sequence numbers are out of order in IAM chain for object ID
> 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> (1:48122) sequence number 0.
> I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> is always the same but, the location of the error in the output moves on each
> subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> Going to a previous backup is not an option so I am getting ready to bcp all
> of the data to a new database. Are there any other options for this?
> -Alan-|||The Object ID
992722589 does not exist so there is nothing to check. Each time DBCC
CHECKALLOC is run it appears in the results of a different table.
"Peter Nolan" wrote:
> Couple of things...
> If the 'ID' column is still unique, then check if its got a unique
> constraint on, if not then put it on.
> If that doe't work take off the foreign key from all tables referencing the
> table which ID is part of and do your CHECKALLOC again.
> If that doesn't work then have a look at
> http://support.microsoft.com/kb/816084
> Peter
>
>
> "sqlsleuth" wrote:
> > Server: Msg 2576, Level 16, State 1, Line 1
> > IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> > object ID 992722589 index ID 0 but was not detected in the scan.
> > Server: Msg 2577, Level 16, State 1, Line 1
> > Chain sequence numbers are out of order in IAM chain for object ID
> > 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> > (1:48122) sequence number 0.
> >
> > I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> > is always the same but, the location of the error in the output moves on each
> > subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> > sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> > CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> > Going to a previous backup is not an option so I am getting ready to bcp all
> > of the data to a new database. Are there any other options for this?
> >
> > -Alan-|||What about http://support.microsoft.com/kb/816084 ?
Do you have auto statisics turned on ?
Are there any unique constraints in the tables themselves ?
The reason I'm going down this route is that occasionally when bulk loading
stuff I take off primary keys, indexes ect but somehow the system doesn't
register it properly and still thinks there something there, it could be as
part of the sysindexes or the statistics.
When it happened to me I re-established my primary key indexes ect.
You could also try dropping you statistics are re-establishing them.
Anyways thats about all I can help you, good luck and I hope you get a
successful resolution.
Peter
Peter
"sqlsleuth" wrote:
> The Object ID
> 992722589 does not exist so there is nothing to check. Each time DBCC
> CHECKALLOC is run it appears in the results of a different table.
> "Peter Nolan" wrote:
> > Couple of things...
> >
> > If the 'ID' column is still unique, then check if its got a unique
> > constraint on, if not then put it on.
> >
> > If that doe't work take off the foreign key from all tables referencing the
> > table which ID is part of and do your CHECKALLOC again.
> >
> > If that doesn't work then have a look at
> > http://support.microsoft.com/kb/816084
> >
> > Peter
> >
> >
> >
> >
> > "sqlsleuth" wrote:
> >
> > > Server: Msg 2576, Level 16, State 1, Line 1
> > > IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> > > object ID 992722589 index ID 0 but was not detected in the scan.
> > > Server: Msg 2577, Level 16, State 1, Line 1
> > > Chain sequence numbers are out of order in IAM chain for object ID
> > > 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> > > (1:48122) sequence number 0.
> > >
> > > I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> > > is always the same but, the location of the error in the output moves on each
> > > subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> > > sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> > > CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> > > Going to a previous backup is not an option so I am getting ready to bcp all
> > > of the data to a new database. Are there any other options for this?
> > >
> > > -Alan-|||Auto Statistics is on and there are a large number of unique constraints in
the tables. I can try to run the suggested fixes on the backup database and
see if it helps.
Thanks for the help.
"Peter Nolan" wrote:
> What about http://support.microsoft.com/kb/816084 ?
> Do you have auto statisics turned on ?
> Are there any unique constraints in the tables themselves ?
> The reason I'm going down this route is that occasionally when bulk loading
> stuff I take off primary keys, indexes ect but somehow the system doesn't
> register it properly and still thinks there something there, it could be as
> part of the sysindexes or the statistics.
> When it happened to me I re-established my primary key indexes ect.
> You could also try dropping you statistics are re-establishing them.
> Anyways thats about all I can help you, good luck and I hope you get a
> successful resolution.
> Peter
> Peter
> "sqlsleuth" wrote:
> > The Object ID
> > 992722589 does not exist so there is nothing to check. Each time DBCC
> > CHECKALLOC is run it appears in the results of a different table.
> >
> > "Peter Nolan" wrote:
> >
> > > Couple of things...
> > >
> > > If the 'ID' column is still unique, then check if its got a unique
> > > constraint on, if not then put it on.
> > >
> > > If that doe't work take off the foreign key from all tables referencing the
> > > table which ID is part of and do your CHECKALLOC again.
> > >
> > > If that doesn't work then have a look at
> > > http://support.microsoft.com/kb/816084
> > >
> > > Peter
> > >
> > >
> > >
> > >
> > > "sqlsleuth" wrote:
> > >
> > > > Server: Msg 2576, Level 16, State 1, Line 1
> > > > IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> > > > object ID 992722589 index ID 0 but was not detected in the scan.
> > > > Server: Msg 2577, Level 16, State 1, Line 1
> > > > Chain sequence numbers are out of order in IAM chain for object ID
> > > > 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> > > > (1:48122) sequence number 0.
> > > >
> > > > I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> > > > is always the same but, the location of the error in the output moves on each
> > > > subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> > > > sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> > > > CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> > > > Going to a previous backup is not an option so I am getting ready to bcp all
> > > > of the data to a new database. Are there any other options for this?
> > > >
> > > > -Alan-|||For complete troubleshooting of the above error message, you will need to firstly analyze the exact reason for the corruption of sequence numbers of IAM pages. If the corruption is caused due to hardware failure, then change the hardware component. However, in case of software corruption, run DBCC CHECKDB command  with appropriate repair clause  to repair the database.



DBCC CHECKDB command allows complete repair in most cases. But, if in case the command fails, then the only option is to use a advanced SQL Recovery software.

IAM page (0:0) is pointed to by the previous pointer of IAM page (

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
object ID 992722589 index ID 0 but was not detected in the scan.
Server: Msg 2577, Level 16, State 1, Line 1
Chain sequence numbers are out of order in IAM chain for object ID
992722589, index ID 0. Page (1:867) sequence number 1 points to page
(1:48122) sequence number 0.
I get this error every time I run DBCC CHECKALLOC(DBName). The page number
is always the same but, the location of the error in the output moves on each
subsequent run. The Object ID does not exist in sysindexes, syscolumns,
sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
Going to a previous backup is not an option so I am getting ready to bcp all
of the data to a new database. Are there any other options for this?
-Alan-
Couple of things...
If the 'ID' column is still unique, then check if its got a unique
constraint on, if not then put it on.
If that doe't work take off the foreign key from all tables referencing the
table which ID is part of and do your CHECKALLOC again.
If that doesn't work then have a look at
http://support.microsoft.com/kb/816084
Peter
"sqlsleuth" wrote:

> Server: Msg 2576, Level 16, State 1, Line 1
> IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> object ID 992722589 index ID 0 but was not detected in the scan.
> Server: Msg 2577, Level 16, State 1, Line 1
> Chain sequence numbers are out of order in IAM chain for object ID
> 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> (1:48122) sequence number 0.
> I get this error every time I run DBCC CHECKALLOC(DBName). The page number
> is always the same but, the location of the error in the output moves on each
> subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> sysdepends or sysobjects. I have put the DB in single user mode and run DBCC
> CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> Going to a previous backup is not an option so I am getting ready to bcp all
> of the data to a new database. Are there any other options for this?
> -Alan-

IAM page (0:0) is pointed to by the previous pointer of IAM page (

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
object ID 992722589 index ID 0 but was not detected in the scan.
Server: Msg 2577, Level 16, State 1, Line 1
Chain sequence numbers are out of order in IAM chain for object ID
992722589, index ID 0. Page (1:867) sequence number 1 points to page
(1:48122) sequence number 0.
I get this error every time I run DBCC CHECKALLOC(DBName). The page number
is always the same but, the location of the error in the output moves on eac
h
subsequent run. The Object ID does not exist in sysindexes, syscolumns,
sysdepends or sysobjects. I have put the DB in single user mode and run DBC
C
CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
Going to a previous backup is not an option so I am getting ready to bcp all
of the data to a new database. Are there any other options for this?
-Alan-Couple of things...
If the 'ID' column is still unique, then check if its got a unique
constraint on, if not then put it on.
If that doe't work take off the foreign key from all tables referencing the
table which ID is part of and do your CHECKALLOC again.
If that doesn't work then have a look at
http://support.microsoft.com/kb/816084
Peter
"sqlsleuth" wrote:

> Server: Msg 2576, Level 16, State 1, Line 1
> IAM page (0:0) is pointed to by the previous pointer of IAM page (1:867)
> object ID 992722589 index ID 0 but was not detected in the scan.
> Server: Msg 2577, Level 16, State 1, Line 1
> Chain sequence numbers are out of order in IAM chain for object ID
> 992722589, index ID 0. Page (1:867) sequence number 1 points to page
> (1:48122) sequence number 0.
> I get this error every time I run DBCC CHECKALLOC(DBName). The page numbe
r
> is always the same but, the location of the error in the output moves on e
ach
> subsequent run. The Object ID does not exist in sysindexes, syscolumns,
> sysdepends or sysobjects. I have put the DB in single user mode and run D
BCC
> CHECKDB(DBNAME, repair_allow_data_loss). It says it cannot repair the DB.
> Going to a previous backup is not an option so I am getting ready to bcp a
ll
> of the data to a new database. Are there any other options for this?
> -Alan-sql

I/O error bad page ID (was "Help")

Is there any other solutions that can I attach my data
with and error of Msg 823 Level 24 State 1 Line 1
I/O error (bad page ID)do you have a backup?|||For the most part: No. Restore from the last good backup. It sounds as if the database files were copied off the SQL Server while the SQL Server service was running. (Assuming you are trying to re-attach databases that is).

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/pr...ntsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.
You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339

/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>
|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Do a find for Torn I/O.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>

I/O error (torn page) detected during read at offset

Hi,
I am getting this error on running a Stored Procedure:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000062b4000 in
file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
What does this error indicate and what needs to be done to resolve this
error. Thanks in advance.You have hardware or system problem perhaps.
You need to run DBCC CheckDB and look for it to report any problems...
http://support.microsoft.com/kb/828339
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>|||Here is another article to better explain torn pages and what they mean...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Do a find for Torn I/O.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E3E0B9C5-C826-462D-8D82-026F0F0A590E@.microsoft.com...
> Hi,
> I am getting this error on running a Stored Procedure:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000062b4000 in
> file 'E:\database\Microsoft SQL Server\MSSQL\Data\Mydatabase_Data.MDF'.
> What does this error indicate and what needs to be done to resolve this
> error. Thanks in advance.
>
>sql

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

I Need to rise to the next level of SQL Programming, Recommendations?

This really isn't a SQL Server specific question, but more tword SQL in general. I am pretty good at SQL, being able to perform joins on several tables at one time. I am looking for more challenges in SQL though as I want to learn more and to rise to the next level. Can anyone recommend some good resources to me?

It would be challenging to me to learn how to do more complex queries involving three or more tables.

Ralph

This forums is a good place to start. Keep checking for questions being asked here and see if you can help out. This way you benefit from learning while the poster benefits from getting his issue resolved.

|||

Subscribe to SQL Server Magazine and read Itzik Ben-Gan's articles religiously. And get his books: very deep T-SQL information. Also, get the Inside SQL Server series of books; Kalen Delaney is the series editor.

These are the best resources available, hands down.

Let us know what you think of them, if you get any, okay?

Don

I need to know the data changes in some table on a row level

Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row

simply i need to do the following in a trigger:

For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue

I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help

Thanks and have a great day

Hi

It is very simply.

From Books Online:

"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."

More , look for "inserted tables" in Books Online

Gigi,

www.sqlserver.ro

|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||

On the triggers use the following statements

Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted

Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted

|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:

UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The concepts (the deleted table and the inserted table) was in SQL 2000 too.

Gigi Ciubuc

www.sqlserver.ro

I need to know the data changes in some table on a row level

Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row

simply i need to do the following in a trigger:

For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue

I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help

Thanks and have a great day

Hi

It is very simply.

From Books Online:

"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."

More , look for "inserted tables" in Books Online

Gigi,

www.sqlserver.ro

|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||

On the triggers use the following statements

Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted

Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted

|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:

UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The concepts (the deleted table and the inserted table) was in SQL 2000 too.

Gigi Ciubuc

www.sqlserver.ro