Data Masking is not enough to protect personal information from prying eyes!

Data Masking is the process of either obfuscating or replacing personally identifying information with meaningless data that cannot be used to identify the items being masked. The data masking process is frequently used to alter production databases for use by the development and test teams so those teams can meaningfully debug and diagnose problems without seeing information relating to actual people, or other business-critical data.

data masking

                     IT doesn’t really mask anything!

Typically, data-masking consists of replacing the existing data with dummy data, either from a predefined dictionary of replacements, or by randomly producing replacement data. SQL Server includes a built-in mechanism for masking data, known as Dynamic Data Masking. From the Micorosoft Docs site:

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. DDM can be configured on the database to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results.

For clarity, Microsoft’s Dynamic Data Masking does NOT replace the existing data in a database, it merely masks the data prior to sending it to specific clients, based on settings applied to each affected column. Dynamic Data Masking is typically used so that non-technical employees can only see snippets of information, such as the last 4 digits of a credit card number – enabling them to do their job, without allowing them to compromise sensitive data. The information I’m presenting in this post is NOT applicable to Microsoft’s Dynamic Data Masking feature, even though the similarity of the naming of Microsoft’s Dynamic Data Masking is confusingly similar to the more commonly desired functionality discussed in this post.

Michael J. Swart’s excellent post, It’s Hard to Destroy Data, provided the impetus for me to investigate the effectiveness of the data masking used by one of my customers. I recommend using a layered approach to data security, including using:

So, now that we’ve set the stage, let’s discuss the problems you may not be aware of with traditional data masking.

Let’s create a test database where we can perform some tests.

I’ve set the recovery model to SIMPLE since we don’t care about being able to recover this database to a specific point in time. Having said that, this means we won’t be able to easily look at the contents of the log file, which under FULL recovery model may also contain personally identifying information. I’ll leave the implications of that to a future post. Suffice it to say, don’t allow your log backups to be accessible outside your production environment; i.e. don’t apply log restores to non-production databases without fully understanding the ramifications.

The BACKUP DATABASE to NUL: is required to ensure the database is actually in simple recovery model. Without that backup, the database stays in full recovery model until the first backup occurs.

Next, we’ll create a table with a clustered index for our first set of tests:

The on_row_data column contains data that will be stored in a “typical” DATA_PAGE. The off_row_data column contains data that will be stored “off-row”, in a TEXT_MIX_PAGE. These two columns allows us to see what happens to on-row-data and off-row-data when we perform various DML operations against the row.

This piece of code gathers the page numbers that have been allocated to the table so we can inspect them as part of our tests:

The pages allocated to the table:

╔════╦════════╦════════╦═══════════════╗
║ rn ║ FileID ║ PageID ║ PageTypeDesc  ║
╠════╬════════╬════════╬═══════════════╣
║  1 ║      1 ║    315 ║ IAM_PAGE      ║
║  2 ║      1 ║    336 ║ DATA_PAGE     ║
║  3 ║      1 ║    314 ║ IAM_PAGE      ║
║  4 ║      1 ║    328 ║ TEXT_MIX_PAGE ║
║  5 ║      1 ║    329 ║ TEXT_MIX_PAGE ║
╚════╩════════╩════════╩═══════════════╝

This code uses DBCC PAGE to read each page listed in the above output:

The output from the above code is a bit unwieldy, so I’ve truncated parts with no data present, but it essentially looks like:

============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 315, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:315)


BUFFER:


BUF @0x000001F8B4EB8280

bpage = 0x000001F899FE8000          bhash = 0x0000000000000000          bpageno = (1:315)
bdbid = 7                           breferences = 15                    bcputicks = 380
bsampleCount = 3                    bUse1 = 36590                       bstat = 0x10b
blog = 0x121215ac                   bnext = 0x0000000000000000          bDirtyContext = 0x000001F87AC88D10
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F899FE8000

m_pageId = (1:315)                  m_headerVersion = 1                 m_type = 10
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 137   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046906368                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 90                        m_slotCnt = 2                       m_freeCnt = 6
m_freeData = 8182                   m_reservedCnt = 0                   m_lsn = (35:116:44)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL                 DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A34BFA000

0000004A34BFA000:   010a0000 00000001 00000000 00005a00 00000000  ..............Z.....
0000004A34BFA014:   00000200 89000000 0600f61f 3b010000 01000000  ....‰.....ö.;.......
0000004A34BFA028:   23000000 74000000 2c000000 00000000 00000000  #...t...,...........
0000004A34BFA03C:   00000000 01000000 00000000 00000000 00000000  ....................
0000004A34BFA050:   00000000 00000000 00000000 00000000 00005e00  ..................^.
0000004A34BFA064:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA078:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA08C:   01000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA0A0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA0B4:   00000000 00000000 00000000 381f0000 00000004  ............8.......
0000004A34BFA0C8:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A34BFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFBFF4:   00000000 00000000 be006000                    ........¾..


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 336, 2);
Page Type: DATA_PAGE
============================================================================================
============================================================================================

PAGE: (1:336)


BUFFER:


BUF @0x000001F8B4EB7080

bpage = 0x000001F8470C6000          bhash = 0x0000000000000000          bpageno = (1:336)
bdbid = 7                           breferences = 7                     bcputicks = 0
bsampleCount = 0                    bUse1 = 36590                       bstat = 0x10b
blog = 0xdb2121cc                   bnext = 0x0000000000000000          bDirtyContext = 0x000001F87AC88CE0
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8470C6000

m_pageId = (1:336)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 137   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046906368                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8030
m_freeData = 160                    m_reservedCnt = 0                   m_lsn = (35:116:57)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A34BFA000

0000004A34BFA000:   01010000 00c00001 00000000 00000800 00000000  .....À..............
0000004A34BFA014:   00000100 89000000 5e1fa000 50010000 01000000  ....‰...^. .P.......
0000004A34BFA028:   23000000 74000000 39000000 00000000 00000000  #...t...9...........
0000004A34BFA03C:   00000000 01000000 00000000 00000000 00000000  ....................
0000004A34BFA050:   00000000 00000000 00000000 00000000 30000800  ................0...
0000004A34BFA064:   01000000 0300d802 001c0040 806f6e5f 726f775f  ......Ø....@.on_row_
0000004A34BFA078:   64617461 04000034 01000000 384e0000 681f0000  data...4....8N..h...
0000004A34BFA08C:   49010000 01000000 28230000 48010000 01000000  I.......(#..H.......
0000004A34BFA0A0:   00002121 21212121 21212121 21212121 21212121  ..!!!!!!!!!!!!!!!!!!
0000004A34BFA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A34BFA0C8:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
.
.
.
0000004A34BFBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A34BFBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)                 


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 314, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:314)


BUFFER:


BUF @0x000001F8B4EB81C0

bpage = 0x000001F899FB0000          bhash = 0x0000000000000000          bpageno = (1:314)
bdbid = 7                           breferences = 15                    bcputicks = 0
bsampleCount = 0                    bUse1 = 36590                       bstat = 0x10b
blog = 0x121215ac                   bnext = 0x0000000000000000          bDirtyContext = 0x000001F8A0900680
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F899FB0000

m_pageId = (1:314)                  m_headerVersion = 1                 m_type = 10
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 90                        m_slotCnt = 2                       m_freeCnt = 6
m_freeData = 8182                   m_reservedCnt = 0                   m_lsn = (35:116:12)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL                 DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A34BFA000

0000004A34BFA000:   010a0000 00000001 00000000 00005a00 00000000  ..............Z.....
0000004A34BFA014:   00000200 8a000000 0600f61f 3a010000 01000000  ....Š.....ö.:.......
0000004A34BFA028:   23000000 74000000 0c000000 00000000 00000000  #...t...............
0000004A34BFA03C:   00000000 01000000 00000000 00000000 00000000  ....................
0000004A34BFA050:   00000000 00000000 00000000 00000000 00005e00  ..................^.
0000004A34BFA064:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA078:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA08C:   01000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA0A0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFA0B4:   00000000 00000000 00000000 381f0000 00000002  ............8.......
0000004A34BFA0C8:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A34BFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A34BFBFF4:   00000000 00000000 be006000                    ........¾..


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 328, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:328)


BUFFER:


BUF @0x000001F8B4EB7200

bpage = 0x000001F8A5DD4000          bhash = 0x0000000000000000          bpageno = (1:328)
bdbid = 7                           breferences = 3                     bcputicks = 388
bsampleCount = 1                    bUse1 = 36590                       bstat = 0x10b
blog = 0xadb2121c                   bnext = 0x0000000000000000          bDirtyContext = 0x000001F893655FD0
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8A5DD4000

m_pageId = (1:328)                  m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 1                       m_freeCnt = 7120
m_freeData = 1070                   m_reservedCnt = 0                   m_lsn = (35:116:32)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x41 ALLOCATED  50_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A34BFA000

0000004A34BFA000:   01030000 00800001 00000000 00000000 00000000  ....................
0000004A34BFA014:   00000100 8a000000 d01b2e04 48010000 01000000  ....Š...Ð...H.......
0000004A34BFA028:   23000000 74000000 20000000 00000000 00000000  #...t... ...........
0000004A34BFA03C:   00000000 01000000 00000000 00000000 00000000  ....................
0000004A34BFA050:   00000000 00000000 00000000 00000000 0800ce03  ..................Î.
0000004A34BFA064:   0000384e 00000000 03004141 41414141 41414141  ..8N......AAAAAAAAAA
0000004A34BFA078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
.
.
.
0000004A34BFA3FC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A34BFA410:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A34BFA424:   41414141 41414141 41410000 21212121 21212121  AAAAAAAAAA..!!!!!!!!
0000004A34BFA438:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A34BFA44C:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
.
.
.
0000004A34BFBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A34BFBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 329, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:329)


BUFFER:


BUF @0x000001F8B4EB7140

bpage = 0x000001F8470AA000          bhash = 0x0000000000000000          bpageno = (1:329)
bdbid = 7                           breferences = 3                     bcputicks = 0
bsampleCount = 0                    bUse1 = 36590                       bstat = 0x10b
blog = 0xadb2121c                   bnext = 0x0000000000000000          bDirtyContext = 0x000001F8783AA500
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8470AA000

m_pageId = (1:329)                  m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 1                       m_freeCnt = 40
m_freeData = 8150                   m_reservedCnt = 0                   m_lsn = (35:116:29)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A34BFA000

0000004A34BFA000:   01030000 00800001 00000000 00000000 00000000  ....................
0000004A34BFA014:   00000100 8a000000 2800d61f 49010000 01000000  ....Š...(.Ö.I.......
0000004A34BFA028:   23000000 74000000 1d000000 00000000 00000000  #...t...............
0000004A34BFA03C:   00000000 01000000 00000000 00000000 00000000  ....................
0000004A34BFA050:   00000000 00000000 00000000 00000000 0800761f  ..................v.
0000004A34BFA064:   0000384e 00000000 03004141 41414141 41414141  ..8N......AAAAAAAAAA
0000004A34BFA078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A34BFA08C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
.
.
.
0000004A34BFBF90:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A34BFBFA4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A34BFBFB8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A34BFBFCC:   41414141 41414141 41410000 21212121 21212121  AAAAAAAAAA..!!!!!!!!
0000004A34BFBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A34BFBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The interesting bits are, page 336, where we see the on_row_data column:

0000004A34BFA064:   01000000 0300d802 001c0040 806f6e5f 726f775f  ......Ø....@.on_row_
0000004A34BFA078:   64617461 04000034 01000000 384e0000 681f0000  data...4....8N..h...

And the two pages where the off_row_data column are stored, pages 328, and 329, where you can see AAAAAAAAAAAAAAAAAAAA repeatedly.

As part of data-masking, one methodology might be to delete the rows we've identified as having PII (personally identifiable data), and potentially re-populating them with fake data. As part of this test, we'll just delete the rows using a simple DELETE FROM statement, then re-run the cursor code from above to see what the pages hold.

CHECKPOINT commits all changes to disk, ensuring the log and data files are consistent at the time CHECKPOINT runs.

The output from running the DBCC PAGE commands now look like:

============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 315, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:315)


BUFFER:


BUF @0x000001F8B4F47680

bpage = 0x000001F8476FA000          bhash = 0x0000000000000000          bpageno = (1:315)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 38563                       bstat = 0x109
blog = 0x15ac8a                     bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8476FA000

m_pageId = (1:315)                  m_headerVersion = 1                 m_type = 10
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 137   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046906368                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 90                        m_slotCnt = 2                       m_freeCnt = 6
m_freeData = 8182                   m_reservedCnt = 0                   m_lsn = (35:116:44)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 186322374              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL                 DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   010a0000 00020001 00000000 00005a00 00000000  ..............Z.....
0000004A33DFA014:   00000200 89000000 0600f61f 3b010000 01000000  ....‰.....ö.;.......
0000004A33DFA028:   23000000 74000000 2c000000 00000000 00000000  #...t...,...........
0000004A33DFA03C:   c60d1b0b 00000000 00000000 00000000 00000000  Æ...................
0000004A33DFA050:   00000000 00000000 00000000 00000000 00005e00  ..................^.
0000004A33DFA064:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA078:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA08C:   01000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA0A0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA0B4:   00000000 00000000 00000000 381f0000 00000004  ............8.......
0000004A33DFA0C8:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFCC:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 be006000                    ........¾..


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 336, 2);
Page Type: DATA_PAGE
============================================================================================
============================================================================================

PAGE: (1:336)


BUFFER:


BUF @0x000001F8B4F481C0

bpage = 0x000001F8981AC000          bhash = 0x0000000000000000          bpageno = (1:336)
bdbid = 7                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 38563                       bstat = 0x109
blog = 0x1cc8a                      bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8981AC000

m_pageId = (1:336)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 137   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046906368                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8030
m_freeData = 160                    m_reservedCnt = 0                   m_lsn = (35:144:9)
m_xactReserved = 0                  m_xdesId = (0:1077)                 m_ghostRecCnt = 1
m_tornBits = -1455191830            DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x48 ALLOCATED   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01010000 00020001 00000000 00000800 00000000  ....................
0000004A33DFA014:   00000100 89000000 5e1fa000 50010000 01000000  ....‰...^. .P.......
0000004A33DFA028:   23000000 90000000 09000000 35040000 00000100  #.......  ...5.......
0000004A33DFA03C:   ea8843a9 00000000 00000000 00000000 00000000  ê.C©................
0000004A33DFA050:   00000000 00000000 00000000 00000000 3c000800  ................< ...
0000004A33DFA064:   01000000 03001802 001c0040 806f6e5f 726f775f  ...........@.on_row_
0000004A33DFA078:   64617461 04000033 01000000 c0690000 681f0000  data...3....Ài..h...
0000004A33DFA08C:   49010000 01000000 28230000 48010000 01000000  I.......(#..H.......
0000004A33DFA0A0:   00002121 21212121 21212121 21212121 21212121  ..!!!!!!!!!!!!!!!!!!
0000004A33DFA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFA0C8:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFA0DC:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFA0F0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
.
.
.
0000004A33DFBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)                 


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 314, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:314)


BUFFER:


BUF @0x000001F8B4F47740

bpage = 0x000001F8476F8000          bhash = 0x0000000000000000          bpageno = (1:314)
bdbid = 7                           breferences = 1                     bcputicks = 344
bsampleCount = 2                    bUse1 = 38563                       bstat = 0x109
blog = 0x15ac8a                     bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8476F8000

m_pageId = (1:314)                  m_headerVersion = 1                 m_type = 10
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 90                        m_slotCnt = 2                       m_freeCnt = 6
m_freeData = 8182                   m_reservedCnt = 0                   m_lsn = (35:144:6)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 192745414              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL                 DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   010a0000 00020001 00000000 00005a00 00000000  ..............Z.....
0000004A33DFA014:   00000200 8a000000 0600f61f 3a010000 01000000  ....Š.....ö.:.......
0000004A33DFA028:   23000000 90000000 06000000 00000000 00000000  #...................
0000004A33DFA03C:   c60f7d0b 00000000 00000000 00000000 00000000  Æ.}.................
0000004A33DFA050:   00000000 00000000 00000000 00000000 00005e00  ..................^.
0000004A33DFA064:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA078:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA08C:   01000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA0A0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA0B4:   00000000 00000000 00000000 381f0000 00000000  ............8.......
0000004A33DFA0C8:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA0DC:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 be006000                    ........¾..


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 328, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:328)


BUFFER:


BUF @0x000001F8B4F48040

bpage = 0x000001F8981DC000          bhash = 0x0000000000000000          bpageno = (1:328)
bdbid = 7                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 38563                       bstat = 0x109
blog = 0x1c8a                       bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8981DC000

m_pageId = (1:328)                  m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 1                       m_freeCnt = 7120
m_freeData = 1070                   m_reservedCnt = 0                   m_lsn = (35:116:32)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 1291198232             DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x1  50_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01030000 00820001 00000000 00000000 00000000  .....‚..............
0000004A33DFA014:   00000100 8a000000 d01b2e04 48010000 01000000  ....Š...Ð...H.......
0000004A33DFA028:   23000000 74000000 20000000 00000000 00000000  #...t... ...........
0000004A33DFA03C:   181ff64c 00000000 00000000 00000000 00000000  ..öL................
0000004A33DFA050:   00000000 00000000 00000000 00000000 0800ce03  ..................Î.
0000004A33DFA064:   0000c069 00000000 03004141 41414141 41414141  ..Ài......AAAAAAAAAA
0000004A33DFA078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA08C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA

.
.
0000004A33DFA410:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA424:   41414141 41414141 41410000 21212121 21212121  AAAAAAAAAA..!!!!!!!!
0000004A33DFA438:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFA44C:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
.
.
.
0000004A33DFBFCC:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 329, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:329)


BUFFER:


BUF @0x000001F8B4F48100

bpage = 0x000001F8981EE000          bhash = 0x0000000000000000          bpageno = (1:329)
bdbid = 7                           breferences = 1                     bcputicks = 136
bsampleCount = 1                    bUse1 = 38563                       bstat = 0x109
blog = 0x1c8a                       bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8981EE000

m_pageId = (1:329)                  m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 1                       m_freeCnt = 40
m_freeData = 8150                   m_reservedCnt = 0                   m_lsn = (35:116:29)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 542446736              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x4 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01030000 00820001 00000000 00000000 00000000  .....‚..............
0000004A33DFA014:   00000100 8a000000 2800d61f 49010000 01000000  ....Š...(.Ö.I.......
0000004A33DFA028:   23000000 74000000 1d000000 00000000 00000000  #...t...............
0000004A33DFA03C:   90145520 00000000 00000000 00000000 00000000  ..U ................
0000004A33DFA050:   00000000 00000000 00000000 00000000 0800761f  ..................v.
0000004A33DFA064:   0000c069 00000000 03004141 41414141 41414141  ..Ài......AAAAAAAAAA
0000004A33DFA078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA08C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA0A0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA0B4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA0C8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA0DC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFA0F0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
.
.
.
0000004A33DFBF68:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFBF7C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFBF90:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFBFA4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFBFB8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFBFCC:   41414141 41414141 41410000 21212121 21212121  AAAAAAAAAA..!!!!!!!!
0000004A33DFBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A33DFBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

For the sake of brevity in this post, I've removed many duplicated rows in the output above, for example where the data is filled with zeros, as in 0000004A34BFA028: 00000000 00000000 01000000 00000000 00000000 .....................

If you look carefully at the output, you can see the contents of the off_row_data column still exists, and the on_row_data column contents are still clearly visible, even though SQL Server has deallocated those rows:

0000004A34BFA064:   01000000 0300d802 001c0040 806f6e5f 726f775f  ......Ø....@.on_row_
0000004A34BFA078:   64617461 04000034 01000000 384e0000 681f0000  data...4....8N..h...
0000004A34BFA08C:   49010000 01000000 28230000 48010000 01000000  I.......(#..H.......
0000004A33DFBF90:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A33DFBFA4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA

This is not so great. This same behavior occurs even if we truncate the table, instead of simply deleting rows. Per the Microsoft Docs page for TRUNCATE TABLE:

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

Perhaps the fact that the pages are not totally deallocated will make a difference?

Since we have recorded the list of pages allocated to the table, we can inspect those pages even though they are no longer associated with the table, using that same cursor with the DBCC PAGE output from above:

============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 315, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:315)


BUFFER:


BUF @0x000001F8B4EA0E40

bpage = 0x000001F8482D6000          bhash = 0x0000000000000000          bpageno = (1:315)
bdbid = 7                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 39615                       bstat = 0x109
blog = 0xb215ac8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8482D6000

m_pageId = (1:315)                  m_headerVersion = 1                 m_type = 10
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 137   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046906368                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 90                        m_slotCnt = 2                       m_freeCnt = 6
m_freeData = 8182                   m_reservedCnt = 0                   m_lsn = (35:144:3)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 192843718              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x30 IAM_PG MIXED_EXT   0_PCT_FULL                           DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A369FA000

0000004A369FA000:   010a0000 00020001 00000000 00005a00 00000000  ..............Z.....
0000004A369FA014:   00000200 89000000 0600f61f 3b010000 01000000  ....‰.....ö.;.......
0000004A369FA028:   23000000 90000000 03000000 00000000 00000000  #...................
0000004A369FA03C:   c68f7e0b 00000000 00000000 00000000 00000000  Æ.~.................
0000004A369FA050:   00000000 00000000 00000000 00000000 00005e00  ..................^.
0000004A369FA064:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA078:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA08C:   01000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA0A0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA0B4:   00000000 00000000 00000000 381f0000 00000000  ............8.......
0000004A369FA0C8:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA0DC:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A369FBFCC:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FBFF4:   00000000 00000000 be006000                    ........¾..


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 336, 2);
Page Type: DATA_PAGE
============================================================================================
============================================================================================

PAGE: (1:336)


BUFFER:


BUF @0x000001F8B4EA0300

bpage = 0x000001F84791A000          bhash = 0x0000000000000000          bpageno = (1:336)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 39616                       bstat = 0x109
blog = 0xdb21cc8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F84791A000

m_pageId = (1:336)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xc200
m_objId (AllocUnitId.idObj) = 137   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046906368                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8030
m_freeData = 160                    m_reservedCnt = 0                   m_lsn = (35:116:57)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 2117700142             DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x0   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A369FA000

0000004A369FA000:   01010000 00c20001 00000000 00000800 00000000  .....Â..............
0000004A369FA014:   00000100 89000000 5e1fa000 50010000 01000000  ....‰...^. .P.......
0000004A369FA028:   23000000 74000000 39000000 00000000 00000000  #...t...9...........
0000004A369FA03C:   2e8a397e 00000000 00000000 00000000 00000000  .Š9~................
0000004A369FA050:   00000000 00000000 00000000 00000000 30000800  ................0...
0000004A369FA064:   01000000 03009002 001c0040 806f6e5f 726f775f  ...........@.on_row_
0000004A369FA078:   64617461 04000036 01000000 d9030000 681f0000  data...6....Ù...h...
0000004A369FA08C:   49010000 01000000 28230000 48010000 01000000  I.......(#..H.......
0000004A369FA0A0:   00002121 21212121 21212121 21212121 21212121  ..!!!!!!!!!!!!!!!!!!
0000004A369FA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A369FA0C8:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
.
.
.
0000004A369FBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A369FBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)                 


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 314, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:314)


BUFFER:


BUF @0x000001F8B4EA0D80

bpage = 0x000001F8482FE000          bhash = 0x0000000000000000          bpageno = (1:314)
bdbid = 7                           breferences = 1                     bcputicks = 460
bsampleCount = 3                    bUse1 = 39615                       bstat = 0x109
blog = 0xb215ac8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8482FE000

m_pageId = (1:314)                  m_headerVersion = 1                 m_type = 10
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 90                        m_slotCnt = 2                       m_freeCnt = 6
m_freeData = 8182                   m_reservedCnt = 0                   m_lsn = (35:144:8)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 192548806              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x30 IAM_PG MIXED_EXT   0_PCT_FULL                           DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A369FA000

0000004A369FA000:   010a0000 00020001 00000000 00005a00 00000000  ..............Z.....
0000004A369FA014:   00000200 8a000000 0600f61f 3a010000 01000000  ....Š.....ö.:.......
0000004A369FA028:   23000000 90000000 08000000 00000000 00000000  #...................
0000004A369FA03C:   c60f7a0b 00000000 00000000 00000000 00000000  Æ.z.................
0000004A369FA050:   00000000 00000000 00000000 00000000 00005e00  ..................^.
0000004A369FA064:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA078:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA08C:   01000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA0A0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FA0B4:   00000000 00000000 00000000 381f0000 00000000  ............8.......
0000004A369FA0C8:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A369FBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A369FBFF4:   00000000 00000000 be006000                    ........¾..


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 328, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:328)


BUFFER:


BUF @0x000001F8B4EA0480

bpage = 0x000001F847926000          bhash = 0x0000000000000000          bpageno = (1:328)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 39616                       bstat = 0x109
blog = 0xadb21c8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F847926000

m_pageId = (1:328)                  m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 1                       m_freeCnt = 7120
m_freeData = 1070                   m_reservedCnt = 0                   m_lsn = (35:116:32)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = -856282604             DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x1  50_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A369FA000

0000004A369FA000:   01030000 00820001 00000000 00000000 00000000  .....‚..............
0000004A369FA014:   00000100 8a000000 d01b2e04 48010000 01000000  ....Š...Ð...H.......
0000004A369FA028:   23000000 74000000 20000000 00000000 00000000  #...t... ...........
0000004A369FA03C:   142af6cc 00000000 00000000 00000000 00000000  .*öÌ................
0000004A369FA050:   00000000 00000000 00000000 00000000 0800ce03  ..................Î.
0000004A369FA064:   0000d903 00000000 03004141 41414141 41414141  ..Ù.......AAAAAAAAAA
0000004A369FA078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FA08C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
.
.
.
0000004A369FA3FC:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FA410:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FA424:   41414141 41414141 41410000 21212121 21212121  AAAAAAAAAA..!!!!!!!!
0000004A369FA438:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A369FA44C:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A369FA460:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
.
.
.
0000004A369FBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A369FBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 329, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:329)


BUFFER:


BUF @0x000001F8B4EA03C0

bpage = 0x000001F847970000          bhash = 0x0000000000000000          bpageno = (1:329)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 39616                       bstat = 0x109
blog = 0xadb21c8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F847970000

m_pageId = (1:329)                  m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 138   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594046971904                                
Metadata: PartitionId = 72057594041270272                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 1                       m_freeCnt = 40
m_freeData = 8150                   m_reservedCnt = 0                   m_lsn = (35:116:29)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = -1605033572            DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x4 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A369FA000

0000004A369FA000:   01030000 00820001 00000000 00000000 00000000  .....‚..............
0000004A369FA014:   00000100 8a000000 2800d61f 49010000 01000000  ....Š...(.Ö.I.......
0000004A369FA028:   23000000 74000000 1d000000 00000000 00000000  #...t...............
0000004A369FA03C:   9c2155a0 00000000 00000000 00000000 00000000  œ!U ................
0000004A369FA050:   00000000 00000000 00000000 00000000 0800761f  ..................v.
0000004A369FA064:   0000d903 00000000 03004141 41414141 41414141  ..Ù.......AAAAAAAAAA
0000004A369FA078:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FA08C:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FA0A0:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
.
.
.
0000004A369FBFA4:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FBFB8:   41414141 41414141 41414141 41414141 41414141  AAAAAAAAAAAAAAAAAAAA
0000004A369FBFCC:   41414141 41414141 41410000 21212121 21212121  AAAAAAAAAA..!!!!!!!!
0000004A369FBFE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
0000004A369FBFF4:   21212121 21212121 21216000                    !!!!!!!!!!.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The pages still contain data!

If we run DBCC CLEANPAGE on the list of pages associated with the table after performing the TRUNCATE TABLE, we get the following output:

============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 315, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:315)


BUFFER:


BUF @0x000001F8B4EA8E80

bpage = 0x000001F8A7308000          bhash = 0x0000000000000000          bpageno = (1:315)
bdbid = 7                           breferences = 3                     bcputicks = 0
bsampleCount = 0                    bUse1 = 37968                       bstat = 0x14b
blog = 0xb215ac8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8A7308000

m_pageId = (1:315)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0     m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 0
Metadata: PartitionId = 0           Metadata: IndexId = -1              Metadata: ObjectId = 0
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 0                       m_freeCnt = 8096                    m_freeData = 96
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 0
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x30 IAM_PG MIXED_EXT   0_PCT_FULL                           DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01010000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA014:   00000000 00000000 a01f6000 3b010000 01000000  ........ ..;.......
0000004A33DFA028:   00000000 00000000 01000000 00000000 00000000  ....................
0000004A33DFA03C:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFCC:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 00000000                    ............

OFFSET TABLE:



DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 336, 2);
Page Type: DATA_PAGE
============================================================================================
============================================================================================

PAGE: (1:336)


BUFFER:


BUF @0x000001F8B4EA8340

bpage = 0x000001F8A7D1C000          bhash = 0x0000000000000000          bpageno = (1:336)
bdbid = 7                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 37968                       bstat = 0x14b
blog = 0xdb21cc8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8A7D1C000

m_pageId = (1:336)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0     m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 0
Metadata: PartitionId = 0           Metadata: IndexId = -1              Metadata: ObjectId = 0
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 0                       m_freeCnt = 8096                    m_freeData = 96
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 0
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x0   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01010000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA014:   00000000 00000000 a01f6000 50010000 01000000  ........ ..P.......
0000004A33DFA028:   00000000 00000000 01000000 00000000 00000000  ....................
0000004A33DFA03C:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA050:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 00000000                    ............

OFFSET TABLE:



DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 314, 2);
Page Type: IAM_PAGE
============================================================================================
============================================================================================

PAGE: (1:314)


BUFFER:


BUF @0x000001F8B4EA8DC0

bpage = 0x000001F8470EA000          bhash = 0x0000000000000000          bpageno = (1:314)
bdbid = 7                           breferences = 3                     bcputicks = 132
bsampleCount = 1                    bUse1 = 37968                       bstat = 0x14b
blog = 0xb215ac8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8470EA000

m_pageId = (1:314)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0     m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 0
Metadata: PartitionId = 0           Metadata: IndexId = -1              Metadata: ObjectId = 0
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 0                       m_freeCnt = 8096                    m_freeData = 96
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 0
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x30 IAM_PG MIXED_EXT   0_PCT_FULL                           DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01010000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA014:   00000000 00000000 a01f6000 3a010000 01000000  ........ ..:.......
0000004A33DFA028:   00000000 00000000 01000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFCC:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 00000000                    ............

OFFSET TABLE:



DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 328, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:328)


BUFFER:


BUF @0x000001F8B4EA84C0

bpage = 0x000001F8A7D2A000          bhash = 0x0000000000000000          bpageno = (1:328)
bdbid = 7                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 37968                       bstat = 0x14b
blog = 0xadb21c8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8A7D2A000

m_pageId = (1:328)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0     m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 0
Metadata: PartitionId = 0           Metadata: IndexId = -1              Metadata: ObjectId = 0
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 0                       m_freeCnt = 8096                    m_freeData = 96
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 0
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x1  50_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01010000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA014:   00000000 00000000 a01f6000 48010000 01000000  ........ ..H.......
0000004A33DFA028:   00000000 00000000 01000000 00000000 00000000  ....................
0000004A33DFA03C:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFCC:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 00000000                    ............

OFFSET TABLE:



DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
 
============================================================================================
============================================================================================
DBCC PAGE (cleanpage_test, 1, 329, 2);
Page Type: TEXT_MIX_PAGE
============================================================================================
============================================================================================

PAGE: (1:329)


BUFFER:


BUF @0x000001F8B4EA8400

bpage = 0x000001F8A7D1E000          bhash = 0x0000000000000000          bpageno = (1:329)
bdbid = 7                           breferences = 1                     bcputicks = 392
bsampleCount = 2                    bUse1 = 37968                       bstat = 0x14b
blog = 0xadb21c8a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000001F8A7D1E000

m_pageId = (1:329)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0     m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 0
Metadata: PartitionId = 0           Metadata: IndexId = -1              Metadata: ObjectId = 0
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 0                       m_freeCnt = 8096                    m_freeData = 96
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 0
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x4 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DATA:


Memory Dump @0x0000004A33DFA000

0000004A33DFA000:   01010000 00000000 00000000 00000000 00000000  ....................
0000004A33DFA014:   00000000 00000000 a01f6000 49010000 01000000  ........ ..I.......
0000004A33DFA028:   00000000 00000000 01000000 00000000 00000000  ....................
0000004A33DFA03C:   00000000 00000000 00000000 00000000 00000000  ....................
.
.
.
0000004A33DFBFCC:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFE0:   00000000 00000000 00000000 00000000 00000000  ....................
0000004A33DFBFF4:   00000000 00000000 00000000                    ............

OFFSET TABLE:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As you can see from the output above, both the DATA_PAGE and TEXT_MIX_PAGE pages are completely empty, which is great. If you are emptying some tables as part of your data-masking process, you should ensure the process uses TRUNCATE TABLE, not DELETE FROM. You probably want to ensure sensitive pages have been deallocated and are in fact empty by using the code above when validating your data-masking processes.

In the next post in this series, I'll use the same database and DBCC PAGE procedures shown above to inspect what happens to data when you overwrite data stored in individual columns.

Ads by Google, Paying the Rent: