Friday, March 30, 2012
Iconsistent DB
message: Error 823
I/O error <error> detected during <operation> at offset <offset> in file
'<file>'
Executei dbcc checkdb and showed some errors, executed dbcc checkdb with
repair_rebuild, but it did not correct with error (The repair level on the
DBCC statement caused this repair to be bypassed. The system cannot self
repair this error.)
It has another possibility to correct?
And I do not have backup brought up to data.If you don't have a backup then you're only option is to run checkedb with
the REPAIR_ALLOW_DATA_LOSS option. Note that this will break the
transactional consistency of relationships between tables and so on. If
you're unsure how to proceed you should contact PSS who will walk you
through it (http://support.microsoft.com/)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carla" <Carla@.discussions.microsoft.com> wrote in message
news:3A6CAEB4-B4DC-48BB-B6AE-67B6979DB453@.microsoft.com...
> I attached a base and when initiating the system appears the following
> message: Error 823
> I/O error <error> detected during <operation> at offset <offset> in file
> '<file>'
> Executei dbcc checkdb and showed some errors, executed dbcc checkdb with
> repair_rebuild, but it did not correct with error (The repair level on the
> DBCC statement caused this repair to be bypassed. The system cannot self
> repair this error.)
> It has another possibility to correct?
> And I do not have backup brought up to data.|||Hi,
I used to REPAIR_ALLOW_DATA_LOSS, but i continued with the same errors.
This is end the error
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object
ID 1094862163)' (object ID 1094862163).
The system cannot self repair this error.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object
ID 1229017694)' (object ID 1229017694).
CHECKDB found 62 allocation errors and 1 consistency errors in database
'itlsys'.
Help me
"Paul S Randal [MS]" wrote:
> If you don't have a backup then you're only option is to run checkedb with
> the REPAIR_ALLOW_DATA_LOSS option. Note that this will break the
> transactional consistency of relationships between tables and so on. If
> you're unsure how to proceed you should contact PSS who will walk you
> through it (http://support.microsoft.com/)
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Carla" <Carla@.discussions.microsoft.com> wrote in message
> news:3A6CAEB4-B4DC-48BB-B6AE-67B6979DB453@.microsoft.com...
> >
> > I attached a base and when initiating the system appears the following
> > message: Error 823
> > I/O error <error> detected during <operation> at offset <offset> in file
> > '<file>'
> > Executei dbcc checkdb and showed some errors, executed dbcc checkdb with
> > repair_rebuild, but it did not correct with error (The repair level on the
> > DBCC statement caused this repair to be bypassed. The system cannot self
> > repair this error.)
> > It has another possibility to correct?
> > And I do not have backup brought up to data.
>
>|||Can you post the actual errors it found please?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carla" <Carla@.discussions.microsoft.com> wrote in message
news:8C62BD23-7200-4E44-9133-C72ED17DD359@.microsoft.com...
> Hi,
> I used to REPAIR_ALLOW_DATA_LOSS, but i continued with the same errors.
> This is end the error
> CHECKDB found 1 allocation errors and 0 consistency errors in table
'(Object
> ID 1094862163)' (object ID 1094862163).
> The system cannot self repair this error.
> CHECKDB found 1 allocation errors and 0 consistency errors in table
'(Object
> ID 1229017694)' (object ID 1229017694).
> CHECKDB found 62 allocation errors and 1 consistency errors in database
> 'itlsys'.
> Help me
>
> "Paul S Randal [MS]" wrote:
> > If you don't have a backup then you're only option is to run checkedb
with
> > the REPAIR_ALLOW_DATA_LOSS option. Note that this will break the
> > transactional consistency of relationships between tables and so on. If
> > you're unsure how to proceed you should contact PSS who will walk you
> > through it (http://support.microsoft.com/)
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Carla" <Carla@.discussions.microsoft.com> wrote in message
> > news:3A6CAEB4-B4DC-48BB-B6AE-67B6979DB453@.microsoft.com...
> > >
> > > I attached a base and when initiating the system appears the following
> > > message: Error 823
> > > I/O error <error> detected during <operation> at offset <offset> in
file
> > > '<file>'
> > > Executei dbcc checkdb and showed some errors, executed dbcc checkdb
with
> > > repair_rebuild, but it did not correct with error (The repair level on
the
> > > DBCC statement caused this repair to be bypassed. The system cannot
self
> > > repair this error.)
> > > It has another possibility to correct?
> > > And I do not have backup brought up to data.
> >
> >
> >|||Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:323520) has invalid PFS_PAGE page header
values. Type is 0. Check type, object ID and page ID on the page.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID -2130691967, index ID 18764, page (1:323520). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID -2130691967, index ID 18764, page (1:323520). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8921, Level 16, State 1, Line 1
CHECKTABLE terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent. Check
previous errors.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:299256) to (1:307343). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:307344) to (1:315431). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:315432) to (1:323519). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:323520) to (1:331607). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:331608) to (1:339695). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:339696). Test (m_headerVersion
== HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:339696). Test ((m_type
>=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE &&
level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:339696). Test (m_freeData >=PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
failed. Values are 0 and 8192.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:339696) to (1:347783). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:347784). Test (m_headerVersion
== HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:347784). Test ((m_type
>=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE &&
level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:347784). Test (m_freeData >=PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
failed. Values are 0 and 8192.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:347784) to (1:355871). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:355872). Test (m_headerVersion
== HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:355872). Test ((m_type
>=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE &&
level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:355872). Test (m_freeData >=PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
failed. Values are 0 and 8192.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:355872) to (1:363959). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:363960) to (1:372047). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:372048) to (1:380135). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:380136) to (1:388223). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:388224) to (1:396311). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:396312). Test (m_headerVersion
== HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:396312). Test ((m_type
>=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE &&
level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:396312). Test (m_freeData >=PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
failed. Values are 0 and 8192.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:396312). Test (*(((int*)
&m_reservedB) + i) == 0) failed. Values are 0 and 892941614.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:396312) to (1:404399). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:404400) to (1:412487). See other errors for cause.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:412488) to (1:420575). See other errors for cause.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 1 allocation errors and 1 consistency errors in table '(Object
ID -2130691967)' (object ID -2130691967).
DBCC results for 'itlsys'.
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.
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 system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be
bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be
bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
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.
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 system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
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.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify
database ID 7 pages from (1:420576) to (1:428663). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 8224, page (1:412488). Test
(m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 8224, page (1:412488). Test ((m_type
>=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE &&
level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 8224, page (1:412488). Test (m_freeData
>= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
failed. Values are 0 and 8192.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 8224, page (1:412488). Test (*(((int*)
&m_reservedB) + i) == 0) failed. Values are 0 and 791763761.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1160, index ID 8224, page (1:380136). Test
(m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1160, index ID 8224, page (1:380136). Test (m_slotCnt
< MaxSlot) failed. Values are 10240 and 4048.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1160, index ID 8224, page (1:380136). Test (*(((int*)
&m_reservedB) + i) == 0) failed. Values are 1 and -1207959552.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 50345345, index ID 18764, page (1:315432). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
(m_headerVersion == HEADER_7_0) failed. Values are 53 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
(m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt *
sizeof (Slot)) failed. Values are 0 and -8256.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
(m_slotCnt < MaxSlot) failed. Values are 8224 and 4048.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
(*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538976288.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
(m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
(m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 8224 and 8096.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
(m_slotCnt < MaxSlot) failed. Values are 8224 and 4048.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
(*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538976288.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976311, index ID 32, page (1:372048). Test
(m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976311, index ID 32, page (1:372048). Test
(m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 13619 and 8096.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976311, index ID 32, page (1:372048). Test
(m_slotCnt < MaxSlot) failed. Values are 8224 and 4048.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 538976311, index ID 32, page (1:372048). Test
(*(((int*) &m_reservedB) + i) == 0) failed. Values are 1 and 538976288.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 771751936, index ID 13618, page (1:420576). Test
(m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 771751936, index ID 13618, page (1:420576). Test
(*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538976288.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
(m_headerVersion == HEADER_7_0) failed. Values are 51 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
(m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 8224 and 8096.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
(m_slotCnt < MaxSlot) failed. Values are 14080 and 4048.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
(*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538980630.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
(m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
(m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt *
sizeof (Slot)) failed. Values are 14640 and 8094.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
(m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 13614 and 8096.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
(*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 825897010.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1094862163, index ID 18764, page (1:331608). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
The repair level on the DBCC statement caused this repair to be
bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 33 allocation errors and 0 consistency errors not associated
with any single object.
DBCC results for 'sysobjects'.
There are 838 rows in 13 pages for object 'sysobjects'.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 3 allocation errors and 0 consistency errors in table '(Object
ID 1160)' (object ID 1160).
The system cannot self repair this error.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object
ID 50345345)' (object ID 50345345).
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 8 allocation errors and 0 consistency errors in table '(Object
ID 538976288)' (object ID 538976288).
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 4 allocation errors and 0 consistency errors in table '(Object
ID 538976311)' (object ID 538976311).
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 2 allocation errors and 0 consistency errors in table '(Object
ID 771751936)' (object ID 771751936).
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 4 allocation errors and 0 consistency errors in table '(Object
ID 824188960)' (object ID 824188960).
The repair level on the DBCC statement caused this repair to be
bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
CHECKDB found 4 allocation errors and 0 consistency errors in table '(Object
ID 892418303)' (object ID 892418303).
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1229017694, index ID 18764, page (1:307344). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
The system cannot self repair this error.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object
ID 1094862163)' (object ID 1094862163).
The system cannot self repair this error.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object
ID 1229017694)' (object ID 1229017694).
CHECKDB found 62 allocation errors and 1 consistency errors in database
'itlsys'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||Ah - you've hit one of the few page corruptions that are unrepairable - a
PFS page header. SQL Server cannot fix this error, so unless you have a
backup, your only option is to extract the data from the database into a new
database. You also need to run hardware diagnostics on your system as I'm
guessing that a portion of the PFS page has been zeroed out.
You should contact PSS to help you as this is beyond the scope of the
newsgroups.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carla" <Carla@.discussions.microsoft.com> wrote in message
news:1F022C26-9F0B-41E0-B1D7-3CADE197E37D@.microsoft.com...
> Server: Msg 8946, Level 16, State 12, Line 2
> Table error: Allocation page (1:323520) has invalid PFS_PAGE page header
> values. Type is 0. Check type, object ID and page ID on the page.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID -2130691967, index ID 18764, page (1:323520). Test
> (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057
and -1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID -2130691967, index ID 18764, page (1:323520). Test
> (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057
and -1.
> Server: Msg 8921, Level 16, State 1, Line 1
> CHECKTABLE terminated. A failure was detected while collecting facts.
> Possibly tempdb out of space or a system table is inconsistent. Check
> previous errors.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:299256) to (1:307343). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:307344) to (1:315431). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:315432) to (1:323519). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:323520) to (1:331607). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:331608) to (1:339695). See other errors for
cause.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:339696). Test
(m_headerVersion
> == HEADER_7_0) failed. Values are 0 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:339696). Test ((m_type
> >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE
&&
> level == BASIC_HEADER)) failed. Values are 0 and 101.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:339696). Test (m_freeData >=> PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
> failed. Values are 0 and 8192.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:339696) to (1:347783). See other errors for
cause.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:347784). Test
(m_headerVersion
> == HEADER_7_0) failed. Values are 0 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:347784). Test ((m_type
> >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE
&&
> level == BASIC_HEADER)) failed. Values are 0 and 101.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:347784). Test (m_freeData >=> PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
> failed. Values are 0 and 8192.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:347784) to (1:355871). See other errors for
cause.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:355872). Test
(m_headerVersion
> == HEADER_7_0) failed. Values are 0 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:355872). Test ((m_type
> >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE
&&
> level == BASIC_HEADER)) failed. Values are 0 and 101.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:355872). Test (m_freeData >=> PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
> failed. Values are 0 and 8192.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:355872) to (1:363959). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:363960) to (1:372047). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:372048) to (1:380135). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:380136) to (1:388223). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:388224) to (1:396311). See other errors for
cause.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:396312). Test
(m_headerVersion
> == HEADER_7_0) failed. Values are 0 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:396312). Test ((m_type
> >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE
&&
> level == BASIC_HEADER)) failed. Values are 0 and 101.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:396312). Test (m_freeData >=> PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot))
> failed. Values are 0 and 8192.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page (1:396312). Test (*(((int*)
> &m_reservedB) + i) == 0) failed. Values are 0 and 892941614.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:396312) to (1:404399). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:404400) to (1:412487). See other errors for
cause.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:412488) to (1:420575). See other errors for
cause.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 1 allocation errors and 1 consistency errors in table
'(Object
> ID -2130691967)' (object ID -2130691967).
> DBCC results for 'itlsys'.
> 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.
> 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 system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The repair level on the DBCC statement caused this repair to be
> bypassed.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The repair level on the DBCC statement caused this repair to be
> bypassed.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> 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.
> 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 system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> 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.
> Server: Msg 8998, Level 16, State 1, Line 1
> Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to
verify
> database ID 7 pages from (1:420576) to (1:428663). See other errors for
cause.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 8224, page (1:412488). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 8224, page (1:412488). Test ((m_type
> >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE
&&
> level == BASIC_HEADER)) failed. Values are 0 and 101.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 8224, page (1:412488). Test (m_freeData
> >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof
(Slot))
> failed. Values are 0 and 8192.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 8224, page (1:412488). Test (*(((int*)
> &m_reservedB) + i) == 0) failed. Values are 0 and 791763761.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1160, index ID 8224, page (1:380136). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1160, index ID 8224, page (1:380136). Test
(m_slotCnt
> < MaxSlot) failed. Values are 10240 and 4048.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1160, index ID 8224, page (1:380136). Test
(*(((int*)
> &m_reservedB) + i) == 0) failed. Values are 1 and -1207959552.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 50345345, index ID 18764, page (1:315432). Test
> (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057
and -1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 53 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
> (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt *
> sizeof (Slot)) failed. Values are 0 and -8256.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
> (m_slotCnt < MaxSlot) failed. Values are 8224 and 4048.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:363960). Test
> (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538976288.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
> (m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 8224 and 8096.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
> (m_slotCnt < MaxSlot) failed. Values are 8224 and 4048.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976288, index ID 8224, page (1:388224). Test
> (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538976288.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976311, index ID 32, page (1:372048). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976311, index ID 32, page (1:372048). Test
> (m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 13619 and 8096.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976311, index ID 32, page (1:372048). Test
> (m_slotCnt < MaxSlot) failed. Values are 8224 and 4048.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 538976311, index ID 32, page (1:372048). Test
> (*(((int*) &m_reservedB) + i) == 0) failed. Values are 1 and 538976288.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 771751936, index ID 13618, page (1:420576). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 32 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 771751936, index ID 13618, page (1:420576). Test
> (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538976288.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 51 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
> (m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 8224 and 8096.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
> (m_slotCnt < MaxSlot) failed. Values are 14080 and 4048.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 824188960, index ID 8224, page (1:404400). Test
> (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 538980630.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
> (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
> (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt *
> sizeof (Slot)) failed. Values are 14640 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
> (m_freeCnt <= PAGESIZE - PAGEHEADSIZE) failed. Values are 13614 and 8096.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 892418303, index ID 25966, page (1:299256). Test
> (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 825897010.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1094862163, index ID 18764, page (1:331608). Test
> (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057
and -1.
> The repair level on the DBCC statement caused this repair to be
> bypassed.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 33 allocation errors and 0 consistency errors not associated
> with any single object.
> DBCC results for 'sysobjects'.
> There are 838 rows in 13 pages for object 'sysobjects'.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 3 allocation errors and 0 consistency errors in table
'(Object
> ID 1160)' (object ID 1160).
> The system cannot self repair this error.
> CHECKDB found 1 allocation errors and 0 consistency errors in table
'(Object
> ID 50345345)' (object ID 50345345).
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 8 allocation errors and 0 consistency errors in table
'(Object
> ID 538976288)' (object ID 538976288).
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 4 allocation errors and 0 consistency errors in table
'(Object
> ID 538976311)' (object ID 538976311).
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 2 allocation errors and 0 consistency errors in table
'(Object
> ID 771751936)' (object ID 771751936).
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 4 allocation errors and 0 consistency errors in table
'(Object
> ID 824188960)' (object ID 824188960).
> The repair level on the DBCC statement caused this repair to be
> bypassed.
> The system cannot self repair this error.
> The system cannot self repair this error.
> The system cannot self repair this error.
> CHECKDB found 4 allocation errors and 0 consistency errors in table
'(Object
> ID 892418303)' (object ID 892418303).
> Server: Msg 8939, Level 16, State 98, Line 1
> Table error: Object ID 1229017694, index ID 18764, page (1:307344). Test
> (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057
and -1.
> The system cannot self repair this error.
> CHECKDB found 1 allocation errors and 0 consistency errors in table
'(Object
> ID 1094862163)' (object ID 1094862163).
> The system cannot self repair this error.
> CHECKDB found 1 allocation errors and 0 consistency errors in table
'(Object
> ID 1229017694)' (object ID 1229017694).
> CHECKDB found 62 allocation errors and 1 consistency errors in database
> 'itlsys'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
ICommandText::Execute() causes mem leaks?
I'm trying the VS2005 addon Deleaker for the first time, and noticed I got about 40 "heap" memory leaks after the following m_pICmdText->Execute() call. Both m_pICmdText and m_pICmdPrepare are released before exiting the program. What else could cause these leaks? I've narrowed it down to only occuring if the execute statment is called. m_ulNumExecutions is 1 and a_pwszQuery is a CREATE TABLE statement:
// Set command sql query
hr = m_pICmdText->SetCommandText(DBGUID_SQL, a_pwszQuery);
CHKHR(hr, L"Failed to set command text\n", _ExitExeSql);
// Prepare command text for execution
hr = m_pICmdPrepare->Prepare(a_ulNumExecutions);
CHKHR(hr, L"Failed to prepare command text - Invalid Query\n", _ExitExeSql);
for(ULONG i=0;i<a_ulNumExecutions;i++)
{
//Execute query and place results in m_pIRowset
hr = m_pICmdText->Execute(NULL, IID_IRowset, NULL, NULL, (IUnknown **) &m_pIRowset);
CHKHR(hr, L"Failed to execute SQL\n", _ExitExeSql);
}
Thanks!
Jeff
Are you releasing the m_pIRowset variable?
|||Yes, but in this case it's not being used since it was a CREATE TABLE statement. My destructor is fairly thorough, but maybe I'm missing something:
Code Snippet
if(m_pIRowset!=NULL)
{
m_pIRowset->Release();
m_pIRowset = NULL;
}
if(m_pICmdPrepare != NULL)
{
m_pICmdPrepare->Release();
m_pICmdPrepare = NULL;
}
if(m_pICmdText != NULL)
{
m_pICmdText->Release();
m_pICmdText = NULL;
}
if(m_pIDBCrtCmd != NULL)
{
m_pIDBCrtCmd->Release();
m_pIDBCrtCmd = NULL;
}
if(m_pIDBCreateSession != NULL)
{
m_pIDBCreateSession->Release();
m_pIDBCreateSession = NULL;
}
if(m_pISequentialStream != NULL)
{
m_pISequentialStream->Release();
m_pISequentialStream = NULL;
}
if(m_pIUnknownSession != NULL)
{
m_pIUnknownSession->Release();
m_pIUnknownSession = NULL;
}
if(m_pIDBDataSourceAdmin != NULL)
{
m_pIDBDataSourceAdmin->Release();
m_pIDBDataSourceAdmin = NULL;
}
if(m_pIDBProperties != NULL)
{
m_pIDBProperties->Release();
m_pIDBProperties = NULL;
}
if(m_pIDBInitialize != NULL)
{
m_pIDBInitialize->Uninitialize();
m_pIDBInitialize->Release();
m_pIDBInitialize = NULL;
}
The "leak" that I get always has the following stack trace:
MSCVCR80.dll!_calloc_impl Line 94 (f:\sp\vctools\crt_bld\self_x86\crt\src\calloc.c)
MSCVCR80.dll!_calloc_crt Line 61 (f:\sp\vctools\crt_bld\self_x86\crt\src\crtheap.c)
I'm starting to wonder if it's just picking up the allocated space in the sql server engine transaction buffer. I noticed it doesn't give me leaks for SELECT statements.
Wednesday, March 28, 2012
IAM error?
have:
Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
range of this database.
This error can only be fixed by running dbcc checkdb allow_data_loss. Can
anyone tell me what may be causing this error and what I can change or avoid
so it stops happening?
TIA
jjYou have corruption in the database. Unless you have backups from before
you got the corruption you are probably going to have to loose some data by
running CHECKDB as mentioned. The first choice is always try to restore
from known good backups though.
--
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>I have gotten the following error a few times on just one database that I
>have:
> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
> range of this database.
> This error can only be fixed by running dbcc checkdb allow_data_loss. Can
> anyone tell me what may be causing this error and what I can change or
> avoid so it stops happening?
> TIA
> jj
>|||Actually I fixed the errors with the aforementioned script but since I only
have these errors on this one db every few months I'm wondering what's going
on.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> You have corruption in the database. Unless you have backups from before
> you got the corruption you are probably going to have to loose some data
> by running CHECKDB as mentioned. The first choice is always try to
> restore from known good backups though.
> --
> Andrew J. Kelly SQL MVP
>
> "jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
> news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>>I have gotten the following error a few times on just one database that I
>>have:
>> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of
>> the range of this database.
>> This error can only be fixed by running dbcc checkdb allow_data_loss. Can
>> anyone tell me what may be causing this error and what I can change or
>> avoid so it stops happening?
>> TIA
>> jj
>|||Sounds like you have hardware problems. That is the most common cause of
corruption these days. Fixing the problem with DBCC CHECKDB is not the best
solution. You loose data each time you fix it that way. Do you know what
data you lost? Your database is no longer solid with regards to data
integrity but you haven't a clue as to where. Not a good situation to be
in.
--
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OLEZlDIwFHA.664@.tk2msftngp13.phx.gbl...
> Actually I fixed the errors with the aforementioned script but since I
> only have these errors on this one db every few months I'm wondering
> what's going on.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
>> You have corruption in the database. Unless you have backups from before
>> you got the corruption you are probably going to have to loose some data
>> by running CHECKDB as mentioned. The first choice is always try to
>> restore from known good backups though.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
>> news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>>I have gotten the following error a few times on just one database that I
>>have:
>> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of
>> the range of this database.
>> This error can only be fixed by running dbcc checkdb allow_data_loss.
>> Can anyone tell me what may be causing this error and what I can change
>> or avoid so it stops happening?
>> TIA
>> jj
>>
>
IAM error?
have:
Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
range of this database.
This error can only be fixed by running dbcc checkdb allow_data_loss. Can
anyone tell me what may be causing this error and what I can change or avoid
so it stops happening?
TIA
jj
You have corruption in the database. Unless you have backups from before
you got the corruption you are probably going to have to loose some data by
running CHECKDB as mentioned. The first choice is always try to restore
from known good backups though.
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>I have gotten the following error a few times on just one database that I
>have:
> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
> range of this database.
> This error can only be fixed by running dbcc checkdb allow_data_loss. Can
> anyone tell me what may be causing this error and what I can change or
> avoid so it stops happening?
> TIA
> jj
>
|||Actually I fixed the errors with the aforementioned script but since I only
have these errors on this one db every few months I'm wondering what's going
on.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> You have corruption in the database. Unless you have backups from before
> you got the corruption you are probably going to have to loose some data
> by running CHECKDB as mentioned. The first choice is always try to
> restore from known good backups though.
> --
> Andrew J. Kelly SQL MVP
>
> "jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
> news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>
|||Sounds like you have hardware problems. That is the most common cause of
corruption these days. Fixing the problem with DBCC CHECKDB is not the best
solution. You loose data each time you fix it that way. Do you know what
data you lost? Your database is no longer solid with regards to data
integrity but you haven't a clue as to where. Not a good situation to be
in.
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OLEZlDIwFHA.664@.tk2msftngp13.phx.gbl...
> Actually I fixed the errors with the aforementioned script but since I
> only have these errors on this one db every few months I'm wondering
> what's going on.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
>
IAM error?
have:
Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
range of this database.
This error can only be fixed by running dbcc checkdb allow_data_loss. Can
anyone tell me what may be causing this error and what I can change or avoid
so it stops happening?
TIA
jjYou have corruption in the database. Unless you have backups from before
you got the corruption you are probably going to have to loose some data by
running CHECKDB as mentioned. The first choice is always try to restore
from known good backups though.
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>I have gotten the following error a few times on just one database that I
>have:
> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
> range of this database.
> This error can only be fixed by running dbcc checkdb allow_data_loss. Can
> anyone tell me what may be causing this error and what I can change or
> avoid so it stops happening?
> TIA
> jj
>|||Actually I fixed the errors with the aforementioned script but since I only
have these errors on this one db every few months I'm wondering what's going
on.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> You have corruption in the database. Unless you have backups from before
> you got the corruption you are probably going to have to loose some data
> by running CHECKDB as mentioned. The first choice is always try to
> restore from known good backups though.
> --
> Andrew J. Kelly SQL MVP
>
> "jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
> news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>|||Sounds like you have hardware problems. That is the most common cause of
corruption these days. Fixing the problem with DBCC CHECKDB is not the best
solution. You loose data each time you fix it that way. Do you know what
data you lost? Your database is no longer solid with regards to data
integrity but you haven't a clue as to where. Not a good situation to be
in.
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OLEZlDIwFHA.664@.tk2msftngp13.phx.gbl...
> Actually I fixed the errors with the aforementioned script but since I
> only have these errors on this one db every few months I'm wondering
> what's going on.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
>
I/O size
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record? Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
Farhan
I/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record? Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan
|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================
OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:
> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
>
|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:[vbcol=seagreen]
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================
> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:
I/O size
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record' Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
--
FarhanI/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record' Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================
OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:
> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
>|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:[vbcol=seagreen]
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================
> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:
>
I/O size
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record' Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
--
FarhanI/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record' Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
--
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:
> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
> >
> > Hello,
> > Following is my understanding when sql needs anythings reads from disk it
> > request to Windows which do 64 k read even though sqlserver has only
> > requested single record' Am I right?
> > how sql handle the I/O request when it reads from RAM (data cache)?
> > Thanks.
> > --
> > Farhan
>|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:
> > I/O refers to an actual read from the I/O device. So if a data page is
> > in the SQL Server Data Cache, then there is no I/O.
> >
> > So really, it is the other way 'round. During execution, data is
> > requested from the Storage Engine. The Storage Engine will check if the
> > required data page is in cache. If it is not, then the page is read from
> > disk (the I/O device) which will almost certainly cause I/O. It could
> > also trigger the storage engine into reading multiple consecutive pages
> > (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> >
> > Gert-Jan
> >
> >
> > Farhan wrote:
> > >
> > > Hello,
> > > Following is my understanding when sql needs anythings reads from disk it
> > > request to Windows which do 64 k read even though sqlserver has only
> > > requested single record' Am I right?
> > > how sql handle the I/O request when it reads from RAM (data cache)?
> > > Thanks.
> > > --
> > > Farhan
> >
I/O request taking longer than 15 seconds to complete
07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
occurrence(s) of I/O requests taking longer than 15 seconds to complete on
file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
file handle is 0x000006D4. The offset of the latest long I/O is:
0x000001e856fa00
We reviewed Microsoft support notes and other forum discussion on this which
lead us to believe this might be indicative of a hardware issue. We're
running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
extensively with HP support and ran a detailed diagnostics routine, 5
iterations, which consumed 12+ hours and interrogated all hardware components
- it came back with no problems.
We're not seeing any errors in the operating system event log. Could this
be an erroneous message within SQL? What else to do?I've seen this same problem and my suspicions are that the disk defrag is
causing the problem.
It only happens after hours when we've set the defrag to run. It happened
again early this morning on one of our servers but it probably been a
month or so since it happened before that.
Jim
On 2007-07-29, Jeffrey Howard <JeffreyHoward@.discussions.microsoft.com> wrote:
> We're periodically getting the following error message in the SQL log:
> 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> file handle is 0x000006D4. The offset of the latest long I/O is:
> 0x000001e856fa00
> We reviewed Microsoft support notes and other forum discussion on this which
> lead us to believe this might be indicative of a hardware issue. We're
> running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> extensively with HP support and ran a detailed diagnostics routine, 5
> iterations, which consumed 12+ hours and interrogated all hardware components
> - it came back with no problems.
> We're not seeing any errors in the operating system event log. Could this
> be an erroneous message within SQL? What else to do?|||What does the file stats look like? Does it indicate you have issues
reading or writing to your data or log files? What do your disk queues look
like when this happens on that drive? Are log files the only files on that
physical drive array or is it shared?
--
Andrew J. Kelly SQL MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
> We're periodically getting the following error message in the SQL log:
> 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The
> OS
> file handle is 0x000006D4. The offset of the latest long I/O is:
> 0x000001e856fa00
> We reviewed Microsoft support notes and other forum discussion on this
> which
> lead us to believe this might be indicative of a hardware issue. We're
> running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> extensively with HP support and ran a detailed diagnostics routine, 5
> iterations, which consumed 12+ hours and interrogated all hardware
> components
> - it came back with no problems.
> We're not seeing any errors in the operating system event log. Could this
> be an erroneous message within SQL? What else to do?|||> We reviewed Microsoft support notes and other forum discussion on this which
In case you have not, I'd suggest you review the notes/articles by Bob Dorr
on this subject, especially this one:
http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
Linchi
"Jeffrey Howard" wrote:
> We're periodically getting the following error message in the SQL log:
> 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> file handle is 0x000006D4. The offset of the latest long I/O is:
> 0x000001e856fa00
> We reviewed Microsoft support notes and other forum discussion on this which
> lead us to believe this might be indicative of a hardware issue. We're
> running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> extensively with HP support and ran a detailed diagnostics routine, 5
> iterations, which consumed 12+ hours and interrogated all hardware components
> - it came back with no problems.
> We're not seeing any errors in the operating system event log. Could this
> be an erroneous message within SQL? What else to do?|||Linchi:
Thank you - we reviewed the article this morning and it is not applicable to
this situation.
Jeff
"Linchi Shea" wrote:
> > We reviewed Microsoft support notes and other forum discussion on this which
> In case you have not, I'd suggest you review the notes/articles by Bob Dorr
> on this subject, especially this one:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
> Linchi
> "Jeffrey Howard" wrote:
> > We're periodically getting the following error message in the SQL log:
> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> > occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> > file handle is 0x000006D4. The offset of the latest long I/O is:
> > 0x000001e856fa00
> >
> > We reviewed Microsoft support notes and other forum discussion on this which
> > lead us to believe this might be indicative of a hardware issue. We're
> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> > extensively with HP support and ran a detailed diagnostics routine, 5
> > iterations, which consumed 12+ hours and interrogated all hardware components
> > - it came back with no problems.
> >
> > We're not seeing any errors in the operating system event log. Could this
> > be an erroneous message within SQL? What else to do?|||Jim:
Thank you. Our disk has 97% space availability. We did run defrag after
getting your feedback but unfortunately that didn't resolve the problem
because we more of these messages afterwards.
Jeff
"Jim Holcomb" wrote:
> I've seen this same problem and my suspicions are that the disk defrag is
> causing the problem.
> It only happens after hours when we've set the defrag to run. It happened
> again early this morning on one of our servers but it probably been a
> month or so since it happened before that.
> Jim
> On 2007-07-29, Jeffrey Howard <JeffreyHoward@.discussions.microsoft.com> wrote:
> > We're periodically getting the following error message in the SQL log:
> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> > occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> > file handle is 0x000006D4. The offset of the latest long I/O is:
> > 0x000001e856fa00
> >
> > We reviewed Microsoft support notes and other forum discussion on this which
> > lead us to believe this might be indicative of a hardware issue. We're
> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> > extensively with HP support and ran a detailed diagnostics routine, 5
> > iterations, which consumed 12+ hours and interrogated all hardware components
> > - it came back with no problems.
> >
> > We're not seeing any errors in the operating system event log. Could this
> > be an erroneous message within SQL? What else to do?
>|||Andrew:
Thank you. We're not seeing any other issues; this system is used very
lightly by 1-2 users during the day time, but has a number of batch processes
which run overnight. We rarely see these messages refer to daytime
operations. We've got 97% disk space available; the system's sole
responsibility is SQL; the drive where the log file and database are stored
is shared, but not that directory.
Jeff
"Andrew J. Kelly" wrote:
> What does the file stats look like? Does it indicate you have issues
> reading or writing to your data or log files? What do your disk queues look
> like when this happens on that drive? Are log files the only files on that
> physical drive array or is it shared?
> --
> Andrew J. Kelly SQL MVP
> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
> > We're periodically getting the following error message in the SQL log:
> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> > occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The
> > OS
> > file handle is 0x000006D4. The offset of the latest long I/O is:
> > 0x000001e856fa00
> >
> > We reviewed Microsoft support notes and other forum discussion on this
> > which
> > lead us to believe this might be indicative of a hardware issue. We're
> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> > extensively with HP support and ran a detailed diagnostics routine, 5
> > iterations, which consumed 12+ hours and interrogated all hardware
> > components
> > - it came back with no problems.
> >
> > We're not seeing any errors in the operating system event log. Could this
> > be an erroneous message within SQL? What else to do?
>
>|||> the drive where the log file and database are stored
> is shared, but not that directory.
Consider moving log files to another drive, preferably RAID 1 or 10.
Separating data and log files a SQL Server Best Practice because it prevents
data file I/O from interfering with the transaction log writes and also
provides more recovery options. Moving the log files to a different drive
will likely mitigate the I/O warning messages too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
> Andrew:
> Thank you. We're not seeing any other issues; this system is used very
> lightly by 1-2 users during the day time, but has a number of batch
> processes
> which run overnight. We rarely see these messages refer to daytime
> operations. We've got 97% disk space available; the system's sole
> responsibility is SQL; the drive where the log file and database are
> stored
> is shared, but not that directory.
> Jeff
>
> "Andrew J. Kelly" wrote:
>> What does the file stats look like? Does it indicate you have issues
>> reading or writing to your data or log files? What do your disk queues
>> look
>> like when this happens on that drive? Are log files the only files on
>> that
>> physical drive array or is it shared?
>> --
>> Andrew J. Kelly SQL MVP
>> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in
>> message
>> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
>> > We're periodically getting the following error message in the SQL log:
>> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
>> > occurrence(s) of I/O requests taking longer than 15 seconds to complete
>> > on
>> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7).
>> > The
>> > OS
>> > file handle is 0x000006D4. The offset of the latest long I/O is:
>> > 0x000001e856fa00
>> >
>> > We reviewed Microsoft support notes and other forum discussion on this
>> > which
>> > lead us to believe this might be indicative of a hardware issue. We're
>> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then
>> > worked
>> > extensively with HP support and ran a detailed diagnostics routine, 5
>> > iterations, which consumed 12+ hours and interrogated all hardware
>> > components
>> > - it came back with no problems.
>> >
>> > We're not seeing any errors in the operating system event log. Could
>> > this
>> > be an erroneous message within SQL? What else to do?
>>|||So I take it you are saying this happens most when the batch jobs are being
run. If so then this is a prime candidate for seeing issues like this.
Anytime you do heavy write activity in SQL Server you will always have
issues to some degree with the data and log files on the same physical disk
or disk array. If this is a single disk drive and not a disk array you can
easily get I/O contention. You should think about adding another disk or
disk array as Dan mentioned to house the log files and nothing else.
--
Andrew J. Kelly SQL MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
> Andrew:
> Thank you. We're not seeing any other issues; this system is used very
> lightly by 1-2 users during the day time, but has a number of batch
> processes
> which run overnight. We rarely see these messages refer to daytime
> operations. We've got 97% disk space available; the system's sole
> responsibility is SQL; the drive where the log file and database are
> stored
> is shared, but not that directory.
> Jeff
>
> "Andrew J. Kelly" wrote:
>> What does the file stats look like? Does it indicate you have issues
>> reading or writing to your data or log files? What do your disk queues
>> look
>> like when this happens on that drive? Are log files the only files on
>> that
>> physical drive array or is it shared?
>> --
>> Andrew J. Kelly SQL MVP
>> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in
>> message
>> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
>> > We're periodically getting the following error message in the SQL log:
>> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
>> > occurrence(s) of I/O requests taking longer than 15 seconds to complete
>> > on
>> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7).
>> > The
>> > OS
>> > file handle is 0x000006D4. The offset of the latest long I/O is:
>> > 0x000001e856fa00
>> >
>> > We reviewed Microsoft support notes and other forum discussion on this
>> > which
>> > lead us to believe this might be indicative of a hardware issue. We're
>> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then
>> > worked
>> > extensively with HP support and ran a detailed diagnostics routine, 5
>> > iterations, which consumed 12+ hours and interrogated all hardware
>> > components
>> > - it came back with no problems.
>> >
>> > We're not seeing any errors in the operating system event log. Could
>> > this
>> > be an erroneous message within SQL? What else to do?
>>|||Make sure your batch jobs are set to not overlap at all to allow most
breathing room for the server.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
> Andrew:
> Thank you. We're not seeing any other issues; this system is used very
> lightly by 1-2 users during the day time, but has a number of batch
> processes
> which run overnight. We rarely see these messages refer to daytime
> operations. We've got 97% disk space available; the system's sole
> responsibility is SQL; the drive where the log file and database are
> stored
> is shared, but not that directory.
> Jeff
>
> "Andrew J. Kelly" wrote:
>> What does the file stats look like? Does it indicate you have issues
>> reading or writing to your data or log files? What do your disk queues
>> look
>> like when this happens on that drive? Are log files the only files on
>> that
>> physical drive array or is it shared?
>> --
>> Andrew J. Kelly SQL MVP
>> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in
>> message
>> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
>> > We're periodically getting the following error message in the SQL log:
>> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
>> > occurrence(s) of I/O requests taking longer than 15 seconds to complete
>> > on
>> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7).
>> > The
>> > OS
>> > file handle is 0x000006D4. The offset of the latest long I/O is:
>> > 0x000001e856fa00
>> >
>> > We reviewed Microsoft support notes and other forum discussion on this
>> > which
>> > lead us to believe this might be indicative of a hardware issue. We're
>> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then
>> > worked
>> > extensively with HP support and ran a detailed diagnostics routine, 5
>> > iterations, which consumed 12+ hours and interrogated all hardware
>> > components
>> > - it came back with no problems.
>> >
>> > We're not seeing any errors in the operating system event log. Could
>> > this
>> > be an erroneous message within SQL? What else to do?
>>|||We moved the log file to a different RAID drive but found that while it
lessened the number of these messages considerably, it did not eliminate them.
"Dan Guzman" wrote:
> > the drive where the log file and database are stored
> > is shared, but not that directory.
> Consider moving log files to another drive, preferably RAID 1 or 10.
> Separating data and log files a SQL Server Best Practice because it prevents
> data file I/O from interfering with the transaction log writes and also
> provides more recovery options. Moving the log files to a different drive
> will likely mitigate the I/O warning messages too.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
> news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
> > Andrew:
> >
> > Thank you. We're not seeing any other issues; this system is used very
> > lightly by 1-2 users during the day time, but has a number of batch
> > processes
> > which run overnight. We rarely see these messages refer to daytime
> > operations. We've got 97% disk space available; the system's sole
> > responsibility is SQL; the drive where the log file and database are
> > stored
> > is shared, but not that directory.
> >
> > Jeff
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> What does the file stats look like? Does it indicate you have issues
> >> reading or writing to your data or log files? What do your disk queues
> >> look
> >> like when this happens on that drive? Are log files the only files on
> >> that
> >> physical drive array or is it shared?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in
> >> message
> >> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
> >> > We're periodically getting the following error message in the SQL log:
> >> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> >> > occurrence(s) of I/O requests taking longer than 15 seconds to complete
> >> > on
> >> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7).
> >> > The
> >> > OS
> >> > file handle is 0x000006D4. The offset of the latest long I/O is:
> >> > 0x000001e856fa00
> >> >
> >> > We reviewed Microsoft support notes and other forum discussion on this
> >> > which
> >> > lead us to believe this might be indicative of a hardware issue. We're
> >> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then
> >> > worked
> >> > extensively with HP support and ran a detailed diagnostics routine, 5
> >> > iterations, which consumed 12+ hours and interrogated all hardware
> >> > components
> >> > - it came back with no problems.
> >> >
> >> > We're not seeing any errors in the operating system event log. Could
> >> > this
> >> > be an erroneous message within SQL? What else to do?
> >>
> >>
> >>
>|||> We moved the log file to a different RAID drive but found that while it
> lessened the number of these messages considerably, it did not eliminate
> them.
I'm glad you're making progress. The bottom line is that you'll need to
either reduce the batch workload or increase disk I/O capacity.
How are the disks configured now (RAID and number of spindles)? Do you have
any other processes besides SQL Server that may be contributing to the I/O
workload?
Have you reviewed the batch schedule as TheSQLGuru and Andrew suggested?
Since you are still getting the warning, it appears you are attempting more
concurrent work than your I/O subsystem and server can handle. In that
case, you'll probably improve batch throughput and reduce the warning
messages by adjusting the schedule. Running SQL queries serially instead of
concurrently can also improve buffer efficiency and reduce I/O workload.
Aside from changing the batch schedule, it's possible that application
tuning or redesign can reduce I/O demand. Check I/O intensive queries to
see if there is room for improvement. Adding more disks (and perhaps RAID
changes) will increase I/O capacity but I don't recommend throwing hardware
at this problem unless you are certain it's a supply and demand issue.
Also, see
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
and monitor the disk performance counters during batch processing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:D20393B9-2554-46A1-A1FD-509624F07A5A@.microsoft.com...
> We moved the log file to a different RAID drive but found that while it
> lessened the number of these messages considerably, it did not eliminate
> them.
> "Dan Guzman" wrote:
>> > the drive where the log file and database are stored
>> > is shared, but not that directory.
>> Consider moving log files to another drive, preferably RAID 1 or 10.
>> Separating data and log files a SQL Server Best Practice because it
>> prevents
>> data file I/O from interfering with the transaction log writes and also
>> provides more recovery options. Moving the log files to a different
>> drive
>> will likely mitigate the I/O warning messages too.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in
>> message
>> news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
>> > Andrew:
>> >
>> > Thank you. We're not seeing any other issues; this system is used very
>> > lightly by 1-2 users during the day time, but has a number of batch
>> > processes
>> > which run overnight. We rarely see these messages refer to daytime
>> > operations. We've got 97% disk space available; the system's sole
>> > responsibility is SQL; the drive where the log file and database are
>> > stored
>> > is shared, but not that directory.
>> >
>> > Jeff
>> >
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> What does the file stats look like? Does it indicate you have issues
>> >> reading or writing to your data or log files? What do your disk
>> >> queues
>> >> look
>> >> like when this happens on that drive? Are log files the only files on
>> >> that
>> >> physical drive array or is it shared?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
>> >> > We're periodically getting the following error message in the SQL
>> >> > log:
>> >> > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
>> >> > occurrence(s) of I/O requests taking longer than 15 seconds to
>> >> > complete
>> >> > on
>> >> > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms]
>> >> > (7).
>> >> > The
>> >> > OS
>> >> > file handle is 0x000006D4. The offset of the latest long I/O is:
>> >> > 0x000001e856fa00
>> >> >
>> >> > We reviewed Microsoft support notes and other forum discussion on
>> >> > this
>> >> > which
>> >> > lead us to believe this might be indicative of a hardware issue.
>> >> > We're
>> >> > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then
>> >> > worked
>> >> > extensively with HP support and ran a detailed diagnostics routine,
>> >> > 5
>> >> > iterations, which consumed 12+ hours and interrogated all hardware
>> >> > components
>> >> > - it came back with no problems.
>> >> >
>> >> > We're not seeing any errors in the operating system event log.
>> >> > Could
>> >> > this
>> >> > be an erroneous message within SQL? What else to do?
>> >>
>> >>
>> >>|||Finally how did you resolve this issue?
TIA,
--
Manoj Kumar
"Jeffrey Howard" wrote:
> Linchi:
> Thank you - we reviewed the article this morning and it is not applicable to
> this situation.
> Jeff
> "Linchi Shea" wrote:
> > > We reviewed Microsoft support notes and other forum discussion on this which
> >
> > In case you have not, I'd suggest you review the notes/articles by Bob Dorr
> > on this subject, especially this one:
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
> >
> > Linchi
> >
> > "Jeffrey Howard" wrote:
> >
> > > We're periodically getting the following error message in the SQL log:
> > > 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> > > occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> > > file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> > > file handle is 0x000006D4. The offset of the latest long I/O is:
> > > 0x000001e856fa00
> > >
> > > We reviewed Microsoft support notes and other forum discussion on this which
> > > lead us to believe this might be indicative of a hardware issue. We're
> > > running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> > > extensively with HP support and ran a detailed diagnostics routine, 5
> > > iterations, which consumed 12+ hours and interrogated all hardware components
> > > - it came back with no problems.
> > >
> > > We're not seeing any errors in the operating system event log. Could this
> > > be an erroneous message within SQL? What else to do?|||Please post if the issue is resolved and what steps were taking to
resolve the issue.