Who Truncated That Table & What Do I Do Now?

  • Divine Flame

    SSCoach

    Points: 15941

    Comments posted to this topic are about the item Who Truncated That Table & What Do I Do Now?


    Sujeet Singh

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    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

  • INCREDIBLEmouse

    SSC Eights!

    Points: 814

    I see the "myth" is busted for Full model.

    Is it still un-busted for the other models?

  • steve.bailey 29413

    SSC Rookie

    Points: 33

    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...

    http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • Robert Davis

    One Orange Chip

    Points: 28027

    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.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • erwin oosterhoorn

    SSCrazy

    Points: 2009

    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.

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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...

    http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

    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

  • Robert Davis

    One Orange Chip

    Points: 28027

    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.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Divine Flame

    SSCoach

    Points: 15941

    Thanks everyone for reading the article & for providing your valuable comments.


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    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...

    http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

    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.


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    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 :-).


    Sujeet Singh

  • Divine Flame

    SSCoach

    Points: 15941

    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


    Sujeet Singh

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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 25 total)

You must be logged in to reply to this topic. Login to reply