shovankar (1/9/2013)
1. Is there a way that I can track this page back to a particular object in the database ?
Not from the information you've given, no. The output of CheckDB will probably identify the table involved.
2. Is there a relation between the DBCC INDEXDEFRAG and the pages getting zeroed out ?
No.
3. What will be the best possible way to run a full consistency check on databases of this size ?
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
You need to do a full checkDB of this DB or a restored backup of this DB (not a replicated copy), as you have no idea the extent or severity of the corruption.
4. What is the possible reason
Hardware problems. Something's misbehaving somewhere in the IO stack and that something has zeroed out one or more pages in the database.
5. Can rebuild index be used instead of using DBCC INDEXDEFRAG in this case ?
Maybe, but it's not the cause nor the fix for your corruption.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability