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.
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:
- The full-database-encryption provided by Transparent Database Encryption,
- Backup Encryption where TDE is not required,
- Column Encryption,
- sensible data retention policies,
- Dynamic Data Masking,
- and the more traditional approach to data masking discussed in this post.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'cleanpage_test') BEGIN ALTER DATABASE cleanpage_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE cleanpage_test; END GO CREATE DATABASE cleanpage_test; ALTER DATABASE cleanpage_test SET RECOVERY SIMPLE; BACKUP DATABASE cleanpage_test TO DISK = N'NUL:'; GO USE cleanpage_test; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE dbo.clustered_index ( rownum int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED , on_row_data varchar(30) NOT NULL DEFAULT ('on_row_data') , off_row_data varchar(MAX) NOT NULL DEFAULT REPLICATE(CONVERT(nvarchar(max), N'A'), 9000) ); INSERT INTO dbo.clustered_index DEFAULT VALUES; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE @ObjectID int = (SELECT o.object_id FROM sys.objects o WHERE o.name = 'clustered_index'); DECLARE @cmd nvarchar(max); DECLARE @FileID int; DECLARE @PageID int; DECLARE @PageTypeDesc varchar(100); IF OBJECT_ID(N'tempdb..#pages', N'U') IS NOT NULL DROP TABLE #pages; CREATE TABLE #pages ( rn int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED , FileID smallint NULL , PageID int NULL , PageTypeDesc nvarchar(100) NULL ); INSERT INTO #pages (FileID, PageID, PageTypeDesc) SELECT FileID = dpa.allocated_page_file_id , PageID = dpa.allocated_page_page_id , PageTypeDesc = dpa.page_type_desc FROM sys.dm_db_database_page_allocations(DB_ID(), @ObjectID, 1, 0, 'DETAILED') dpa WHERE dpa.is_allocated = 1;</code><code> |
The pages allocated to the table:
|
1 2 |
SELECT * FROM #pages; |
╔════╦════════╦════════╦═══════════════╗ ║ 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT p.FileID , p.PageID , p.PageTypeDesc FROM #pages p ORDER BY p.rn; OPEN cur; FETCH NEXT FROM cur INTO @FileID, @PageID, @PageTypeDesc; WHILE (SELECT sc.fetch_status FROM sys.dm_exec_cursors(@@SPID) sc WHERE sc.name = 'cur') = 0 BEGIN PRINT '============================================================================================'; PRINT '============================================================================================'; PRINT 'DBCC PAGE (cleanpage_test, ' + CONVERT(varchar(max), @FileID) + N', ' + CONVERT(varchar(max), @PageID) + N', 2);'; PRINT 'Page Type: ' + @PageTypeDesc; PRINT '============================================================================================'; PRINT '============================================================================================'; DBCC PAGE (cleanpage_test, @FileID, @PageID, 2); PRINT ''; PRINT ''; FETCH NEXT FROM cur INTO @FileID, @PageID, @PageTypeDesc; END CLOSE cur; DEALLOCATE cur; |
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.
|
1 2 |
DELETE FROM dbo.clustered_index; CHECKPOINT; |
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.