July 11, 2005 at 7:37 pm
I have a table, and I'd like to know for every 20' who is accessing it and which sql statement they are running. For example, I'd like to know who run a select, update, delete, or insert data into or against my table. Is this can be achieved? Thanks a million in advance.
Minh Vu
July 11, 2005 at 7:48 pm
The only way to track all those is to start a trace using the profiler. This will allow you to get all that information.
The downside is that this can cause some (minute to huge) overhead on the server. But depending on the situation this is often a very acceptable cost to pay.
July 12, 2005 at 3:01 am
U didn't specify any method to do the task..
What is the way to achieve it
July 12, 2005 at 6:23 am
R u talking to me??
The only way to track all those is to start a trace using the profiler. This will allow you to get all that information.
The downside is that this can cause some (minute to huge) overhead on the server. But depending on the situation this is often a very acceptable cost to pay.
July 12, 2005 at 7:25 am
I'll piggy-back on Remi.
SQL Profiler is a tool that comes with SQL Server. If you have Enterprise Manager and Query Analyzer on your system, you likely have SQL Profiler as well. It can do traces of all database activity coming in to your SQL Server.
Also, if you're adventurous, you can use the trace stored procedures that are built into SQL Server (this is actually how Profiler works).
In Books Online you can find more information on SQL Profiler at:
Administering SQL Server | Monitoring Server Performance and Activity | Monitoring with SQL Profiler.
Here is the linke to the documentation on the MSDN site:
MSDN Library: Monitoring with SQL Profiler
K. Brian Kelley
@kbriankelley
July 13, 2005 at 3:43 am
An another way is to use SQL Server Agent to run sp_who or sp_who2 every 20'. you can collect the data then analyse it.
There is one drawback: neither sp_who nor profiler gives you the exact information about table usage. you can see the command issued in the profiler or the command type (select insert...) in sp_who.
If you want to see the exact command issued, then you have to use dbcc inputbuffer
Bye
Gabor
July 13, 2005 at 8:29 am
Unless it's a security related function or stored procedure (like sp_addlogin), Profiler will give you the exact command issued. You have to toggle SQL statement instead of SQL batch, however, as an event to monitor.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply