February 23, 2011 at 1:52 pm
Hello:
I have 2 other administrators for some database.
Historical records until a well known moment in time "disappeared" from some tables.
Working in full recovery mode I had scheduled transaction log backups each 3 minutes so I was able to restore transaction logs and determine the exact date and time when the records "disappeared".
I downloaded Apex SQL Log explorer and loaded the transaction log containing all the transactions before and after the previously mentioned date and time, but apparently the "truncate table" statement is not logged....
I suspect that somebody issued a "truncate table" because restoring until the specified date-time I can see all the records in the tables, but restoring to a point in time 10 seconds later, the tables are empty, and using the Apex log explorer there's no transaction involving any deletions over the tables in this 10 seconds interval...
I was not using the database when the records "disappeared" and the other 2 admins that have access to the database say that they did not delete the tables and I almost trust them... I'm even wondering if this might be a server bug as I noticed that around 3 hours before the fact, there are 12 transactions in the log that have the begin time with dates that belong to march of 2025 year... they don't show the user who issued them... and the involved tables (in the deletion) are affected in some operations ....though, after these transactions the tables were Ok till 3 hours later when they became empty...
I'm running SQL server 2005 enterprise edition SP2.
Any comments about this issue?
Regards,
Felix
February 23, 2011 at 4:01 pm
I had a developer come storming in one day, demanding to know who dropped his tables that were there last week. It so happened I had a server side trace running and captured the event. He had actually deleted them and either forgot about it, or did it by mistake ....
I don't think the default trace would capture a truncate table .. not sure though
February 23, 2011 at 7:26 pm
Not that this will necessarilly help you with your particular problem but TRUNCATE TABLE is logged but not at the same level of detail as DELETE statements.
What is logged is at the page level. What a TRUNCATE TABLE is really doing is saying to SQL Server ... "you can put all of the pages allocated to this table back in the free space list". In response to that, SQL Server immediately changes what it allocated to the table (and any associated indexes) so that the table looks empty pretty much straight away and as far as you are concerned, the TRUNCATE TABLE command is complete. At this point, the space previously allocated to the table is not available as free space. SQL Server still need to formally add all of the pages back to the free space list. This may not happen for some time depending on how large the table was. To actually return the pages back to the free space list, you will see a process call "Ghost Writer" kick - this is what actually does the work. The "Ghost Writer" may run for some time - this is because SQL Server gives priority to other processes (e.g. your SQL Queries) over this process.
February 23, 2011 at 9:43 pm
Truncate table is logged. All data modifications are logged. That said, truncate will not appear as a delete because it's not.
If only some of the records disappeared, it could not have been truncate, as that always wipes the entire table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply