Thursday, August 15, 2013

SQL Server: Row Count for Specific Data Page

Recently found that for one of our production server, we have to use repair_allow_data_loss parameter to resolve page corruption. But question was how many rows we can loss for a specific corrupt page ???
Rows count on a specific page can be found if we get a page header print  by DBCC Page(). Obviously, before using DBCC Page() you must enable trace (3604).

DBCC TRACEON(3604)
GO
DBCC PAGE('DatabaseName',1,323008,0)
GO

In above case DatabaseName is my database name, 1 is data file id, 323008 is page number for which we need number of rows and 0 parameter value is for print header only. According to MSDN, we have 4 options for this parameter.
  • 0 - print just the page header
  • 1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
  • 2 - page header plus whole page hex dump
  • 3 - page header plus detailed per-row interpretation
In output, under Page Header section, m_slotCnt shows number of rows on this particular page. In my case it is 736.

2 comments:

  1. This is very useful post for SQLDBA, how did you recovery loss of 736 rows? by using latest backup.

    ReplyDelete
    Replies
    1. Well, this time we were lucky as page contains only history data and data loss was quite Ok.
      BUT a corrupted page can be restored easily. Use following link to understand HOW ?
      http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/01/18/fixing-damaged-pages-using-page-restore-or-manual-inserts.aspx

      Delete

All suggestions are welcome