August 30, 2010 at 10:20 pm
Hi All,
Someone from the development team has truncated the data from a table. Now I am out of ideas about how can I track the hostname & user who has done this act. Its very important because it can happen in future also. Does SQL server maintain any session history kind of thing ? Is there any way through which I can track it ?
Any help will be greatly appreciated. Thanks in advance 🙂
-Sujeet
August 31, 2010 at 1:09 am
What recovery mode is the database in? If its in full or Bulk you can use SQL Log Rescue:
However, I don't think you will get hostname information from that.
Is it not possible to add triggers to the table in question to record this sort of information?
August 31, 2010 at 2:24 am
Hi Stupeo,
Does this provide the same information as DBCC LOG does ? I have checked the DBCC LOG & does not contain any hostname entry. Adding a trigger will not be perfect bcoz it won't catch the truncate statement. I think............:cool:
August 31, 2010 at 2:57 am
Not sure about matching the output of DBCC Log - I'll look into it.
With regards to the trigger. I wasn't really thinking. The Truncate does not actually delete data - it just deallocates the pages which would only be visible in the transaction log.
August 31, 2010 at 5:03 am
Recovery model of the database is Full.
However as per the result shown by DBCC LOG, log file doesn't contain any entry for hostname corresponding to the entry "Truncate Table" under [Transaction Name] column.
If somebody has these third party tools like SQL_LOG_Rescue or LogExplorer, please let me know if it returns the hostname also in results.
August 31, 2010 at 5:09 am
If the hostname is not in the log, the 3rd party tools won't be able to extract it. Remember the log is not an audit trail, it's for database consistency and recovery, neither of which needs a host name.
You can try the default trace, though I don't think that truncate table will be an event caught. To track this kind of info, you typically need a trace running at the time the truncate happened.
You can narrow it down via permissions. Truncate requires DDL_Admin or ownership of the table. If this is a production database, the number of people with that level of permissions should be very small.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply