MS SQL Server - Torn Pages - m

Author

Torn Pages - m_tornBits value

warmtea@hotmail.com

2005-07-15, 7:23 am

I have an index that shows consistency errors with DBCC CheckDB, shown
below:

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1659152956, index ID 2: Page (1:28272) could not be
processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1659152956, index ID 2, page (1:28272). Test
(IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are
2057 and -1.
DBCC results for 'PPCLive'.
....
DBCC results for 'User'.
There are 21951 rows in 1217 pages for object 'User'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'User' (object ID 1659152956).
....
....
CHECKDB found 0 allocation errors and 2 consistency errors in database
'PPCLive'.
repair_allow_data_lo
ss is the minimum repair level for the errors found
by DBCC CHECKDB (PPCLive ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

********************
*

When I run 'DBCC Page' with the listed page ID, I get:

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

PAGE: (1:28272)
---------------

BUFFER:
-------

BUF @0x019D8400
---------------
bpage = 0x399A0000 bhash = 0x00000000 bpageno = (1:28272)
bdbid = 5 breferences = 1 bstat = 0x809
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x399A0000
----------------
m_pageId = (1:28272) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1659152956 m_indexId = 2 m_prevPage =
(1:28271)
m_nextPage = (1:210) pminlen = 7 m_slotCnt = 144
m_freeCnt = 3534 m_freeData = 6968 m_reservedCnt = 0
m_lsn = (7083:328:270) m_xactReserved = 0 m_xdesId =
(0:6679660)
m_ghostRecCnt = 14 m_tornBits = 1291845633

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:24264) = 0x48 ALLOCATED 0_PCT_FULL DIFF (1:6) =
CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:
-----

Slot 0, Offset 0x15c2
---------------------
Record Type = INDEX_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
399A15C2: 00831b36 03000400 00010000 004d001e 6.............M.
399A15D2: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.

Slot 1, Offset 0x3ac
--------------------
Record Type = INDEX_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
399A03AC: 009cd136 03000400 00010000 004d001e 6.............M.
399A03BC: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.
********************

When I query the table and use a hint to use this index page with:

select * from Users (index = idx_Users_usr_sName)

where usr_sName like 'Mc%'
order by usr_sName

I get the predictable 'I/O error (torn page) detected during read at
offset'.

If I restore a backup of this databass, DBCC checkdb shows 0
consistency errors. If I read the same page again, the details are:

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

PAGE: (1:28272)
---------------

BUFFER:
-------

BUF @0x0199A340
---------------
bpage = 0x37A9A000 bhash = 0x00000000 bpageno = (1:28272)
bdbid = 5 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x37A9A000
----------------
m_pageId = (1:28272) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1659152956 m_indexId = 2 m_prevPage =
(1:28271)
m_nextPage = (1:210) pminlen = 7 m_slotCnt = 130
m_freeCnt = 3972 m_freeData = 6968 m_reservedCnt = 0
m_lsn = (7095:161:15) m_xactReserved = 0 m_xdesId =
(0:6679660)
m_ghostRecCnt = 0 m_tornBits = 1291845634

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:24264) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) =
CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:
-----

Slot 0, Offset 0x15c2
---------------------
Record Type = INDEX_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
37A9B5C2: 00831b36 03000400 00010000 004d001e 6.............M.
37A9B5D2: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.

Slot 1, Offset 0x3ac
--------------------
Record Type = INDEX_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
37A9A3AC: 009cd136 03000400 00010000 004d001e 6.............M.
37A9A3BC: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.
******************

And the SQL query correctly returns the required rows. Notice that the
value of m_tornBits is non-zero, but different to the previous value
when the query returned a torn page error.

My intial question is: How is the value of m_tornBits used to
determine if the page is inconsistent?

I thought any non-zero value would render the page unusable and all
attempts to read the page would fail. So how do the bit map values in
here directly map to sectors of the page? Could it be that the valid
page contents are within the 'good' sectors as listed in m_tornBits and
that the remaining bits are there because subsequent writes to that
page have not gone far enough in to clear them?

I can find nothing describing the mechanics on how this value is used.

Info:

1. SQL Server 2000 SP3 running on Windows Server 2003 Standard

2. There have been no recorded power failure events on either the
source server where the backup was taken from nor the destination
server that is showing these errors. Is a failed write operation the
only cause of torn pages?

3. Running DBCC IND for the index listed the affected page in both
restored copies of the database

PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType
IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
1 28272 1 1437 1659152956 2 2 0
1 210 1 28271

4. Running DBCC Extentinfo for the index showed:
file_id page_id pg_alloc ext_size obj_id index_id
pfs_bytes
1 28272 8 8 1659152956 2
0x4040404040404040

Can anybody help me understand this one?

Kind regards

Simon

Paul S Randal [MS]

2005-07-17, 9:23 am

From SQL Server 2005 BOL entry for ALTER DATABASE:

TORN_PAGE_DETECTION

Saves a specific bit for each 512-byte sector in the 8-kilobyte (KB)
database page and stored in the database page header when the page is
written to disk. When the page is read from disk, the torn bits stored in
the page header are compared to the actual page sector information.
Unmatched values indicate that only part of the page was written to disk. In
this situation, error message 824 (indicating a torn page error) is reported
to both the SQL Server error log and the Windows event log. Torn pages are
typically detected by database recovery if it is truly an incomplete write
of a page. However, other I/O path failures can cause a torn page at any
time.

You may also find the IO whitepaper below helpful as background.

http://www.microsoft.com/technet/pr...qlIObasics.mspx

Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

<warmtea@hotmail.com> wrote in message
news:1121431463.560012.207210@g14g2000cwa.googlegroups.com...
>I have an index that shows consistency errors with DBCC CheckDB, shown
> below:
>
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1659152956, index ID 2: Page (1:28272) could not be
> processed. See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1659152956, index ID 2, page (1:28272). Test
> (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are
> 2057 and -1.
> DBCC results for 'PPCLive'.
> ...
> DBCC results for 'User'.
> There are 21951 rows in 1217 pages for object 'User'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'User' (object ID 1659152956).
> ...
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'PPCLive'.
> repair_allow_data_lo
ss is the minimum repair level for the errors found
> by DBCC CHECKDB (PPCLive ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> ********************
*
>
>
> When I run 'DBCC Page' with the listed page ID, I get:
>
>
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> PAGE: (1:28272)
> ---------------
>
> BUFFER:
> -------
>
> BUF @0x019D8400
> ---------------
> bpage = 0x399A0000 bhash = 0x00000000 bpageno = (1:28272)
> bdbid = 5 breferences = 1 bstat = 0x809
> bspin = 0 bnext = 0x00000000
>
> PAGE HEADER:
> ------------
>
> Page @0x399A0000
> ----------------
> m_pageId = (1:28272) m_headerVersion = 1 m_type = 2
> m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
> m_objId = 1659152956 m_indexId = 2 m_prevPage =
> (1:28271)
> m_nextPage = (1:210) pminlen = 7 m_slotCnt = 144
> m_freeCnt = 3534 m_freeData = 6968 m_reservedCnt = 0
> m_lsn = (7083:328:270) m_xactReserved = 0 m_xdesId =
> (0:6679660)
> m_ghostRecCnt = 14 m_tornBits = 1291845633
>
> Allocation Status
> -----------------
> GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
> PFS (1:24264) = 0x48 ALLOCATED 0_PCT_FULL DIFF (1:6) =
> CHANGED
> ML (1:7) = NOT MIN_LOGGED
>
> DATA:
> -----
>
> Slot 0, Offset 0x15c2
> ---------------------
> Record Type = INDEX_RECORD
> Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
> 399A15C2: 00831b36 03000400 00010000 004d001e 6.............M.
> 399A15D2: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.
>
> Slot 1, Offset 0x3ac
> --------------------
> Record Type = INDEX_RECORD
> Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
> 399A03AC: 009cd136 03000400 00010000 004d001e 6.............M.
> 399A03BC: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.
> ********************

>
>
>
> When I query the table and use a hint to use this index page with:
>
> select * from Users (index = idx_Users_usr_sName)

> where usr_sName like 'Mc%'
> order by usr_sName
>
> I get the predictable 'I/O error (torn page) detected during read at
> offset'.
>
> If I restore a backup of this databass, DBCC checkdb shows 0
> consistency errors. If I read the same page again, the details are:
>
>
>
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> PAGE: (1:28272)
> ---------------
>
> BUFFER:
> -------
>
> BUF @0x0199A340
> ---------------
> bpage = 0x37A9A000 bhash = 0x00000000 bpageno = (1:28272)
> bdbid = 5 breferences = 1 bstat = 0x9
> bspin = 0 bnext = 0x00000000
>
> PAGE HEADER:
> ------------
>
> Page @0x37A9A000
> ----------------
> m_pageId = (1:28272) m_headerVersion = 1 m_type = 2
> m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
> m_objId = 1659152956 m_indexId = 2 m_prevPage =
> (1:28271)
> m_nextPage = (1:210) pminlen = 7 m_slotCnt = 130
> m_freeCnt = 3972 m_freeData = 6968 m_reservedCnt = 0
> m_lsn = (7095:161:15) m_xactReserved = 0 m_xdesId =
> (0:6679660)
> m_ghostRecCnt = 0 m_tornBits = 1291845634
>
> Allocation Status
> -----------------
> GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
> PFS (1:24264) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) =
> CHANGED
> ML (1:7) = NOT MIN_LOGGED
>
> DATA:
> -----
>
> Slot 0, Offset 0x15c2
> ---------------------
> Record Type = INDEX_RECORD
> Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
> 37A9B5C2: 00831b36 03000400 00010000 004d001e 6.............M.
> 37A9B5D2: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.
>
> Slot 1, Offset 0x3ac
> --------------------
> Record Type = INDEX_RECORD
> Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
> 37A9A3AC: 009cd136 03000400 00010000 004d001e 6.............M.
> 37A9A3BC: 00440063 006e006f 006c0061 0064 c.D.o.n.a.l.d.
> ******************
>
> And the SQL query correctly returns the required rows. Notice that the
> value of m_tornBits is non-zero, but different to the previous value
> when the query returned a torn page error.
>
> My intial question is: How is the value of m_tornBits used to
> determine if the page is inconsistent?
>
> I thought any non-zero value would render the page unusable and all
> attempts to read the page would fail. So how do the bit map values in
> here directly map to sectors of the page? Could it be that the valid
> page contents are within the 'good' sectors as listed in m_tornBits and
> that the remaining bits are there because subsequent writes to that
> page have not gone far enough in to clear them?
>
> I can find nothing describing the mechanics on how this value is used.
>
> Info:
>
> 1. SQL Server 2000 SP3 running on Windows Server 2003 Standard
>
> 2. There have been no recorded power failure events on either the
> source server where the backup was taken from nor the destination
> server that is showing these errors. Is a failed write operation the
> only cause of torn pages?
>
> 3. Running DBCC IND for the index listed the affected page in both
> restored copies of the database
>
> PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType
> IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
> 1 28272 1 1437 1659152956 2 2 0
> 1 210 1 28271
>
> 4. Running DBCC Extentinfo for the index showed:
> file_id page_id pg_alloc ext_size obj_id index_id
> pfs_bytes
> 1 28272 8 8 1659152956 2
> 0x4040404040404040
>
> Can anybody help me understand this one?
>
> Kind regards
>
> Simon
>

Viracochas

2005-07-21, 7:23 am

Thanks for the information. Though useful, it did not quite answer my
question about how torn page detection works.

I would like to be in a position of understanding how the m_tornBits
value relates to the sectors and how to determine which data resides on
each sector in the page.

Or even just to know that a non-zero value for this flag does not
necessarily mean that the page is torn.

Many thanks

Simon

Paul S Randal [MS]

2005-07-24, 11:25 am

If I remember right, the m_tornBits value is the first couple of bits from
each sector, which are then replaced with a pattern which is used to detect
if some of the sectors were not written out correctly.

I don't understand your question about which data resides on each sector -
the page is layed out over 16 sequential 512-byte sectors.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Viracochas" <warmtea@hotmail.com> wrote in message
news:1121945885.636625.315730@o13g2000cwo.googlegroups.com...
> Thanks for the information. Though useful, it did not quite answer my
> question about how torn page detection works.
>
> I would like to be in a position of understanding how the m_tornBits
> value relates to the sectors and how to determine which data resides on
> each sector in the page.
>
> Or even just to know that a non-zero value for this flag does not
> necessarily mean that the page is torn.
>
> Many thanks
>
> Simon
>

Sponsored Links