Corrupt Pages on SQL Server

  • Article

While we were drinking caipiriñas on Brazil, the SQL PFE’s were sharing some of our experiences on clients, and one of them was corrupt pages on SQL Server.

First of all, ¿What is a page? On the SQL Server context a page is the smallest unit of allocation of data, with 8KB of size. For more information visit: https://msdn.microsoft.com/en-us/library/ms190969(v=SQL.105).aspx  (Pages and Extents)

Now, ¿Why does pages get corrupted? There's not a specific cause for this, but generally it’s because of hardware failures.

Here we have three simple steps to identify if they exist on a database:

  1. Check the SQL Server Error Log, and look for errors related to corrupt pages.

  2. Execute the following DBCC CHECKDB command.

    DBCC CHECKDB ('NombreBasedeDatos') WITH ALL_ERRORMSGS

    GO

  3. Check the table msdb.dbo.suspect_pages.

SELECT *

FROM msdb..suspect_pages

WHERE (event_type = 1 OR event_type = 2 OR event_type = 3)

Once you have identified the corrupt page, you can solve the problem with the following steps:

  1. First of all, if possible, make a full backup of your database.

  2. If the corrupt page correspond to a non-clustered index or is the intermediate page of a clustered index, rebuilding the index might solve your problem.

    DBCC CHECKDB (NombreBasedeDatos, REPAIR_REBUILD)

  3. If the corrupt page corresponds to a data page, you are going to need your last consistent backup. If you’re using SQL Server Enterprise Edition, then you can use the page or file restoration feature. As an example lets suppose the corrupt page is the page 265 from the data file 1 from the Northwind database. In this case we can use the following command to restore the page.

USE master

GO

RESTORE DATABASE Northwind

PAGE = '1:265'

FROM DISK = N'D:DBBackupNorthwindBackup.bak'

GO

 

There are a few restrictions to use this feature; in general, you can’t use page level restoration on the following pages:

  • Transaction log
  • Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.
  • Page 0 of all data files (the file boot page)
  • Page 1:9 (the database boot page)
  • Full-text catalog

For more information: https://msdn.microsoft.com/en-us/library/ms175168.aspx (Page Restoration)

As a last resort you could use the command REPAIR_ALLOW_DATA_LOSS with DBCC CHECKDB, however we don’t recommend this options, because it will allow the loss of data without assurance of the page recovery.

We’ll keep drinking the caipiriñas while talking about SQL Server. Until the next blog!

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”