Delayed durability data loss demonstration

This MSDN article, http://msdn.microsoft.com/en-gb/library/dn449490.aspx, makes it clear under the section ‘When can I lose data ? SQL Server shutdown and restart’ that a normal shutdown and restart can result in missing data. That is, client applications may receive acknowledgement that a transaction has been committed but because of the deferred log writer activity the transaction log may not have been persisted to the transaction log file. The following scripts  demonstrate the data loss that you could encounter.

First I’ll show that a database with delayed durability disabled does not suffer data loss. My testing database is named DelayedDurabilityTest. I will be inserting data into a table named MyData:

CREATE TABLE DelayedDurabilityTest.dbo.MyData
(
id BIGINT NOT NULL IDENTITY,
val VARCHAR(255) NOT NULL
)

For the first test I set delayed durability to disabled:

ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT

Now in one query window run this to start populating the table:

-- Insert script - best to run this using SQLCMD - see the very end of this postSET NOCOUNT ON
WHILE 1=1 
BEGIN
INSERT INTO DelayedDurabilityTest.dbo.MyData (val) VALUES ('Hello !!')
PRINT SCOPE_IDENTITY()
END

 

 

While this is running start a new query window and run this:

SHUTDOWN WITH NOWAIT

 

 

Switch back to the window that was running the inserts and note down the last printed identity value. For my example, this was

...16248
16249
16250
16251
16252
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

 

So in my case the last printed identity value was 16252.

Now restart the SQL Server and run

SELECT COUNT(*) FROM DelayedDurabilityTest.dbo.MyData

 

In my case this returned a value of 16253. That fact that it is one higher than the printed value in the window that was doing the inserts simply means the printing of output was lagging the actual inserting of data. The important point is that there is no data loss.

Now we truncate the table, set delayed durability to FORCED and rerun the inserting window and then the shutdown window:


TRUNCATE TABLE DelayedDurabilityTest.dbo.MyData
<pre>ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT</pre>

This time the output of the inserting window was this:


18554
18555
18556
18557
18558
18559
Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

So the last printed identity value was 18559. As was previously the case there may well be more rows in the table. The slow output of the printed messages may be under representing the actual rows. However, compare this to the row count in the table when the SQL Server was restarted. It was 18512. So in this case it’s actually lower than the last inserted value. So this has shown that with delayed durability enabled a SQL Server shutdown has resulted in data loss.

When running the scripts it may take several attempts to return a case where the row count is lower than the print message for the last row inserted. Using SQLCMD to run the insert script you’ll have more occurrences where the last printed identity is higher than the actual row count in the table when SQL Server is restarted.


SQLCMD -S localhost -i insertscript.sql


		
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: