March 23, 2016 at 4:40 am
Hi,
I was wondering how can I see the latest queries on a table.
I know that if I set the recovery mode to FULL the ldf file will save the queries, but how can I select a query on them?
Thanks a lot for the help
Best Regards,
Ashkan
March 23, 2016 at 4:44 am
You can't. The LDF does not save queries, it saves modifications.
If you want to track queries, you have to set up some kind of auditing.
March 23, 2016 at 6:12 am
Hi Hugo,
Thanks a lot for the reply. I think that is enough for me. Could you please guide me how to do it?
I need to see what was the amount of something so if I can see the changes on the table, that would be great 🙂
Best Regards,
Ashkan
March 23, 2016 at 8:45 am
If you just want to track changes to a single table or a very small amount of tables, setting up a trigger to catch the changes and log them to a history table is probably the easiest way. Be aware that this will impact performance (a bit), and check the nested/recursive trigger options if you have other triggers as well.
The simplest way to implement this is to create a table that has the same columns as the original plus an "EffectiveDate" (or any other name) column and some other columns - e.g. operation (insert, update, delete), and userid or username. The primary key should be on the same column(s) as the original table plus the EffectiveDate column.
In the trigger, copy all data from inserted (in the insert and update triggers), or from deleted (in the delete trigger), plus the CURRENT_TIMESTAMP (for the effective date), and the correct values for the other added columns.
If you need a full report of all changes, or even of all queries (including SELECT), then google for "SQL Server audit" and take it from there. This is not something I have ever done myself so I cannot help with this.
March 23, 2016 at 10:00 am
To see recent queries against a table, you can search sys.dm_exec_query_stats in combination with sys.dm_exec_sql_text. It will have the queries that are currently in cache. That should cover most of what you need for a recent set of queries.
I'm with Hugo, if you want to track everything, you need to set up auditing.
Another approach is to use extended events to capture queries run on the system. That can generate a lot of data, so you need to be very specific about what you want to track and why before you just fire it up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply