November 21, 2002 at 4:49 pm
Is there any way to get SQL Profiler to run as a background service or is there another sort of background service that is like Profiler?
I need to capture all sql statements that are sent to a user database.
I know SQL Profiler can give me that but the bad news is that SQL Profiler is an application and, as such, will not capture sql statements when I log off the network.
Is there any way to get SQL Profiler to run as a background service or is there another sort of background service that is like Profiler?
Thanks in advance
Billy
November 21, 2002 at 5:22 pm
Sounds like you want a server side trace. You can perform the same profiling that you do with profiler. I regret I don't remember the syntax off hand, and it figures I am not at one of "my" machines with my scripts, but look in BOL and microsoft site for Server Side traces, and I'm sure someone will come along in a minute who knows the syntax offhand. If not, when I get home or back to my desk, I'll post you a script.
I would think there's most likely one in the scripts section of this site, as its a fairly common task.
November 22, 2002 at 4:46 am
This example is for Auditing logins and logouts
It is kind of ruff but you should be able to follow.
SQL 7
declare @P1 int
declare @P2 int
set @P1=32|64|512|1024|8192 --Column output selection list
/*
32 Microsoft Windows NT® username
64 Windows NT domain name
512 Application name
1024 SQL username
8192 Start time
*/
set @P2=0 --Default QueueHandle to 0
exec xp_trace_addnewqueue 1000, 5000, 95, 90, @P1, @P2 output, 1
select @P2 --YOu will need this output number for destroy queue otherwise you will have to shutdown server to stop trace
--Capture Events
exec xp_trace_seteventclassrequired @P2, 14, 1 --Connect
exec xp_trace_seteventclassrequired @P2, 15, 1 --Disconnect
exec xp_trace_seteventclassrequired @P2, 20, 1 --LoginFailed
--Configure the queue to write to a file.
EXEC xp_trace_setqueuedestination @P2, 2, 1, NULL, 'c:\AuditLogins.trc'
--Start the consumer that actually writes to a file.
EXEC xp_trace_startconsumer @P2
--Save the queue definition to the server.
--EXEC xp_trace_savequeuedefinition @P2, 'AuditLogins', 1
--Mark it for autostart on the server’s next restart. Use 1 for yes, 0 for no in secound parameter
--EXEC xp_trace_setqueueautostart 'AuditLogins', 1
--This is how you drop the queue
--EXEC xp_trace_destroyqueue @P2
-----------------------------------------------------------------------------------------------------------------------
SQL 2000
declare @P1 int
set @P1=0
exec sp_trace_create @P1 output, 2, N'C:\LoginAudit', NULL, NULL
select @P1 --You will need this output to be able to stop trace otherwise shutdown SQL.
--@x is a bit value, for some reason you must submit parameter 4 this way to get to work properly.
declare @x bit
set @x = 1
--Login
exec sp_trace_setevent @P1,14,11, @x
exec sp_trace_setevent @P1,14,14, @x
--Logout
exec sp_trace_setevent @P1,15,11, @x
exec sp_trace_setevent @P1,15,14, @x
--LoginFailed
exec sp_trace_setevent @P1,20,11, @x
exec sp_trace_setevent @P1,20,14, @x
--Start trace
exec sp_trace_setstatus @P1, 1
/*
--Parameter 1 is the trace id to stop and remove from queue
exec sp_trace_setstatus @P1,0
exec sp_trace_setstatus @P1,2
*/
Look at SQL BOL for the relative information of each process to get an idea of exactly what you need to change to fit your needs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply