Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?
I got bored (really bored) one weekend I decided to challenge myself. I had corruption within a specific table (localised within a page) with no backups handy only an old image of the data files. The challenge being salvage data without attaching the data files.
TDE (Transparent Data Encryption) encrypts the data files at rest but don’t forget that it also encrypts your backup file too. I fancied looking inside the MDF and a backup of my database before enabling TDE and comparing it to when enabled.
If you like learning about internals use the undocumented function to get the FileID:PageID of your table rows and use that in DBCC PAGE.
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM Person.EmailAddress;
Output:
RID
(1:3392:0)
(1:3392:1)
(1:3392:2)
Now set your trace flag on and read the contents of DBCC PAGE to satisfy your curiosity.
Enjoy.
