Deletion Logs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Deletion Logs

I may have an employee who accidentally deleted a table within a database. He won’t own up to it. Is there a log that I can look at to find evidence of this? Can the log be modified so as to remove the evidence?
You need to find out what recovery model your databases is in. If it’s in a SIMPLE recovery model, you’ll have to restore from the last full backup. If it’s in FULL/BULK_LOGGED you can restore from the last known good transaction log backup (prior to the drop event happening). Look at the "Recovery Model Overview" topic in Books Online. ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8cfea566-8f89-4581-b30d-c53f1f2c79eb.htm
tlog can’t be modified but it can be truncated so that you don’t get tlog backup… You can..If you are running sql trace on the server at that time or ddl triggers enabled…
OR
If you have tlog backup of the db… you can use third party tools like Lumigent to read the tlog backups…
http://www.lumigent.com/products/le_sql.html And also check command dbcc loginfo
http://www.databasejournal.com/features/mssql/article.php/1460161 Mohammed U.
I am not concerned with restoring at this time. I want to see what employee did it.
Let me be more specific. I was hoping this would be easy enough not to need the entire scenario. We are a software vendor that is using the customers SQL install. Two of the tables in a database have come up empty. Our product is incapable of this procedure. I need to PROVE that the customer did it. I do not have access to Enterprise Manager. So I need one of two things:
1. The name of the log to look at and hopefully the supervisor there will cooperate.
2. Or a log I might be able to access through VFP or similar product from a client.
As suggested above you can get backup of transaction log and depend upon the third party tools suggested above, also refer to the software spotlight articles in this website about usage of such tools. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
In this case, the best situation is from profiler trace. I got a question about the Tlog record, I knew the delete/truncate is logged, but I want to know whether the info about who issued the delete/truncate is logged or not? ——————
Bug explorer/finder/seeker/locator
——————
Yes with PROFILER you will get information from where that statement is issued and also it logs in Transactin log. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>