Tracking Microsoft SQL Server Activity

Question

I have a very strange and complicated situation. I have data being erased from one of my SQL Server tables, and I am not sure by what application. I would like to be able to track this.

As I am sure you are wondering how I could find myself in this situation, here is some background. We have 2 servers, Web and Database running IIS6 and SQL Server 2005 respectively. They were setup by the previous developer who left the company without giving me any sort of introduction to the system so I am left "hunting" for everything.. I have been able to figure out most of the system on my own except for this, which remains a mystery. All I know for sure is this:

1. Data is being erased at a set time every day (I have setup a TRIGGER to capture this)
2. It is not a SQL Server Agent Job
3. It is not a Windows Scheduled Task
4. It is not a Windows Service
5. All database logins are done with the sa user so login history cannot help me... (again, I didn't set this up)

How the heck do I debug something like this? If anything, I want to know if this is coming from something running on the database server, or from a request from an outside source. Please help :-)

Answer 1

As you know the time it happens you should set up a SQL Profiler trace at that time to catch the statements being sent.

This will show you the SQL being sent, the spid of the connection, user name, application name sent by the connection and other useful info to track down the culprit.

In case the time that it happens is not convenient for you to do this you can script SQL traces (which is more lightweight than running the full GUI anyway)

Edit: Be careful when using it not to record so much information that you bog down the server. You can filter for activity on the database of interest for example.