Monday, March 26, 2012

Error: 823 - Torn page - Reposting for Attention

Hi,
I am reposting my problem freshly , previously i posted it
as a part of existing thread. Sorry for Inconvience.
I had a server break down with two harddisks down on RAID
5. Was able to reocover from that situation. When I was
going through the event viewer I found the below error (
the time stamp is after two hours of my server
recovery,when a Maintanence Plan starts). I am not finding
msdb marked suspect and everything seems working normally.
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset
0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
Server\MSSQL\data\msdbdata.mdf'.
following the above i found a warning msg
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance
Plan 'DB Maintenance Plan1''
(0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
Invoked on: 2003-08-25 18:00:00 - Message: The job
failed. The Job was invoked by Schedule 5 (Schedule 1).
The last step to run was step 1 (Step 1).
on Executing DDBC CHECKDB
partial log
--
There are 0 rows in 0 pages for
object 'RTblEnumerationValueDef'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2069582411, index ID 2: Page (1:1569) could not
be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 2, page
(1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Last Lines
--
CHECKDB found 0 allocation errors and 2 consistency errors
in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the
errors found by DBCC CHECKDB (msdb ).
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I dont have the backup of msdb and when i try to backup
the existing one it gives torn page error.
Is this happend because of Database Maintanence plan which
was scheduled exactly few minutes before at the time of
error msg. I am just using backup option of user databases
and nothing else in that plan . (oR) some data missing coz
of system breakdown
Any help of this will be greatly appreciated.
Best Regards
ThirumalThirumal
Well,I think you have onto Maintenance Plan check box 'Attempt to repaire
any minor problems' turned on ,so you need turn off the checkbox.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
> Hi,
> I am reposting my problem freshly , previously i posted it
> as a part of existing thread. Sorry for Inconvience.
>
> I had a server break down with two harddisks down on RAID
> 5. Was able to reocover from that situation. When I was
> going through the event viewer I found the below error (
> the time stamp is after two hours of my server
> recovery,when a Maintanence Plan starts). I am not finding
> msdb marked suspect and everything seems working normally.
> Error: 823, Severity: 24, State: 2
> I/O error (torn page) detected during read at offset
> 0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
> Server\MSSQL\data\msdbdata.mdf'.
> following the above i found a warning msg
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan1''
> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
> Invoked on: 2003-08-25 18:00:00 - Message: The job
> failed. The Job was invoked by Schedule 5 (Schedule 1).
> The last step to run was step 1 (Step 1).
> on Executing DDBC CHECKDB
> partial log
> --
> There are 0 rows in 0 pages for
> object 'RTblEnumerationValueDef'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2069582411, index ID 2: Page (1:1569) could not
> be processed. See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 2069582411, index ID 2, page
> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >berrcode) failed. Values are 2057 and -1.
> Last Lines
> --
> CHECKDB found 0 allocation errors and 2 consistency errors
> in database 'msdb'.
> repair_allow_data_loss is the minimum repair level for the
> errors found by DBCC CHECKDB (msdb ).
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I dont have the backup of msdb and when i try to backup
> the existing one it gives torn page error.
> Is this happend because of Database Maintanence plan which
> was scheduled exactly few minutes before at the time of
> error msg. I am just using backup option of user databases
> and nothing else in that plan . (oR) some data missing coz
> of system breakdown
> Any help of this will be greatly appreciated.
> Best Regards
> Thirumal
>|||Hi Dimant,
Thanks for the reply. I dont have any system databases in
my maintenance plan and in fact i dint enabled integrity
check for my user databses .
Regards
Thirumal
>--Original Message--
>Thirumal
>Well,I think you have onto Maintenance Plan check
box 'Attempt to repaire
>any minor problems' turned on ,so you need turn off the
checkbox.
>
>
>"Thirumal" <treddym@.hotmail.com> wrote in message
>news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
>> Hi,
>> I am reposting my problem freshly , previously i posted
it
>> as a part of existing thread. Sorry for Inconvience.
>>
>> I had a server break down with two harddisks down on
RAID
>> 5. Was able to reocover from that situation. When I was
>> going through the event viewer I found the below error (
>> the time stamp is after two hours of my server
>> recovery,when a Maintanence Plan starts). I am not
finding
>> msdb marked suspect and everything seems working
normally.
>> Error: 823, Severity: 24, State: 2
>> I/O error (torn page) detected during read at offset
>> 0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
>> Server\MSSQL\data\msdbdata.mdf'.
>> following the above i found a warning msg
>> SQL Server Scheduled Job 'DB Backup Job for DB
Maintenance
>> Plan 'DB Maintenance Plan1''
>> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
>> Invoked on: 2003-08-25 18:00:00 - Message: The job
>> failed. The Job was invoked by Schedule 5 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> on Executing DDBC CHECKDB
>> partial log
>> --
>> There are 0 rows in 0 pages for
>> object 'RTblEnumerationValueDef'.
>> Server: Msg 8928, Level 16, State 1, Line 1
>> Object ID 2069582411, index ID 2: Page (1:1569) could
not
>> be processed. See other errors for details.
>> Server: Msg 8939, Level 16, State 1, Line 1
>> Table error: Object ID 2069582411, index ID 2, page
>> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>> >berrcode) failed. Values are 2057 and -1.
>> Last Lines
>> --
>> CHECKDB found 0 allocation errors and 2 consistency
errors
>> in database 'msdb'.
>> repair_allow_data_loss is the minimum repair level for
the
>> errors found by DBCC CHECKDB (msdb ).
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> I dont have the backup of msdb and when i try to backup
>> the existing one it gives torn page error.
>> Is this happend because of Database Maintanence plan
which
>> was scheduled exactly few minutes before at the time of
>> error msg. I am just using backup option of user
databases
>> and nothing else in that plan . (oR) some data missing
coz
>> of system breakdown
>> Any help of this will be greatly appreciated.
>> Best Regards
>> Thirumal
>
>.
>|||Ok
Do you have a last backup of MSDB database? If you do try to restore it ,
else you need to reser the suspect status.
UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = @.dbname
You must reboot SQL Server prior to accessing this database.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:024501c36ba5$eea87860$a001280a@.phx.gbl...
> Hi Dimant,
> Thanks for the reply. I dont have any system databases in
> my maintenance plan and in fact i dint enabled integrity
> check for my user databses .
> Regards
> Thirumal
> >--Original Message--
> >Thirumal
> >Well,I think you have onto Maintenance Plan check
> box 'Attempt to repaire
> >any minor problems' turned on ,so you need turn off the
> checkbox.
> >
> >
> >
> >
> >
> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> I am reposting my problem freshly , previously i posted
> it
> >> as a part of existing thread. Sorry for Inconvience.
> >>
> >>
> >> I had a server break down with two harddisks down on
> RAID
> >> 5. Was able to reocover from that situation. When I was
> >> going through the event viewer I found the below error (
> >> the time stamp is after two hours of my server
> >> recovery,when a Maintanence Plan starts). I am not
> finding
> >> msdb marked suspect and everything seems working
> normally.
> >>
> >> Error: 823, Severity: 24, State: 2
> >> I/O error (torn page) detected during read at offset
> >> 0x00000000c42000 in file 'd:\Program Files\Microsoft SQL
> >> Server\MSSQL\data\msdbdata.mdf'.
> >>
> >> following the above i found a warning msg
> >>
> >> SQL Server Scheduled Job 'DB Backup Job for DB
> Maintenance
> >> Plan 'DB Maintenance Plan1''
> >> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status: Failed -
> >> Invoked on: 2003-08-25 18:00:00 - Message: The job
> >> failed. The Job was invoked by Schedule 5 (Schedule 1).
> >> The last step to run was step 1 (Step 1).
> >>
> >> on Executing DDBC CHECKDB
> >>
> >> partial log
> >> --
> >> There are 0 rows in 0 pages for
> >> object 'RTblEnumerationValueDef'.
> >> Server: Msg 8928, Level 16, State 1, Line 1
> >> Object ID 2069582411, index ID 2: Page (1:1569) could
> not
> >> be processed. See other errors for details.
> >> Server: Msg 8939, Level 16, State 1, Line 1
> >> Table error: Object ID 2069582411, index ID 2, page
> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >> >berrcode) failed. Values are 2057 and -1.
> >>
> >> Last Lines
> >> --
> >>
> >> CHECKDB found 0 allocation errors and 2 consistency
> errors
> >> in database 'msdb'.
> >> repair_allow_data_loss is the minimum repair level for
> the
> >> errors found by DBCC CHECKDB (msdb ).
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> I dont have the backup of msdb and when i try to backup
> >> the existing one it gives torn page error.
> >>
> >> Is this happend because of Database Maintanence plan
> which
> >> was scheduled exactly few minutes before at the time of
> >> error msg. I am just using backup option of user
> databases
> >> and nothing else in that plan . (oR) some data missing
> coz
> >> of system breakdown
> >>
> >> Any help of this will be greatly appreciated.
> >>
> >> Best Regards
> >>
> >> Thirumal
> >>
> >
> >
> >.
> >|||Hi Dimant,
I dont have the backup of msdb. BTW as i mentioned in my
previous posts msdb is not marked suspect. Do i still need
to use reset queries?
Thanks
Thirumal
>--Original Message--
>Ok
>Do you have a last backup of MSDB database? If you do try
to restore it ,
>else you need to reser the suspect status.
>UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = @.dbname
>You must reboot SQL Server prior to accessing this
database.
>
>
>"Thirumal" <treddym@.hotmail.com> wrote in message
>news:024501c36ba5$eea87860$a001280a@.phx.gbl...
>> Hi Dimant,
>> Thanks for the reply. I dont have any system databases
in
>> my maintenance plan and in fact i dint enabled integrity
>> check for my user databses .
>> Regards
>> Thirumal
>> >--Original Message--
>> >Thirumal
>> >Well,I think you have onto Maintenance Plan check
>> box 'Attempt to repaire
>> >any minor problems' turned on ,so you need turn off the
>> checkbox.
>> >
>> >
>> >
>> >
>> >
>> >"Thirumal" <treddym@.hotmail.com> wrote in message
>> >news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I am reposting my problem freshly , previously i
posted
>> it
>> >> as a part of existing thread. Sorry for Inconvience.
>> >>
>> >>
>> >> I had a server break down with two harddisks down on
>> RAID
>> >> 5. Was able to reocover from that situation. When I
was
>> >> going through the event viewer I found the below
error (
>> >> the time stamp is after two hours of my server
>> >> recovery,when a Maintanence Plan starts). I am not
>> finding
>> >> msdb marked suspect and everything seems working
>> normally.
>> >>
>> >> Error: 823, Severity: 24, State: 2
>> >> I/O error (torn page) detected during read at offset
>> >> 0x00000000c42000 in file 'd:\Program Files\Microsoft
SQL
>> >> Server\MSSQL\data\msdbdata.mdf'.
>> >>
>> >> following the above i found a warning msg
>> >>
>> >> SQL Server Scheduled Job 'DB Backup Job for DB
>> Maintenance
>> >> Plan 'DB Maintenance Plan1''
>> >> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status:
Failed -
>> >> Invoked on: 2003-08-25 18:00:00 - Message: The job
>> >> failed. The Job was invoked by Schedule 5 (Schedule
1).
>> >> The last step to run was step 1 (Step 1).
>> >>
>> >> on Executing DDBC CHECKDB
>> >>
>> >> partial log
>> >> --
>> >> There are 0 rows in 0 pages for
>> >> object 'RTblEnumerationValueDef'.
>> >> Server: Msg 8928, Level 16, State 1, Line 1
>> >> Object ID 2069582411, index ID 2: Page (1:1569) could
>> not
>> >> be processed. See other errors for details.
>> >> Server: Msg 8939, Level 16, State 1, Line 1
>> >> Table error: Object ID 2069582411, index ID 2, page
>> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>> >> >berrcode) failed. Values are 2057 and -1.
>> >>
>> >> Last Lines
>> >> --
>> >>
>> >> CHECKDB found 0 allocation errors and 2 consistency
>> errors
>> >> in database 'msdb'.
>> >> repair_allow_data_loss is the minimum repair level
for
>> the
>> >> errors found by DBCC CHECKDB (msdb ).
>> >> DBCC execution completed. If DBCC printed error
>> messages,
>> >> contact your system administrator.
>> >>
>> >> I dont have the backup of msdb and when i try to
backup
>> >> the existing one it gives torn page error.
>> >>
>> >> Is this happend because of Database Maintanence plan
>> which
>> >> was scheduled exactly few minutes before at the time
of
>> >> error msg. I am just using backup option of user
>> databases
>> >> and nothing else in that plan . (oR) some data
missing
>> coz
>> >> of system breakdown
>> >>
>> >> Any help of this will be greatly appreciated.
>> >>
>> >> Best Regards
>> >>
>> >> Thirumal
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||You should open a case with Product support to resolve this in the best
way. From the error it looks as though there was a write error on the disk
prior to the last shutdown of the database.
Here is how books online describes torn pages:
This recovery option allows SQL Server to detect incomplete I/O operations
caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte
sector in an 8-kilobyte (KB) database page when the page is written to
disk. If a bit is in the wrong state when the page is later read by SQL
Server, the page was written incorrectly; a torn page is detected. Torn
pages are usually detected during recovery because any page that was
written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations
using a 512-byte sector. Therefore, 16 sectors are written per database
page. A torn page can occur if the system fails (for example, due to power
failure) between the time the operating system writes the first 512-byte
sector to disk and the completion of the 8-KB I/O operation. If the first
sector of a database page is successfully written before the failure, the
database page on disk will appear as updated, although it may not have
succeeded.
Note Using battery-backed disk caches can ensure that data is successfully
written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is
killed. If the torn page is detected during recovery, the database is also
marked suspect. The database backup should be restored, and any transaction
log backups applied, because it is physically inconsistent.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:0b0401c36bb2$d1845fd0$a301280a@.phx.gbl...
> Hi Dimant,
> I dont have the backup of msdb. BTW as i mentioned in my
> previous posts msdb is not marked suspect. Do i still need
> to use reset queries?
> Thanks
> Thirumal
> >--Original Message--
> >Ok
> >Do you have a last backup of MSDB database? If you do try
> to restore it ,
> >else you need to reser the suspect status.
> >UPDATE master..sysdatabases SET status = status ^ 256
> WHERE name = @.dbname
> >You must reboot SQL Server prior to accessing this
> database.
> >
> >
> >
> >
> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >news:024501c36ba5$eea87860$a001280a@.phx.gbl...
> >> Hi Dimant,
> >>
> >> Thanks for the reply. I dont have any system databases
> in
> >> my maintenance plan and in fact i dint enabled integrity
> >> check for my user databses .
> >>
> >> Regards
> >>
> >> Thirumal
> >> >--Original Message--
> >> >Thirumal
> >> >Well,I think you have onto Maintenance Plan check
> >> box 'Attempt to repaire
> >> >any minor problems' turned on ,so you need turn off the
> >> checkbox.
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >> >news:097701c36ba3$5dac4c80$a401280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> I am reposting my problem freshly , previously i
> posted
> >> it
> >> >> as a part of existing thread. Sorry for Inconvience.
> >> >>
> >> >>
> >> >> I had a server break down with two harddisks down on
> >> RAID
> >> >> 5. Was able to reocover from that situation. When I
> was
> >> >> going through the event viewer I found the below
> error (
> >> >> the time stamp is after two hours of my server
> >> >> recovery,when a Maintanence Plan starts). I am not
> >> finding
> >> >> msdb marked suspect and everything seems working
> >> normally.
> >> >>
> >> >> Error: 823, Severity: 24, State: 2
> >> >> I/O error (torn page) detected during read at offset
> >> >> 0x00000000c42000 in file 'd:\Program Files\Microsoft
> SQL
> >> >> Server\MSSQL\data\msdbdata.mdf'.
> >> >>
> >> >> following the above i found a warning msg
> >> >>
> >> >> SQL Server Scheduled Job 'DB Backup Job for DB
> >> Maintenance
> >> >> Plan 'DB Maintenance Plan1''
> >> >> (0xB35DFA686DAE5D4A82B3A8908A271A0B) - Status:
> Failed -
> >> >> Invoked on: 2003-08-25 18:00:00 - Message: The job
> >> >> failed. The Job was invoked by Schedule 5 (Schedule
> 1).
> >> >> The last step to run was step 1 (Step 1).
> >> >>
> >> >> on Executing DDBC CHECKDB
> >> >>
> >> >> partial log
> >> >> --
> >> >> There are 0 rows in 0 pages for
> >> >> object 'RTblEnumerationValueDef'.
> >> >> Server: Msg 8928, Level 16, State 1, Line 1
> >> >> Object ID 2069582411, index ID 2: Page (1:1569) could
> >> not
> >> >> be processed. See other errors for details.
> >> >> Server: Msg 8939, Level 16, State 1, Line 1
> >> >> Table error: Object ID 2069582411, index ID 2, page
> >> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >> >> >berrcode) failed. Values are 2057 and -1.
> >> >>
> >> >> Last Lines
> >> >> --
> >> >>
> >> >> CHECKDB found 0 allocation errors and 2 consistency
> >> errors
> >> >> in database 'msdb'.
> >> >> repair_allow_data_loss is the minimum repair level
> for
> >> the
> >> >> errors found by DBCC CHECKDB (msdb ).
> >> >> DBCC execution completed. If DBCC printed error
> >> messages,
> >> >> contact your system administrator.
> >> >>
> >> >> I dont have the backup of msdb and when i try to
> backup
> >> >> the existing one it gives torn page error.
> >> >>
> >> >> Is this happend because of Database Maintanence plan
> >> which
> >> >> was scheduled exactly few minutes before at the time
> of
> >> >> error msg. I am just using backup option of user
> >> databases
> >> >> and nothing else in that plan . (oR) some data
> missing
> >> coz
> >> >> of system breakdown
> >> >>
> >> >> Any help of this will be greatly appreciated.
> >> >>
> >> >> Best Regards
> >> >>
> >> >> Thirumal
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hi Jacco,
Thanks for the help. I have executed DBCC CHECKDB('msdb',
REPAIR_REBUILD)in single user mode and finding errors with
two tables at SQL output.
DBCC results for 'RTblVersions'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2069582411, index ID 2: Page (1:1569) could not
be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 2, page
(1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
>berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 2. Page
(1:1569) was not seen in the scan although its parent
(1:1370) and previous (1:1487) refer to it. Check any
previous errors.
There are 2333 rows in 27 pages for object 'RTblVersions'.
--
DBCC results for 'backupmediafamily'.
The repair level on the DBCC statement caused this
repair to be bypassed.
The repair level on the DBCC statement caused this
repair to be bypassed.
The repair level on the DBCC statement caused this
repair to be bypassed.
There are 580 rows in 11 pages for
object 'backupmediafamily'.
CHECKDB found 0 allocation errors and 3 consistency errors
in table 'backupmediafamily' (object ID 2069582411).
I am running only two user databases one is around 1GB and
the other in MB's . I am using Database maintainance
plans - Backup option. I am aware that Information needed
by the SQL Server Agent is stored in the msdb database,
for example, alerts and jobs and tear in msdb wont affect
user databases.
Any help to come out of this situation will be highly
regarded as i dont have msdb backups. Since I am new to
DBA works I would also request you to guide me for any
articles or ur suggestions on what all the databases needs
to be backup on regular basis.
Best Regards
Thirumal
>--Original Message--
>Hi,
>You can speed up the SET SINGLE_USER statement by adding
WITH ROLLBACK
>IMMEDIATE. This will roll back any open transaction in
the database and
>terminate all the connections.Without rollback immediate
SQL Server will
>just wait until all transactions have been committed or
rolled back.
>You can set the database back to normal with ALTER
DATABASE msdb SET
>MULTI_USER
>
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>|||Hi Thirumal,
The error that are reported for RTblVersions indicate that there is a
problem with a non-clustered index, which you can safely correct with DBBC
CHECKTABLE('RTblVersions' , REPAIR_ALLOW_DATA_LOSS).
I would also run DBBC CHECKTABLE('backupmediafamily',
REPAIR_ALLOW_DATA_LOSS).
You might possibly lose some data there, but the only information that is in
that table is where (tape or file locations) old backups have been backed up
to, something you can do without. It is only used to present you with a list
of backups to restore in the restore dialog in Enterprise Manager, but you
can still restore the backups if you can rememeber the filename.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Thirumal" <treddym@.hotmail.com> wrote in message
news:00cc01c36bea$bc194d80$a301280a@.phx.gbl...
> Hi Jacco,
> Thanks for the help. I have executed DBCC CHECKDB('msdb',
> REPAIR_REBUILD)in single user mode and finding errors with
> two tables at SQL output.
> DBCC results for 'RTblVersions'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2069582411, index ID 2: Page (1:1569) could not
> be processed. See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 2069582411, index ID 2, page
> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >berrcode) failed. Values are 2057 and -1.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 2069582411, index ID 2. Page
> (1:1569) was not seen in the scan although its parent
> (1:1370) and previous (1:1487) refer to it. Check any
> previous errors.
> There are 2333 rows in 27 pages for object 'RTblVersions'.
> --
> DBCC results for 'backupmediafamily'.
> The repair level on the DBCC statement caused this
> repair to be bypassed.
> The repair level on the DBCC statement caused this
> repair to be bypassed.
> The repair level on the DBCC statement caused this
> repair to be bypassed.
> There are 580 rows in 11 pages for
> object 'backupmediafamily'.
> CHECKDB found 0 allocation errors and 3 consistency errors
> in table 'backupmediafamily' (object ID 2069582411).
> I am running only two user databases one is around 1GB and
> the other in MB's . I am using Database maintainance
> plans - Backup option. I am aware that Information needed
> by the SQL Server Agent is stored in the msdb database,
> for example, alerts and jobs and tear in msdb wont affect
> user databases.
> Any help to come out of this situation will be highly
> regarded as i dont have msdb backups. Since I am new to
> DBA works I would also request you to guide me for any
> articles or ur suggestions on what all the databases needs
> to be backup on regular basis.
> Best Regards
> Thirumal
>
> >--Original Message--
> >Hi,
> >
> >You can speed up the SET SINGLE_USER statement by adding
> WITH ROLLBACK
> >IMMEDIATE. This will roll back any open transaction in
> the database and
> >terminate all the connections.Without rollback immediate
> SQL Server will
> >just wait until all transactions have been committed or
> rolled back.
> >
> >You can set the database back to normal with ALTER
> DATABASE msdb SET
> >MULTI_USER
> >
> >
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
>|||> I would like to hear from your experience on,
> what are the possible cases for these type of torn page
> erros on production boxes.
Power failure in the middle of writing a page. Hence the name "torn page". If you have HW write
caching *and proper battery backup* then the battery backup should make sure that the OS never get a
pertially completed I/O operation.
> Any suggestions & precautions to newbie's to excel as SQL
> DBA's from ur end would be very helpful.
Another option for you could have been to script out the jobs, alerts etc definitions and rebuild
the MSDB database (search KB for how to rebuild). And, of course, do regular backup of all databases
except tempdb, pubs and northwind from now on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Thirumal" <treddym@.hotmail.com> wrote in message news:004901c36bfc$86327450$a601280a@.phx.gbl...
> Hi Jacoo,
> Thanks a lot for helping this newbie. I have executed the
> commands on both the tables and to cross check i have run
> DBCC CHECKB and found found 0 allocation errors and 0
> consistency errors. ariseThe Maintenance Plan is working
> fine now. I would like to hear from your experience on,
> what are the possible cases for these type of torn page
> erros on production boxes.
> Any suggestions & precautions to newbie's to excel as SQL
> DBA's from ur end would be very helpful.
> Thanks & Best Regards
> Thirumal
> >--Original Message--
> >Hi Thirumal,
> >
> >The error that are reported for RTblVersions indicate
> that there is a
> >problem with a non-clustered index, which you can safely
> correct with DBBC
> >CHECKTABLE('RTblVersions' , REPAIR_ALLOW_DATA_LOSS).
> >
> >I would also run DBBC CHECKTABLE('backupmediafamily',
> >REPAIR_ALLOW_DATA_LOSS).
> >You might possibly lose some data there, but the only
> information that is in
> >that table is where (tape or file locations) old backups
> have been backed up
> >to, something you can do without. It is only used to
> present you with a list
> >of backups to restore in the restore dialog in Enterprise
> Manager, but you
> >can still restore the backups if you can rememeber the
> filename.
> >
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
> >"Thirumal" <treddym@.hotmail.com> wrote in message
> >news:00cc01c36bea$bc194d80$a301280a@.phx.gbl...
> >> Hi Jacco,
> >>
> >> Thanks for the help. I have executed DBCC CHECKDB
> ('msdb',
> >> REPAIR_REBUILD)in single user mode and finding errors
> with
> >> two tables at SQL output.
> >>
> >> DBCC results for 'RTblVersions'.
> >> Server: Msg 8928, Level 16, State 1, Line 1
> >> Object ID 2069582411, index ID 2: Page (1:1569) could
> not
> >> be processed. See other errors for details.
> >> Server: Msg 8939, Level 16, State 1, Line 1
> >> Table error: Object ID 2069582411, index ID 2, page
> >> (1:1569). Test (IS_ON (BUF_IOERR, bp->bstat) && bp-
> >> >berrcode) failed. Values are 2057 and -1.
> >> Server: Msg 8976, Level 16, State 1, Line 1
> >> Table error: Object ID 2069582411, index ID 2. Page
> >> (1:1569) was not seen in the scan although its parent
> >> (1:1370) and previous (1:1487) refer to it. Check any
> >> previous errors.
> >> There are 2333 rows in 27 pages for
> object 'RTblVersions'.
> >> --
> >> DBCC results for 'backupmediafamily'.
> >> The repair level on the DBCC statement caused
> this
> >> repair to be bypassed.
> >> The repair level on the DBCC statement caused
> this
> >> repair to be bypassed.
> >> The repair level on the DBCC statement caused
> this
> >> repair to be bypassed.
> >> There are 580 rows in 11 pages for
> >> object 'backupmediafamily'.
> >> CHECKDB found 0 allocation errors and 3 consistency
> errors
> >> in table 'backupmediafamily' (object ID 2069582411).
> >>
> >> I am running only two user databases one is around 1GB
> and
> >> the other in MB's . I am using Database maintainance
> >> plans - Backup option. I am aware that Information
> needed
> >> by the SQL Server Agent is stored in the msdb database,
> >> for example, alerts and jobs and tear in msdb wont
> affect
> >> user databases.
> >>
> >> Any help to come out of this situation will be highly
> >> regarded as i dont have msdb backups. Since I am new to
> >> DBA works I would also request you to guide me for any
> >> articles or ur suggestions on what all the databases
> needs
> >> to be backup on regular basis.
> >>
> >> Best Regards
> >>
> >> Thirumal
> >>
> >>
> >>
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >You can speed up the SET SINGLE_USER statement by
> adding
> >> WITH ROLLBACK
> >> >IMMEDIATE. This will roll back any open transaction in
> >> the database and
> >> >terminate all the connections.Without rollback
> immediate
> >> SQL Server will
> >> >just wait until all transactions have been committed or
> >> rolled back.
> >> >
> >> >You can set the database back to normal with ALTER
> >> DATABASE msdb SET
> >> >MULTI_USER
> >> >
> >> >
> >> >
> >> >--
> >> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >> >Database Administrator
> >> >Eurostop Ltd.
> >> >
> >> >
> >>
> >
> >
> >.
> >

No comments:

Post a Comment