June 11, 2009 at 1:17 am
Hi All,
So here is my case , I would like to know is there any way or script so that I can monitor for all the users in a server or Database running any script and its details (When its executed , what command ran , Execution time, SPID ) . I think this can achieved by triggers any expert please give inputs .
June 11, 2009 at 3:36 am
The only way to do this in SQL 2005 is by running a trace.
Triggers won't catch select statements.
[font="Verdana"]Markus Bohse[/font]
June 11, 2009 at 5:18 am
'The only way to do this in SQL 2005 is by running a trace. '
I'm not sure, but I recall having read somewhere, that Profiler does not catch every SQL-Statements, if there is a lot and if the statement has a very short Duration(0).
But maybe I'm wrong. You could have a look at the Default trace: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/]
Greetz,
Hans Brouwer
June 11, 2009 at 5:29 am
FreeHansje (6/11/2009)
'The only way to do this in SQL 2005 is by running a trace. 'I'm not sure, but I recall having read somewhere, that Profiler does not catch every SQL-Statements, if there is a lot and if the statement has a very short Duration(0).
But maybe I'm wrong. You could have a look at the Default trace: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/]
Hans, I was talking about a server-side trace not using profiler.
The default trace only captures certain DDL statements, so I don't think it sufficient for the requirements of the OP.
[font="Verdana"]Markus Bohse[/font]
June 11, 2009 at 5:37 am
MarkusB (6/11/2009)
FreeHansje (6/11/2009)
'The only way to do this in SQL 2005 is by running a trace. 'I'm not sure, but I recall having read somewhere, that Profiler does not catch every SQL-Statements, if there is a lot and if the statement has a very short Duration(0).
But maybe I'm wrong. You could have a look at the Default trace: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/]
Hans, I was talking about a server-side trace not using profiler.
The default trace only captures certain DDL statements, so I don't think it sufficient for the requirements of the OP.
Also it does some DML statements too not so sure though ( I might be wrong), I will update it once I am home.
June 11, 2009 at 6:52 am
I'd use a server-side trace. If you're unsure how to put them together, you can use Profiler to build the scripts for you after you use the GUI to define what's needed.
Profiler and trace don't collect data in the same way. Here's some research[/url] I did on the subject. It's mainly links to the guys who know what's going on.
"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
June 11, 2009 at 10:30 am
I don't understand the distinction between profiler & server-side trace. If I go into profiler and script out the trace from the GUI, it gives me the same script that I can run in a query window as a server-side trace. So isn't profiler just a GUI version of trace ?
June 11, 2009 at 11:18 am
Nope.
This is a side-by-side test between Profiler & server-side traces by Linchi Shea. The difference is not to be sneezed at.
When the Profiler GUI runs, it attaches a memory latch to each spid to capture the information as it comes by. The information is transferred to a buffer for Profiler and then to Profiler itself. It runs as a seperate process from SQL Server. When you run a server side trace, it runs as a part of SQL Server and captures the events through internal processes. They really are different critters. It's explained in a lot of detail in this white paper from Microsoft.
"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
June 11, 2009 at 11:42 am
Thanks for the info. I should have read the earlier posts a little more closely.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply