Blog Post

A DMV a Day – Day 5

,

The DMV for Day 5 is sys.dm_db_mirroring_auto_page_repair, which is described by BOL as:

Returns a row for every automatic page-repair attempt on any mirrored database on the server instance. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database. As soon as a database reaches the maximum, the row for its next automatic page-repair attempt replaces one of the existing entries.

This DMV was added in SQL Server 2008. It tells you whether you have had any automatic page repair attempts (and the results of the attempt) with SQL Server 2008 database mirroring. This query works with SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Check auto page repair history (New in SQL 2008)
SELECT DB_NAME(database_id) AS [database_name], 
database_id, file_id, page_id, error_type, page_status, modification_time
FROM sys.dm_db_mirroring_auto_page_repair; 

SQL Server 2008 added a new feature to database mirroring called Automatic Page Repair. This feature allows either side of a database mirroring session to request data from the other side of the session that can be used to repair certain 823, 824, and 829 errors asynchronously. Paul Randal (blog/twitter) does a great job of explaining it in more detail here.

I like to periodically run this query on my SQL Server instances that have any mirrored databases to see if there have been any automatic page repair attempts since the instance was last restarted. If you see any records in this query, that could be an early warning sign that you are seeing corruption issues that should be investigated further with DBCC CHECKDB.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating