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.

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s