Skip to main content

Truncate a table in SQL Server and nobody will ever see it....

Today I ran into a very strange problem. I was doing an audit because a client lost 32 billion records in a table and he wanted to know "Who did it!". They did not use auditing because it had a too large impact on performance. The server and instance was not restarted so the DMV's were still available with data. 

 I presumed there were two options how the data could have been lost and so quickly:
  1. run a TRUNCATE TABLE. Very fast and and in less than a second your table is unrecoverable empty 
  2. Switch a partition out into a temporary table and just truncate the original table. Yes it happens and the effect is the same: nothing! 
So I ran this query to collect the last queries:

SELECT
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE t.TEXT LIKE '%TRUNCATE%'
ORDER BY
s.creation_time  DESC 
GO 

No truncate. Mmm. With partition? No partition. Mmm, very strange. 

 So I created a temp table on my server and ran a truncate table command like this:

TRUNCATE TABLE [dbo].[tblToBeTruncated] 

 and the query above. No truncate. Than I changed the query in the following: 

 TRUNCATE TABLE [dbo].[tblToBeTruncated] 
GO

 and again no truncate.Then I changed the query in a format with a ; like this:

 TRUNCATE TABLE [dbo].[tblToBeTruncated];

and there was the truncate statement when I ran the query above.

Due to this bug we weren't able to find the bad guy and he got away with it. I hope the Microsoft SQL Server team will correct this ASAP because every query, even it takes a second, should be in the DMV's. 

For the bad guys: you still can get away with a truncate of a table but be aware the SQL Server team is working on it. 

PS: the test was executed on Azure SQL Database. When I tried it SQL Server 2016 the TRUNCATE command never appeared in the list with ; or without ;. Nothing. Same for SQL Server 2012.
Labels:
Location: Paris, France

Comments

Post a Comment

Popular posts from this blog

Privacy and the liberty to express yourself on LinkedIn

Unaware that LinkedIn has such a strong filtering policy that it does not allow me posting a completely innocent post on a Chinese extreme photography website I tried to post the following: "As an Mpx lover I was suprised to find out that the M from Million is now replaced by the B from Billion. This picture is 24 Bpx! Yes you read this well, 24 billion pixels.  Searching on the picture I stumbled on a fellow Nikon lover. If you want to search for him yourself you can find him here: http://www.bigpixel.cn/t/5834170785f26b37002af46a " In my eyes nothing is wrong with this post, but LinkedIn considers it as offending. I changed the lover words, but I could not post it.  Even taking a picture and post it will not let this pass:  Or my critical post on LinkedIn crazy posting policy: it will not pass and I cannot post it.  The technology LinkedIn shows here is an example what to expect in the near future.  Newspapers will have a unified reporting using
Post a Comment
Read more

How to run SQL Server 2016 with In-Databasse R on Windows 2016 CTP5

For those who like me tried to run SQL Server 2016 with In-Database R might have run into the same problem as me: In-Database R or the LaunchPad service gives a timeout and won't start. I did several clean installations with different configuration options - for instance I like to put my data on another disk than the system disk - but in the end I tried to do the next, next, next, finish install to see if it something in the setup options is hard coded in there (yes, it happens developers!). For some reason this problem is related to Windows 2016 and not on Windows 2012R2 and I hope the SQL Server team will soon resolve these issues because they are in one word a bit sloppy.  There are 2 issues (maybe even 3 so I give this one also):  The R setup does not create the ExtensibilityLog directory in the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" directory The R setup sets the number of users in the SQL Server Launchpad service to 0 it is pos
Post a Comment
Read more

Windows Server 2016 with ... XBox extensions !!!

Microsoft must have been thinking that the live of a Windows administrator gets so boring that they need a distraction and they have integrated the XBox Live extensions standard in Windows Server 2016.  No kidding: take a look. I did not select it as a feature and it is there. As you can see the XBox Live service is started automatically even in mode manual (see error log).  To my opinion these 3 XBox Live services should never be available on a server. Unless the server is part of the XBox Live platform of cause but honestly I don't think that Microsoft will allow that.  Every IT manager with a serious Windows production environment would fire any administrator playing XBox Live on the production servers. So though it might be tempting don't do it Windows administrator. It is a trap! :-)
Post a Comment
Read more