View all queries that ran on a particular table in Sql Server

Question

One of the tables in my db has some missing rows since the last time I made a backup of db as it shows those rows in the backup db.

I am trying to view all queries that ran against that table in last 3 days (which is the time that I last made the backup)

I would appreciate any help. Thanks.

Answer 1

This is why databases need audit tables which can be created to show who took the action. You also would have need to set up in advance a way to record the queries themselves (you can use Profiler but it takes a significant performance hit to do so and very few dbas will allow you to use it on a production database.) if you really wanted that but usually knowing who took the action (or which applications as we have many that hit our database) and when is all we need to trace down a data issue. If you haven't set up for auditing in advance, it is hard to get the answer you want. Some third party log analyzer (which will be costly) might be able to recreate the data but it still wouldn't know who did it or what the query was. And why are you doing backups only every three days?

How do you know the rows are missing (ie they should still be there) and not correctly deleted by someone because they should not be there. Just becasue there is a data difference from three days ago doesn't indicate a problem. If these are values that should not be changed by users, they why do they have the rights to change them? Perhaps your security model as welll as you back process needs to be re-evaluated.

For right now you are out of luck finding how the change was made. So use that as incentive to investigate what checks and balances you need your database to have set up so that in the future you can better see what caused a data issue or prevent one from occurring. And since you have a backup with what you say are the correct values (see paragraphy 2), then fix the data.

Answer 2

You could check to see if the query has been cached, but it's a long shot:

SELECT sql FROM sys.syscacheobjects where sql like '%TableName%'

Answer 3

Unfortunately, you had to set up the log of raw queries before. There are plenty of solutions for this on internet (via trace of profiler or others). All the logs are written permanently into LOG file of DBMS, but just for transactional IO purposes, once they are rolled to hard drive, the queries themselves leave the log. SQL Agent Error log might have some data you might want to check out. It is in object explorer -> SQL Server Agent -> Error logs