SQL Server DELETE! Does it permanently delete?

With General Data Protection Regulation (GDPR) coming into full force, it’s ever so more important we comply in lines with the regulations.

Therefore, when you are requested for data removal permanently by the customer or internally are you actually deleting all records of the data by using the ‘DELETE’ command.

Let’s take a look into plan cache and page memory dumps, you will be surprised what you can find! As delete doesn’t always mean everything is going to be permanently deleted.

Let’s take a look from SQL Server perspective:

Am going to create an SQL Server Database and table with few columns to store customer details:

CREATE DATABASE DELETETEST;

GO

USE DELETETEST;

GO

CREATE TABLE dbo.Customers

(

CustomerID    int IDENTITY(1,1) NOT NULL,

EmailAddress  nvarchar(320)     NOT NULL,

Firstname         nvarchar(40)    NOT NULL,

Surname           nvarchar(40)    NOT NULL,

CONSTRAINT    PK_Customers      PRIMARY KEY (CustomerID),

CONSTRAINT    UQ_CustomerEmail  UNIQUE      (EmailAddress)

);

GO

 

Now let’s insert some data into the table:

USE DELETETEST

INSERT INTO dbo.Customers(EmailAddress,Firstname,Surname) VALUES

(N’DeleteTesting@SQL.com’,N’Dharam’,N’Patel’);

GO

 

Let’s check that insert of the values has worked:

USE DELETETEST;

select * from dbo.Customers

 

Although you have interested those values, you get a request for removal of the data you had just inserted. So, you already have a delete stored procedure which removes data for this example let’s create the stored procedure:

USE DELETETEST;

CREATE PROCEDURE dbo.DeleteCust

@EmailAddress nvarchar(320)

AS

BEGIN

SET NOCOUNT ON;

DELETE dbo.Customers WHERE EmailAddress = @EmailAddress;

END

GO

You execute that stored procedure passing in the email address as a parameter value:

USE DELETETEST;

EXEC dbo.Customer_Delete @EmailAddress = N’DeleteTesting@SQL.com’;

 

Then you execute select on all rows for customer table to ensure the data has been removed:

USE DELETETEST;

select * from dbo.Customers

 

You get 0 rows, and you think the data has been permanently deleted:

Then you remember the plan for this stored procedure will be available in the plan cache, that will be associated with the first compiled parameter values from the above execution, so you check this using the query provided by Bhavesh Patel (https://www.mssqltips.com/sqlservertip/4992/how-to-find-compiled-parameter-values-for-sql-server-cached-plans/)

 

;WITH cvalue AS

(  SELECT est.text,

eqs.statement_start_offset/2 +1 as statement_start_offset,

( CASE WHEN eqs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), est.text)) * 2

ELSE eqs.statement_end_offset END – eqs.statement_start_offset

) / 2 as statement_end_offset,

TRY_CONVERT(XML,SUBSTRING(etqp.query_plan,CHARINDEX(‘<ParameterList>’,etqp.query_plan),

CHARINDEX(‘</ParameterList>’,etqp.query_plan) + LEN(‘</ParameterList>’)

– CHARINDEX(‘<ParameterList>’,etqp.query_plan) )) AS Parameters

FROM sys.dm_exec_query_stats eqs

CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est

CROSS APPLY sys.dm_exec_text_query_plan

(eqs.plan_handle, eqs.statement_start_offset, eqs.statement_end_offset) etqp

WHERE est.objectid = OBJECT_ID(N’dbo.DeleteCust‘)

AND est.dbid = db_id(N’DELETETEST’)

)

SELECT pc.compiled.value(‘@Column’, ‘nvarchar(128)’) AS Parameterlist,

pc.compiled.value(‘@ParameterCompiledValue’, ‘nvarchar(128)’) AS [compiled Value],

SUBSTRING(cvalue.text,cvalue.statement_start_offset,cvalue.statement_end_offset+1) AS sql_text

FROM cvalue

OUTER APPLY cvalue.parameters.nodes(‘//ParameterList/ColumnReference’) AS pc(compiled);

 

You get results back and surprisingly the e-mail address is still in the database in the form of a compiled parameter. So, data not being permanently removed from the system.

However, there’s Index and data pages let’s see what we can find:

Remember the TRACEON with -1 passed in as parameter would enable the trace globally. Also, the trace flag 3604 is used to redirect the output of the DBCC commands to the result window.

DBCC TRACEON (3604, -1);

DBCC IND(N’DELETETEST’, N’dbo.Customers’, 1); — clustered index

DBCC IND(N’DELETETEST’, N’dbo.Customers’, 2); — unique index

 

Take note of: PageFID, PagePID and Page Type.

Now let’s look into the data and index page:

dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

DBCC PAGE(N’DELETETEST’, 1, 144, 2);

DBCC PAGE(N’DELETETEST’, 1, 146, 2);

You find the email address within the memory dump of the index page:

Also, in the data page you find the email address:

To bring you into the scenario from a non-technical perspective, think of your electric bill, say you made one or more photocopies and stored them away in files and on your computer. If you were moving home, of course, you’d want to notify your electric provider and get your address changed to a new address, and you’d ask the electric provider to remove any old bills referencing the previous address. Alternatively, they could be automatically archived after some period. But, it came to yourself applying for some credit and an evidence of the previous residency is required. You asked the electric provider to remove old bills and yourself deleted the copy of old bills from your computer, and somewhere you have stored away your old bills or even threw them away (which you have forgotten about). At this point your thinking, what should I do? Where should I get the evidence needed? As everything has been deleted. Well, when you actually empty your computer bin you still haven’t permanently deleted everything and someone can take your hard drive and recover those files. Although you thought they are actually deleted. So, remember that your data is always stored away somewhere else than you initially think thus, it is always best to document and if you search further you would find what you thought had been deleted.

Moral of the story is you can find your data in various ways. Therefore, always act with precautions to ensure data has been completely removed from everywhere, because if the database was to be backed up and restored elsewhere then the email address could still be found within the memory dump of the index and data pages of the database elsewhere. So, always remember to document your delete processes include all the various ways the data might be stored.

The above methods are used primarily from Aaron Bertrand, to allow me to give a quick overview and a point of view from a non-technical perspective. I would recommend reading Aaron Bertrand blog further: https://www.mssqltips.com/sqlservertip/5529/data-cleanup-in-sql-server-becomes-more-important-with-gdpr/

 

Recommended readings:

https://www.mssqltips.com/sqlservertip/5529/data-cleanup-in-sql-server-becomes-more-important-with-gdpr/

https://www.red-gate.com/simple-talk/sysadmin/data-protection-and-privacy/execution-plans-data-protection/

https://www.mssqltips.com/sqlservertip/4992/how-to-find-compiled-parameter-values-for-sql-server-cached-plans/

https://www.dbrnd.com/2016/04/sql-server-dbcc-page-and-dbcc-ind-to-find-a-data-page-information/

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Neuro Brilliance Review Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Neuro Brilliance Review
Guest
Neuro Brilliance Review

Hello.This post was extremely interesting, particularly because I was
looking for thoughts on this topic last Thursday.