SQL Server Tracking Suspect Pages

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?

Continue reading

SQL Server Recovering Data via a Hex Editor

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.

Continue reading

TDE: Looking inside your database and backups

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.

Continue reading

A Handy function

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.