CODEWITHSUNDEEP
sql-serversql-server-2008profilerextended-events
Randy Minder
Randy Minder

Reputation: 48392

SQL Server 2008 - Capturing all SQL Statements Hitting the Server

Can anyone suggest options we might have in capturing all SQL statements being sent to our SQL Server, outside of running Profiler? I'm aware of a couple ways of doing it, but want to make sure I'm not overlooking something, such as an existing DM view etc.

Thanks very much.

Upvotes: 9

Views: 15088

Answers (5)

Giorgi
Giorgi

Reputation: 30873

You can use Tracing to capture the output programmatically: Programmatically receiving profiler events (in real time) from SQL Server 2005

Upvotes: 1

DJA
DJA

Reputation: 659

For what its worth, the book "Inside Microsoft SQL Server 2008 T-SQL Programming" has a GREAT chapter written by Greg Low that looks at all of the logging and auditing options in SQL Server 2008. It discusses when each should be used and the pro and cons of each. Having said that, what you have done is probably best.

Upvotes: 0

Chris Diver
Chris Diver

Reputation: 19802

I think your options are

There are DMV's that collect information such as long running queries, but I don't think that there is one that will give you everything.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452947

Extended Events in SQL Server 2008. These seem fairly underused. Perhaps due to a lack of UI support but are more flexible than SQL Traces (more events and better filtering possibilities) more light weight (due to better filtering and possibility to drop events rather than block)

Example syntax is below. There are lots more events, actions, predicates and output target possibilities than that though.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test_trace')
DROP EVENT SESSION [test_trace] ON SERVER;
CREATE EVENT SESSION [test_trace]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
     ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
     )
,
ADD EVENT sqlserver.sp_statement_completed(
     ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
     )
ADD TARGET package0.asynchronous_file_target
(set filename = 'c:\temp\test_trace.xel' , metadatafile = 'c:\temp\test_trace.xem')
ALTER EVENT SESSION [test_trace] ON SERVER STATE = START

And to review the results

SELECT CONVERT (XML, event_data) AS data
        FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel',
         'C:\Temp\test_trace*.xem', NULL, NULL)

Upvotes: 13

DOK
DOK

Reputation: 32831

If your problem with Profiler isn't that you don't want to use it, but that you can't use it, perhaps you could use Profiler for Microsoft SQL Server 2005/2008 Express Edition It's free and open source.

Upvotes: 1

Related Questions