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.

SQL Quiz: Solve Simple TSQL Quiz and Win an Apple iPad

a

I think most ecstatic event for me in last week was, when I received mail from Jacob Sebastian, the man behind beyondrelational.com . I found that,  comparatively, it’s a great site for developers and DBAs who are interested in different aspects of SQL Server. Beyoundrlational.com is quite different from other SQL Server sties and blogs. Other then usual articles, it has much more for you to stick on with beyondrelational.com.

One interesting part of beyondrelational.com is TSQL- Quizzes, from where not only you can learn new and different techniques but also you can win great prizes.

Everyone must try these simple but interesting quizzes. I think one of these APPLE iPad is waiting for you.

http://beyondrelational.com/quiz/SQLServer/TSQL/2011/default.aspx