We are using SQL Server 2005. In our database some rows are deleted; how can I find the system (host name / IP address), program and date & time of deletion?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You will not be able to find this information after the fact. You'll need to add a trigger and a logging table (or set up an expensive server-side trace, or add manual logging to your data access methods).
Here is a very quick example of how to implement a logging table and a trigger:
SQL Server does not store that information. If you have a backup, including transaction log files, you may be able to pinpoint when the deletion occurred, by restoring and searching for those missing records.
You can only find the hostname/IP/program provided you've logged that elsewhere. There may be traces in your eventlog, SQL log or other places, about the activity in general, but no specific logs (not to be confused with transaction log records) are stored for a record deletion.