December 12, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/trace.asp
December 19, 2002 at 1:52 pm
Hi,
There is one mistake in the beginning of the scripts. Instead of DROP PROCEDURE usp_Login_Audit_Trace, the article says DROP PROCEDURE dbo.spRoleMembers. My apologies and much thanks to reader William Nabers. Got to be careful with "cut and paste"....
The correct script is at the end of this post.
In addition, there might be some compilation errors when you copy and paste the script to Query Analyzer. I think that's due to changes to formatting during the upload of the article to the web server. You might need to make some minor adjustment in order for it to work.
Thanks.
Haidong
--Use the master database
USE master
go
IF OBJECT_ID('dbo.usp_Login_Audit_Trace') IS NOT NULL
DROP PROCEDURE dbo.usp_Login_Audit_Trace
GO
CREATE proc usp_Login_Audit_Trace
@path nvarchar(128),
@duration smallint
as
/*
Author: Haidong Ji
Date: 11/08/2002
Purpose: Login audit. Try to track which logins and which databases are accessed
Set up trace to a file in the path specified in the @path parameter (use UNC path for network drive) for the number of days/hours/minutes specified in the @duration input parameter.
*/
declare @tracestarttime datetime
declare @traceident int
declare @options int
declare @filename nvarchar(245)
declare @filesize bigint
declare @tracestoptime datetime
declare @createcode int
declare @on bit
declare @startcode int
set @tracestarttime = current_timestamp
/* Set the name of the trace file. */
set @filename = cast(month(current_timestamp) as varchar) + '_' +
cast(day(current_timestamp) as varchar) + '_' +
cast(year(current_timestamp) as varchar) + '_' +
cast(datepart(hh, current_timestamp) as varchar) + '_' +
cast(datepart(mi, current_timestamp) as varchar) + '_' +
cast(datepart(ss, current_timestamp) as varchar)
set @options = 2
set @filename = @path + N'\' + @filename
set @filesize = 20
/* You can change the first parameter in the dateadd function to set how long your trace will be
For example, if it is hh, the trace will last @duration hours */
set @tracestoptime = dateadd(dd, @duration, @tracestarttime)
set @on = 1
--set up the trace
exec @createcode = sp_trace_create @traceid = @traceident output, @options = @options,
@tracefile = @filename, @maxfilesize = @filesize, @stoptime = @tracestoptime
if @createcode = 0
--trace created
begin
--set events and columns
--Trace Login event
exec sp_trace_setevent @traceident, 14, 1, @on
exec sp_trace_setevent @traceident, 14, 6, @on
exec sp_trace_setevent @traceident, 14, 7, @on
exec sp_trace_setevent @traceident, 14, 8, @on
exec sp_trace_setevent @traceident, 14, 9, @on
exec sp_trace_setevent @traceident, 14, 10, @on
exec sp_trace_setevent @traceident, 14, 11, @on
exec sp_trace_setevent @traceident, 14, 12, @on
exec sp_trace_setevent @traceident, 14, 14, @on
exec sp_trace_setevent @traceident, 14, 18, @on
exec sp_trace_setevent @traceident, 14, 34, @on
exec sp_trace_setevent @traceident, 14, 35, @on
--Trace Logout event
exec sp_trace_setevent @traceident, 15, 1, @on
exec sp_trace_setevent @traceident, 15, 6, @on
exec sp_trace_setevent @traceident, 15, 7, @on
exec sp_trace_setevent @traceident, 15, 8, @on
exec sp_trace_setevent @traceident, 15, 9, @on
exec sp_trace_setevent @traceident, 15, 10, @on
exec sp_trace_setevent @traceident, 15, 11, @on
exec sp_trace_setevent @traceident, 15, 12, @on
exec sp_trace_setevent @traceident, 15, 14, @on
exec sp_trace_setevent @traceident, 15, 18, @on
exec sp_trace_setevent @traceident, 15, 34, @on
exec sp_trace_setevent @traceident, 15, 35, @on
--Trace Audit Object GDR event
exec sp_trace_setevent @traceident, 103, 1, @on
exec sp_trace_setevent @traceident, 103, 6, @on
exec sp_trace_setevent @traceident, 103, 7, @on
exec sp_trace_setevent @traceident, 103, 8, @on
exec sp_trace_setevent @traceident, 103, 9, @on
exec sp_trace_setevent @traceident, 103, 10, @on
exec sp_trace_setevent @traceident, 103, 11, @on
exec sp_trace_setevent @traceident, 103, 12, @on
exec sp_trace_setevent @traceident, 103, 14, @on
exec sp_trace_setevent @traceident, 103, 18, @on
exec sp_trace_setevent @traceident, 103, 34, @on
exec sp_trace_setevent @traceident, 103, 35, @on
--Trace Audit Add/Drop Login event
exec sp_trace_setevent @traceident, 104, 1, @on
exec sp_trace_setevent @traceident, 104, 6, @on
exec sp_trace_setevent @traceident, 104, 7, @on
exec sp_trace_setevent @traceident, 104, 8, @on
exec sp_trace_setevent @traceident, 104, 9, @on
exec sp_trace_setevent @traceident, 104, 10, @on
exec sp_trace_setevent @traceident, 104, 11, @on
exec sp_trace_setevent @traceident, 104, 12, @on
exec sp_trace_setevent @traceident, 104, 14, @on
exec sp_trace_setevent @traceident, 104, 18, @on
exec sp_trace_setevent @traceident, 104, 34, @on
exec sp_trace_setevent @traceident, 104, 35, @on
--Trace Audit Login GDR event
exec sp_trace_setevent @traceident, 105, 1, @on
exec sp_trace_setevent @traceident, 105, 6, @on
exec sp_trace_setevent @traceident, 105, 7, @on
exec sp_trace_setevent @traceident, 105, 8, @on
exec sp_trace_setevent @traceident, 105, 9, @on
exec sp_trace_setevent @traceident, 105, 10, @on
exec sp_trace_setevent @traceident, 105, 11, @on
exec sp_trace_setevent @traceident, 105, 12, @on
exec sp_trace_setevent @traceident, 105, 14, @on
exec sp_trace_setevent @traceident, 105, 18, @on
exec sp_trace_setevent @traceident, 105, 34, @on
exec sp_trace_setevent @traceident, 105, 35, @on
--Trace Audit Login Change Property event
exec sp_trace_setevent @traceident, 106, 1, @on
exec sp_trace_setevent @traceident, 106, 6, @on
exec sp_trace_setevent @traceident, 106, 7, @on
exec sp_trace_setevent @traceident, 106, 8, @on
exec sp_trace_setevent @traceident, 106, 9, @on
exec sp_trace_setevent @traceident, 106, 10, @on
exec sp_trace_setevent @traceident, 106, 11, @on
exec sp_trace_setevent @traceident, 106, 12, @on
exec sp_trace_setevent @traceident, 106, 14, @on
exec sp_trace_setevent @traceident, 106, 18, @on
exec sp_trace_setevent @traceident, 106, 34, @on
exec sp_trace_setevent @traceident, 106, 35, @on
--Trace Audit Add Login to Server Role event
exec sp_trace_setevent @traceident, 108, 1, @on
exec sp_trace_setevent @traceident, 108, 6, @on
exec sp_trace_setevent @traceident, 108, 7, @on
exec sp_trace_setevent @traceident, 108, 8, @on
exec sp_trace_setevent @traceident, 108, 9, @on
exec sp_trace_setevent @traceident, 108, 10, @on
exec sp_trace_setevent @traceident, 108, 11, @on
exec sp_trace_setevent @traceident, 108, 12, @on
exec sp_trace_setevent @traceident, 108, 14, @on
exec sp_trace_setevent @traceident, 108, 18, @on
exec sp_trace_setevent @traceident, 108, 34, @on
exec sp_trace_setevent @traceident, 108, 35, @on
--Trace Audit Add DB User event
exec sp_trace_setevent @traceident, 109, 1, @on
exec sp_trace_setevent @traceident, 109, 6, @on
exec sp_trace_setevent @traceident, 109, 7, @on
exec sp_trace_setevent @traceident, 109, 8, @on
exec sp_trace_setevent @traceident, 109, 9, @on
exec sp_trace_setevent @traceident, 109, 10, @on
exec sp_trace_setevent @traceident, 109, 11, @on
exec sp_trace_setevent @traceident, 109, 12, @on
exec sp_trace_setevent @traceident, 109, 14, @on
exec sp_trace_setevent @traceident, 109, 18, @on
exec sp_trace_setevent @traceident, 109, 34, @on
exec sp_trace_setevent @traceident, 109, 35, @on
--Trace Audit Add Member to DB event
exec sp_trace_setevent @traceident, 110, 1, @on
exec sp_trace_setevent @traceident, 110, 6, @on
exec sp_trace_setevent @traceident, 110, 7, @on
exec sp_trace_setevent @traceident, 110, 8, @on
exec sp_trace_setevent @traceident, 110, 9, @on
exec sp_trace_setevent @traceident, 110, 10, @on
exec sp_trace_setevent @traceident, 110, 11, @on
exec sp_trace_setevent @traceident, 110, 12, @on
exec sp_trace_setevent @traceident, 110, 14, @on
exec sp_trace_setevent @traceident, 110, 18, @on
exec sp_trace_setevent @traceident, 110, 34, @on
exec sp_trace_setevent @traceident, 110, 35, @on
--Trace Audit Add/Drop Role event
exec sp_trace_setevent @traceident, 111, 1, @on
exec sp_trace_setevent @traceident, 111, 6, @on
exec sp_trace_setevent @traceident, 111, 7, @on
exec sp_trace_setevent @traceident, 111, 8, @on
exec sp_trace_setevent @traceident, 111, 9, @on
exec sp_trace_setevent @traceident, 111, 10, @on
exec sp_trace_setevent @traceident, 111, 11, @on
exec sp_trace_setevent @traceident, 111, 12, @on
exec sp_trace_setevent @traceident, 111, 14, @on
exec sp_trace_setevent @traceident, 111, 18, @on
exec sp_trace_setevent @traceident, 111, 34, @on
exec sp_trace_setevent @traceident, 111, 35, @on
--filter Profiler
exec sp_trace_setfilter @traceid = @traceident, @columid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'SQL Profiler'
--start the trace
exec @startcode = sp_trace_setstatus @traceid = @traceident, @status = 1
if @startcode = 0
begin
select 'Trace started at ' + cast(@tracestarttime as varchar) + ' for ' + cast(@duration as varchar)+
' minutes; trace id is ' + cast(@traceident as nvarchar) + '.'
end
else
begin
goto Error
end
end
else
begin
goto Error
end
return
Error:
select 'Error starting trace.'
return
GO
December 19, 2002 at 4:04 pm
Thanks for posting the correction!
Andy
January 14, 2003 at 1:27 am
I have tried to execute the listed stored procedure. the procedure does execute and the file is created but with no data in it (0 KB)
January 14, 2003 at 9:32 am
Deema,
Thanks for reading my article.
It's normal for the file size to be 0 when the trace just got started. SQL Trace does not write to this file right away. For a short trace (a couple of minutes), the size will be 0kb until the trace is over. For a long trace, the trace size will increase periodically.
To view all the running trace, use the following T-SQL:
SELECT * FROM :: fn_trace_getinfo(default)
To stop a trace, use the following T-SQL:
sp_trace_setstatus @traceId, 0
Hope this helps
Haidong
January 17, 2003 at 5:04 am
hi, we've implemented this on our sql server and it's working a treat, however, I've become greedy and also want to get the objectid of the object that is being traced (in particular DTS Designer
and SQLAgent - Job Manager, i've tried adding the following line into the stored procedure for each Trace Event block...
exec sp_trace_setevent @traceident, XX, 22, @on
however nothing is returned. I'm not an sql guru so it may be something really fundamental that I'm not understanding, can you help?
January 17, 2003 at 4:18 pm
shoayb,
I am glad you found this article helpful. In fact, you could easily edit that stored procedure and use it to trace deadlocks, do performence tuning and lots of other things.
>>exec sp_trace_setevent @traceident, XX, >>22, @on
>>however nothing is returned.
I am not too sure what you mean by "Nothing is returned"
1. If the ObjectID column is added to the trace output file but the data column is empty, then that is normal, because some columns are not applicable to certain events;
2. If the ObjectID column does not exist in the output file, that means this column was not added to the trace. There are 2 ways to resolve this:
a. You could add this column on the fly:
1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;
2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;
3. You can then issue exec sp_trace_setevent @traceid, XX, 22, @on to add this column;
4. After you are done, issue SP_trace_setstatus @traceId, 1 to restart the trace;
b. This is the permanent solution
1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;
2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;
3. Use sp_trace_setstatus @traceId, 2. This will kill the trace; NOTE: YOU MUST USE STEP 2 AND 3 TO KILL AND DELETE A TRACE. SIMPLY USING sp_trace_setstatus @traceId, 2 WILL NOT WORK;
4. Edit the your stored procedure and add the data column there and you are good to go.
Hope this helps.
Haidong
January 20, 2003 at 2:53 am
Hi Haidong,
You answered my question with...
"that is normal, because some columns are not applicable to certain events".
After I posted my question I found 2 very useul articles...
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=23016
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_6koi.asp
The second one details which columns are applicable for given events.
Thanks for your help and reply.
September 22, 2003 at 5:34 pm
Haitong, how to make your script to do the trace automatically whenever the server is reboot ?
ddfg
September 23, 2003 at 8:26 am
awu,
Assuming SQLSERVERAGENT service starts automatically, you can create a job that runs this procedure. When schedule this job, under Schedule Type, pick the "Start automatically when SQL Server Agent starts" option.
Hope this helps.
Haidong
September 23, 2003 at 10:50 am
Haidong, thank you for your reply.
I used the extended stored procedure created a couple of trace running on SQL 7, we are in the process of upgrading to SQL 2000.
In SQL 7, there is an extended stored procedure: xp_trace_setqueueautostart
which will make the trace automatically started whenever the server is reboot. But i didn't find the same thing in SQL 2000, that's why i asking the question.
I thought the way you mentioned before, it will restart the trace whenever the Agent is restart, but what happen if you only restart the Agent without restarting the SQL Server ?
Again, thank you for your help.
ddfg
September 23, 2003 at 4:04 pm
awu,
Tracing and profling have been totally redesigned for SQL 2k, that's why you couldn't see xp_trace_setqueueautostart and other extended stored procedures for tracing.
When you start SQL Agent, SQL Server Service will be started automatically, as SQL Agent service is dependent on SQL Server service.
Hope this helps.
Haidong
September 24, 2003 at 12:14 pm
No, Haidong, I don't agree with you on "When you start SQL Agent, SQL Server Service will be started automatically, as SQL Agent service is dependent on SQL Server service."
For my understanding, it’s true that when you restart SQL Server, the SQL Agent will restarted automatically, but you are able to restart Agent without restart SQL Server, so my question is, If we put a job to run the your script automatically when the Agent is start, then do we need to check if the existing trace still active, and remove it first before we create another one ? I am afraid we may create multi trace in the system.
Again, thank you for your time.
ddfg
September 24, 2003 at 3:40 pm
awu,
I suggest you DO the following:
1. Stop both SQL Server Service and SQL Server Agent service;
2. Start SQL Server Agent service. Then verify to see if SQL Server service starts automatically;
3. Again, stop both SQL Server Service and SQL Server Agent service. Now start SQL Server Service. Check SQL Agent service and see if it is started.
After you've done the above, assuming you are running Win2K or above and go to Computer Management. Then go to Services. Make sure SQL Agent service's startup type is Automatic. For WinNT, go to Control Panel, then Services.
Yes, you are able to stop and restart SQL Agent without affecting SQL Server service. I don't have time to test that scenario, but I would agree that the existing trace would still be active, if it is not timed out or stopped manually.
September 25, 2003 at 4:28 pm
Haidong,
You are right in your sense, I think we are just not in the same page for the same question, my original concern is :
if you put the sp in a job and configure it as "Start automatically when SQL Server Agent starts", then what happen if you only restart the Agent without restarting the SQL Server ? I am afraid we may create multiple trace in the system.
And you actually give me the answer by saying:
Yes, you are able to stop and restart SQL Agent without affecting SQL Server service. I don't have time to test that scenario, but I would agree that the existing trace would still be active, if it is not timed out or stopped manually.
And i test it today, it did keeping create new active trace in the system each time you stop and start Agent service, i fixed this simply put the following the first line inside the SP:
IF EXISTS (select * from ::fn_trace_getinfo(0) where property=5 and value=1) Return
But be aware of the fact, this assume you have only one active trace on the server, and it's true in my system.
Just wanna share this with you, again, thank you for your help, it's great script.
ddfg
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply