June 4, 2014 at 9:34 pm
Comments posted to this topic are about the item Who Truncated That Table & What Do I Do Now?
June 5, 2014 at 3:21 am
Interesting and detailed article, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 4:01 am
I see the "myth" is busted for Full model.
Is it still un-busted for the other models?
June 5, 2014 at 4:49 am
TRUNCATE TABLE isn't a fully logged operation but as Sujeet's findings show it is logged in the transaction log (It's always logged regardless of recovery model - I'd be very concerned if it wasn't) so you can roll it back provided that you haven't committed the transaction that included the TRUNCATE TABLE statement. This doesn't mean that it logs all the rows in the table in the transaction log like the DELETE statement (if it did it wouldn't be as immediate as it is). If you do commit the transaction, the only way to recover the contents of the table is to perform a point in time restore from backup (just like Sujeet indicated). If you are using the simple model, it will still be logged and can be rolled back, you just won't be able to perform a point in time restoration of the database (unless that point in time happens to be when you performed a full backup of the database).
According to Kalen Delaney it doesn't quite fit into the minimally logged category either...
June 5, 2014 at 8:58 am
I recommend using ApexSQL's transaction log centric tools. Pretty amazing what they can do as far as redo/undo from tlog activity!! They don't have any competitor that I know of in that space. The first time you can unwind a "HOLY SH!T" event where you update all rows in a table or some such you will be very happy you have the product(s).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2014 at 9:15 am
Are they going to create newer versions of their log tool? I read that it won't be updated for SQL 2012 or 2014 because MSFT is no longer giving anyone access to their log format.
June 5, 2014 at 9:22 am
To make sure that you can find the time of truncation you have to adjust the query a bit, as you can't find the time if another action has been performed to the table.
Changing it to:
SET NOCOUNT ON;
DECLARE @ObjectName SYSNAME
SET @ObjectName = 'dbo.TestTable'
-- Your schema qualified table name here
--============== Retrieving the UserName & Time when the table was truncated, based on the TransactionID
SELECT @ObjectName AS ObjectName
, [Transaction Name]
, SUSER_SNAME([Transaction SID]) AS UserName
, [Begin Time]
, Operation
, [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName = @ObjectName)
AND [Transaction Name] LIKE 'TRUNCATE%'
AND Operation = 'LOP_BEGIN_XACT'
allows you to find the truncation, might have to have an order by if there are more than one in the lifetime of the log.
June 5, 2014 at 11:09 am
INCREDIBLEmouse (6/5/2014)
I see the "myth" is busted for Full model.Is it still un-busted for the other models?
Truncate is still a logged operation in the other models.
The myth has been long busted around this one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 11:11 am
steve.bailey 29413 (6/5/2014)
TRUNCATE TABLE isn't a fully logged operation but as Sujeet's findings show it is logged in the transaction log (It's always logged regardless of recovery model - I'd be very concerned if it wasn't) so you can roll it back provided that you haven't committed the transaction that included the TRUNCATE TABLE statement. This doesn't mean that it logs all the rows in the table in the transaction log like the DELETE statement (if it did it wouldn't be as immediate as it is). If you do commit the transaction, the only way to recover the contents of the table is to perform a point in time restore from backup (just like Sujeet indicated). If you are using the simple model, it will still be logged and can be rolled back, you just won't be able to perform a point in time restoration of the database (unless that point in time happens to be when you performed a full backup of the database).According to Kalen Delaney it doesn't quite fit into the minimally logged category either...
Way to fill in the gaps.
Glad you discussed the "fully" logged issue too. 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 11:16 am
Technically, truncate is a fully logged operation. It deallocates pages and those are logged. Every action it performs is logged. The confusion is that record deletions are not logged, but it doesn't perform record deletions. It performs deallocations.
June 5, 2014 at 11:27 am
Thanks everyone for reading the article & for providing your valuable comments.
June 5, 2014 at 11:33 am
steve.bailey 29413 (6/5/2014)
TRUNCATE TABLE isn't a fully logged operation but as Sujeet's findings show it is logged in the transaction log (It's always logged regardless of recovery model - I'd be very concerned if it wasn't) so you can roll it back provided that you haven't committed the transaction that included the TRUNCATE TABLE statement. This doesn't mean that it logs all the rows in the table in the transaction log like the DELETE statement (if it did it wouldn't be as immediate as it is). If you do commit the transaction, the only way to recover the contents of the table is to perform a point in time restore from backup (just like Sujeet indicated). If you are using the simple model, it will still be logged and can be rolled back, you just won't be able to perform a point in time restoration of the database (unless that point in time happens to be when you performed a full backup of the database).According to Kalen Delaney it doesn't quite fit into the minimally logged category either...
Thanks Steve for covering me there, unfortunately I couldn't join the discussion earlier. I would like to add just one thing that truncate itself is fully logged (for what it does). Truncate only de-allocates the pages & these de-allocations are fully logged.
June 5, 2014 at 11:34 am
erwin.oosterhoorn (6/5/2014)
To make sure that you can find the time of truncation you have to adjust the query a bit, as you can't find the time if another action has been performed to the table.Changing it to:
SET NOCOUNT ON;
DECLARE @ObjectName SYSNAME
SET @ObjectName = 'dbo.TestTable'
-- Your schema qualified table name here
--============== Retrieving the UserName & Time when the table was truncated, based on the TransactionID
SELECT @ObjectName AS ObjectName
, [Transaction Name]
, SUSER_SNAME([Transaction SID]) AS UserName
, [Begin Time]
, Operation
, [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName = @ObjectName)
AND [Transaction Name] LIKE 'TRUNCATE%'
AND Operation = 'LOP_BEGIN_XACT'
allows you to find the truncation, might have to have an order by if there are more than one in the lifetime of the log.
Thanks for pointing that out Erwin :-).
June 5, 2014 at 11:37 am
Robert Davis (6/5/2014)
Technically, truncate is a fully logged operation. It deallocates pages and those are logged. Every action it performs is logged. The confusion is that record deletions are not logged, but it doesn't perform record deletions. It performs deallocations.
+1
June 5, 2014 at 12:35 pm
Robert Davis (6/5/2014)
Are they going to create newer versions of their log tool? I read that it won't be updated for SQL 2012 or 2014 because MSFT is no longer giving anyone access to their log format.
Both tools are now showing SQL 2014 support:
http://www.apexsql.com/sql_tools_recover_features.aspx
http://www.apexsql.com/sql_tools_log_features.aspx
I don't know if they got an exception to have access to all logging technical details or not. It is possible since they are to my knowledge the ONLY player in that market - which is CRITICAL when/if you need it. They give SQL Server a recoverability level it would otherwise not have. Comes in handy in auditing/forensic scenarios too, and can even help in some data file corruption situations.
But even without the details there is no reason that smart people can't (or haven't) reverse-engineered the SQL Server tlog subsystem guts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply