SQLServerCentral / Discuss Content Posted by Haidong Ji / Article Discussions / Article Discussions by Author / Using SQL Trace to Audit Database Access / Latest Posts

RE: Using SQL Trace to Audit Database Access

Tara-1044200 — Tue, 24 May 2011 12:14:49 GMT

I am getting the error...[quote]The trace file name is not valid because it contains a rollover file number (NNN in C:\file_NNN) while the trace rollover option is enabled.[/quote]

RE: Using SQL Trace to Audit Database Access

Shawn Melton — Fri, 26 Mar 2010 14:22:08 GMT

Attached the script, formated for those interested :-D

RE: Using SQL Trace to Audit Database Access

Walker A Impagliatelli — Tue, 09 Sep 2008 14:49:06 GMT

Too cool. :cool:Thanks.

RE: Using SQL Trace to Audit Database Access

aamiguens — Thu, 01 May 2008 12:05:31 GMT

When I stop running trace, I still cannot open trace file. It seems to keep a lock to it.

RE: Using SQL Trace to Audit Database Access

Monica-331108 — Wed, 31 May 2006 04:52:00 GMT

Hello,

I have one question. The article was very good, I made changes to the script so I could trace what I needed to. One more thing, I am trying to filter not just by application name (column 10) but also by the name of the application so I will get activity for that particular app and I am not having much luck. I am poblably missing something simple but...could you help please?

Thanks a lot

Monica

RE: Using SQL Trace to Audit Database Access

Stefan Turalski — Mon, 19 Jan 2004 00:54:00 GMT

Hi,

What you say about this trace select into your analyze table - i used

SELECT * INTO dbo.Analyze FROM :: fn_trace_gettable('\\MYHOST\SHAREDFOLDER\trace_filename.trc', default)

and after giving anyone all access to this folder that works well...

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Thu, 25 Sep 2003 18:00:00 GMT

awu,I am happy it worked out for you. Glad to be of help.Haidong

RE: Using SQL Trace to Audit Database Access

awu — Thu, 25 Sep 2003 16:28:00 GMT

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) ReturnBut 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.

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Wed, 24 Sep 2003 15:40:00 GMT

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.

RE: Using SQL Trace to Audit Database Access

awu — Wed, 24 Sep 2003 12:14:00 GMT

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.

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Tue, 23 Sep 2003 16:04:00 GMT

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

RE: Using SQL Trace to Audit Database Access

awu — Tue, 23 Sep 2003 10:50:00 GMT

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.

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Tue, 23 Sep 2003 08:26:00 GMT

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

RE: Using SQL Trace to Audit Database Access

awu — Mon, 22 Sep 2003 17:34:00 GMT

Haitong, how to make your script to do the trace automatically whenever the server is reboot ?

RE: Using SQL Trace to Audit Database Access

shoayb — Mon, 20 Jan 2003 02:53:00 GMT

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=23016http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_6koi.aspThe second one details which columns are applicable for given events.Thanks for your help and reply.

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Fri, 17 Jan 2003 16:18:00 GMT

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 solution1. 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.Haidonghttp://www.sqlservercentral.com/columnists/hji

RE: Using SQL Trace to Audit Database Access

shoayb — Fri, 17 Jan 2003 05:04:00 GMT

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 Designerand 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, @onhowever nothing is returned. I'm not an sql guru so it may be something really fundamental that I'm not understanding, can you help?

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Tue, 14 Jan 2003 09:32:00 GMT

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, 0Hope this helpsHaidonghttp://www.sqlservercentral.com/columnists/hji

RE: Using SQL Trace to Audit Database Access

deema — Tue, 14 Jan 2003 01:27:00 GMT

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)

RE: Using SQL Trace to Audit Database Access

Andy Warren — Thu, 19 Dec 2002 16:04:00 GMT

Thanks for posting the correction!Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Using SQL Trace to Audit Database Access

Haidong Ji — Thu, 19 Dec 2002 13:52:00 GMT

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 mastergo 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 endelse begin goto Error endreturnError: select 'Error starting trace.' returnGO

Using SQL Trace to Audit Database Access

Haidong Ji — Thu, 12 Dec 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/trace.asp