January 18, 2008 at 11:03 am
Can somebody browse through the opening of this stored procedure that's being run periodically by an audit tool in SQL Profiler and advise if it would kill all other running traces? It seems to be the case that this auditing tool is killing all other traces when it starts, but I need to confirm if that makes sense based upon this script. Or is this script being selective at all about what it tries to kill? Thanks!
------------------------------------------------------
-- Drop tables, functions, stored procedures if they exist for reinit
------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_end_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_end_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_curr_rec]') AND type in (N'P', N'PC'))
drop procedure nic_aud_get_curr_rec
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_init_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_init_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_wait]') AND type in (N'P', N'PC'))
drop procedure nic_aud_wait
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_update_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_update_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_set_events]') AND type in (N'P', N'PC'))
drop procedure nic_aud_set_events
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_start_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_start_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_data]') AND type in (N'P', N'PC'))
drop procedure nic_aud_get_data
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_swap_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_swap_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_file_exists]') AND type in (N'P', N'PC'))
drop procedure nic_aud_file_exists
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_prev_idx]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function nic_aud_get_prev_idx
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_filename]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function nic_aud_get_filename
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_file_exists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function nic_aud_file_exists
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_trace]') AND type in (N'U'))
drop table nic_aud_trace
GO
January 18, 2008 at 11:15 am
No. All this is doing is dropping a bunch of tables, procedures and functions if they exist.
To stop a trace requires the sp_trace_setstatus proc to be called. (Assuming that we are talking about a SQL Profiler trace, that is.)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2008 at 11:25 am
unless you are dumping the results of the profiler trace to a table like nic_aud_trace, in which case your script would in fact "kill" that profiler session
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_trace]') AND type in (N'U'))
drop table nic_aud_trace
Just having tried it for fun - you should be getting a "failed to log activity to table xxx" in profiler if that is in fact the case. And the trace would then stop.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 12:33 pm
Thanks so much, Matt and Gail, for the responses. Matt, it just so happens that the trace results are being dumped to nic_aud_trace. Further, I do also see the sp_trace_setstatus being invoked, as Gail mentioned.
Actually, I now have access to the entire stored procedure, which even includes a description of the procedure in its comments section. This is a repetitive trace by an auditing system that constantly cycles, starting a new one and killing the last one. I sort of knew this already, but wasn't sure of the exact process it was using to do it.
Anyway, the problem that seems to be happening in this case is that this particular trace is not just killing its own previous instances, but is killing other traces started by other systems and users. I've been doing looking through the script and doing a little bit of SQL research, and it's clear that procedure is using sp_trace_setstatus to start and stop traces. However, I'm not knowledgeable enough to be able to easily deduce how the script is deciding which traces to kill.
Is this procedure keeping track of its trace ID's at all so that it doesn't step on other traces?
Or does it, as I'm suspecting, assume that it's the only game in town and issue a trace 0,0 "Stop all traces" command?
I'm including the entire procedure EXCEPT for the hundreds or thousands of lines of exec sp_trace_setevent, which I've edited out. Thanks!!!
--------------------------------
--------------------------------
------------------------------------------------------
------------------------------------------------------
-- Title: SQLServer Audit Procs
-- Version: Microsoft SQL Server 2005
-- Description: These procedures, function, and table
-- help manage collecting sql server
-- trace files.
--
-- The entry point is to call procedure "nic_aud_swap_trace"
-- which will stop the existing trace (if one is running),
-- start a new one, and retrieve the results of the old trace file (if it exists).
-- In order to not lose events, the new trace is started
-- before the old one ends. Since there could be duplicate
-- events in these 2 trace files because of the time overlap,
-- procedure "nic_aud_get_data" attempts to remove that
-- duplication. This procedure is called at the end of
-- "nic_aud_swap_trace". So calling "nic_aud_swap_trace"
-- will effectively swap trace files and get the latest
-- trace data removing any duplication.
--
-- The input to "nic_aud_swap_trace" is as follows:
-- @maxidx int = the maximum round robin index to put on
-- the filenames before starting over
-- (e.g., tracefile-1.trc, tracefile-2.trc, etc.)
-- @file_template nvarchar(255) = the template name for all trace files (e.g., 'C:\Trace\tracefile')
-- @overwrite int = (1=true, 0=false) overwrite any old trace files
-- with same name (which could likely happen w/ round robin)
--
-- Notes:
--
-- MaxfileSize is hard-coded to 100MB and length of trace is set to one day so that if
-- for some reason, the collector goes down and does not make the call to switch
-- trace files, the trace will stop after one day.
--
-- Also, use procedure "nic_aud_init_trace" to start over. This will stop any existing traces
-- stored in table "nic_aud_trace" and then will delete the rows from this table.
--
-- Make sure to manually delete or move old trace files after restarting if
-- they use the same template filename as a new trace to avoid having them picked up by
-- the de-duplication logic.
--
-- More than one collector can run traces as long as they use different file template patterns.
-- If the same pattern is used, the two collector processes will step on each others trace files
-- or hit errors trying to access a trace file locked by the other.
------------------------------------------------------
------------------------------------------------------
------------------------------------------------------
-- Drop tables, functions, stored procedures if they exist for reinit
------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_end_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_end_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_curr_rec]') AND type in (N'P', N'PC'))
drop procedure nic_aud_get_curr_rec
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_init_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_init_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_wait]') AND type in (N'P', N'PC'))
drop procedure nic_aud_wait
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_update_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_update_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_set_events]') AND type in (N'P', N'PC'))
drop procedure nic_aud_set_events
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_start_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_start_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_data]') AND type in (N'P', N'PC'))
drop procedure nic_aud_get_data
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_swap_trace]') AND type in (N'P', N'PC'))
drop procedure nic_aud_swap_trace
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_file_exists]') AND type in (N'P', N'PC'))
drop procedure nic_aud_file_exists
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_prev_idx]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function nic_aud_get_prev_idx
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_filename]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function nic_aud_get_filename
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_file_exists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function nic_aud_file_exists
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_trace]') AND type in (N'U'))
drop table nic_aud_trace
GO
------------------------------------------------------
-- Table to store the currently running trace
-- (Only one record should ever exist in this table.)
------------------------------------------------------
create table nic_aud_trace
( trace_file_template nvarchar(255) primary key,
curr_trace_id int,
curr_idx int,
endtime datetime,
updated datetime
)
go
------------------------------------------------------
-- ends existing trace
------------------------------------------------------
create procedure nic_aud_end_trace
( @traceid int )
as
begin
declare @val int
select @val = convert(int, value)
from sys.fn_trace_getinfo(@traceid)
where property = 5
if @val is not null
begin
exec sp_trace_setstatus @traceid, 0
exec sp_trace_setstatus @traceid, 2
end
end
go
------------------------------------------------------
-- gets current trace information
------------------------------------------------------
create procedure nic_aud_get_curr_rec
( @file_template nvarchar(255), @idx int output, @traceid int output )
as
begin
select @idx = curr_idx,
@traceid = curr_trace_id
from nic_aud_trace
where trace_file_template = @file_template
end
go
------------------------------------------------------
-- init trace functionality (could be put in as startup proc in sql server)
------------------------------------------------------
create procedure nic_aud_init_trace
as
begin
declare @traceid int
declare get_traces cursor for
select curr_trace_id from nic_aud_trace
open get_traces
-- see if any existing trace is running
fetch next from get_traces
into @traceid
while @@FETCH_STATUS = 0
begin
exec dbo.nic_aud_end_trace @traceid
fetch next from get_traces
into @traceid
end
close get_traces
deallocate get_traces
-- delete all
delete from nic_aud_trace
end
go
------------------------------------------------------
-- help proc to wait N number of seconds.
------------------------------------------------------
create procedure nic_aud_wait ( @sec int )
as
begin
declare @t char(8)
set @t = convert(char(8),dateadd(second,@sec,0),8)
waitfor delay @t
end
go
------------------------------------------------------
-- updates current trace information
------------------------------------------------------
create procedure nic_aud_update_trace
( @file_template nvarchar(255),
@traceid int,
@idx int,
@endtime datetime )
as
begin
declare @test_traceid int
select @test_traceid = curr_trace_id
from nic_aud_trace
where trace_file_template = @file_template
if @test_traceid is null
begin
insert into nic_aud_trace
( trace_file_template,
curr_trace_id,
curr_idx,
endtime,
updated
)
values ( @file_template, @traceid, @idx, @endtime, getdate() )
end
else
begin
update nic_aud_trace
set curr_trace_id = @traceid,
curr_idx = @idx,
endtime = @endtime,
updated = getdate()
where trace_file_template = @file_template
end
end
go
------------------------------------------------------
-- Gets the previous idx based on current one and max
------------------------------------------------------
create function nic_aud_get_prev_idx
( @maxidx int,
@curridx int
)
returns int
as
begin
declare @prev int
if @curridx <= 1
begin
set @prev = @maxidx
end
else
begin
set @prev = @curridx - 1
end
return @prev
end
go
------------------------------------------------------
-- Constructs filename based on input
------------------------------------------------------
create function nic_aud_get_filename
( @fileprefix nvarchar(255),
@curridx int,
@suffix nvarchar(3)
)
returns nvarchar(255)
as
begin
declare @filename nvarchar(255)
set @filename = @fileprefix+'-'+convert(nvarchar, @curridx)
if @suffix is not null
begin
set @filename = @filename+'.'+@suffix
end
return @filename
end
go
------------------------------------------------------
-- File exists procedure
------------------------------------------------------
create procedure nic_aud_file_exists
( @filename nvarchar(255), @rst int output)
as
begin
declare@objFSys int
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @rst out, @filename
exec sp_OADestroy @objFSys
end
go
------------------------------------------------------
-- Customer specific events should be put into this procedure
------------------------------------------------------
create procedure nic_aud_set_events
( @traceid int )
as
begin
-- Set the events
declare @on bit
set @on = 1
-- *************
-- *** Custom events should be pasted below this line!!!
-- *************
-- *************
-- *** Default Events for SQL Server 2005 documented here:
-- *** http://msdn2.microsoft.com/en-us/library/ms186265.aspx
-- *************
-- *************
-- *** Event ID 10 - RPC:Completed
-- *** Occurs when a remote procedure call (RPC) has completed.
-- *************
exec sp_trace_setevent @traceid, 10, 1, @on
...
...
...
exec sp_trace_setevent @traceid, 202, 44, @on
-- *************
-- *** Custom events should be pasted above this line!!!
-- *************
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- *************
-- *** Custom filters should be pasted below this line!!!
-- *************
exec sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
-- *************
-- *** Custom filters should be pasted above this line!!!
-- *************
end
go
------------------------------------------------------
-- starts a new trace
------------------------------------------------------
create procedure nic_aud_start_trace
( @filename nvarchar(255),
@overwrite int,
@endtime datetime,
@traceid int output,
@rc int output )
as
begin
-- Create a Queue
declare @del_cmd nvarchar(270)
declare @filename2 nvarchar(255)
declare @rst int
declare @maxfilesize bigint
-- if overwrite specified, delete existing file
if @overwrite = 1
begin
set @filename2 = @filename+'.trc'
-- if file exists, delete it
exec dbo.nic_aud_file_exists @filename2, @rst output
if @rst = 1
begin
set @del_cmd = 'del '+@filename2
exec @rc = master..xp_cmdshell @del_cmd, NO_OUTPUT
if (@rc != 0)
begin
raiserror ( 'ERROR: Error deleting old trace file',10,1, @@SPID,@filename2)
goto ERR_HANDLER
end
end
end
-- create new trace
set @maxfilesize = 100
exec @rc = sp_trace_create @traceid output, 0, @filename, @maxfilesize, @endtime
if (@rc != 0)
begin
raiserror ( 'ERROR: Error occured trying to start tracing for file',10,1, @@SPID,@filename)
goto ERR_HANDLER
end
-- set client specific events to trace
exec dbo.nic_aud_set_events @traceid
-- Set the trace status to start
exec sp_trace_setstatus @traceid, 1
ERR_HANDLER:
end
go
------------------------------------------------------
-- Procedure that returns the latest log data
------------------------------------------------------
create procedure nic_aud_get_data ( @newfile varchar(255), @oldfile varchar(255) )
as
begin
declare @min_time datetime
if @newfile is null
begin
select null
end
else if @oldfile is null
begin
select EventClass,
NTUserName,
LoginName,
SPID,
StartTime,
ObjectName,
DatabaseID,
TransactionID,
NTDomainName,
HostName,
ClientProcessID,
ApplicationName,
Duration,
EndTime,
Reads,
Writes,
CPU,
Permissions,
Severity,
EventSubClass,
ObjectID,
Success,
IndexID,
IntegerData,
ServerName,
ObjectType,
NestLevel,
State,
Error,
Mode,
Handle,
DatabaseName,
FileName,
OwnerName,
RoleName,
TargetUserName,
DBUserName,
CAST(LoginSid AS varbinary(1024)),
TargetLoginName,
CAST(TargetLoginSid AS varbinary(1024)),
ColumnPermissions,
CAST(BinaryData AS varbinary(1024)),
CAST(TextData AS varchar(1024))
from sys.fn_trace_gettable(@newfile, default)
end
else if @newfile = @oldfile
begin
raiserror ( 'ERROR: Duplicate filenames not supported.', 10, 1, @@SPID, @newfile )
end
else
begin
-- get the min time from new file
select @min_time = min(StartTime)
from sys.fn_trace_gettable(@newfile, default)
select EventClass,
NTUserName,
LoginName,
SPID,
StartTime,
ObjectName,
DatabaseID,
TransactionID,
NTDomainName,
HostName,
ClientProcessID,
ApplicationName,
Duration,
EndTime,
Reads,
Writes,
CPU,
Permissions,
Severity,
EventSubClass,
ObjectID,
Success,
IndexID,
IntegerData,
ServerName,
ObjectType,
NestLevel,
State,
Error,
Mode,
Handle,
DatabaseName,
FileName,
OwnerName,
RoleName,
TargetUserName,
DBUserName,
CAST(LoginSid AS varbinary(1024)),
TargetLoginName,
CAST(TargetLoginSid AS varbinary(1024)),
ColumnPermissions,
CAST(BinaryData AS varbinary(1024)),
CAST(TextData AS varchar(1024))
from sys.fn_trace_gettable(@newfile, default) as t1
union
select EventClass,
NTUserName,
LoginName,
SPID,
StartTime,
ObjectName,
DatabaseID,
TransactionID,
NTDomainName,
HostName,
ClientProcessID,
ApplicationName,
Duration,
EndTime,
Reads,
Writes,
CPU,
Permissions,
Severity,
EventSubClass,
ObjectID,
Success,
IndexID,
IntegerData,
ServerName,
ObjectType,
NestLevel,
State,
Error,
Mode,
Handle,
DatabaseName,
FileName,
OwnerName,
RoleName,
TargetUserName,
DBUserName,
CAST(LoginSid AS varbinary(1024)),
TargetLoginName,
CAST(TargetLoginSid AS varbinary(1024)),
ColumnPermissions,
CAST(BinaryData AS varbinary(1024)),
CAST(TextData AS varchar(1024))
from sys.fn_trace_gettable(@oldfile, default) as t2
where StartTime >= @min_time
end
end
go
------------------------------------------------------
-- Starts new trace, stops existing trace,
-- and updates trace info record in db.
------------------------------------------------------
create procedure nic_aud_swap_trace
( @maxidx int,
@file_template nvarchar(255),
@overwrite int,
@dummy varchar(256) )
as
begin
declare @old_idx int
declare @new_idx int
declare @old_trace_id int
declare @new_trace_id int
declare @filename nvarchar(255)
declare @prev_prev_file nvarchar(255)
declare @prev_file nvarchar(255)
declare @tomorrow datetime
declare @rc int
declare @rst int
if (@maxidx < 3)
begin
raiserror ( 'ERROR: Max Index must be 3 or greater.',10,1, @@SPID,@filename)
goto ERR_HANDLER
end
-- get the current running trace info
exec dbo.nic_aud_get_curr_rec @file_template, @old_idx output, @old_trace_id output
-- if we reach max index, wrap filename index to beginning (e.g., trace_1.trc, trace_2.trc )
if @old_idx >= @maxidx
begin
set @new_idx = 1
end
else
begin
set @new_idx = coalesce(@old_idx, 0) + 1
end
-- construct new trace filename and start trace
set @filename = dbo.nic_aud_get_filename ( @file_template, @new_idx, null )
set @tomorrow = getDate() + 1
exec dbo.nic_aud_start_trace @filename, @overwrite, @tomorrow, @new_trace_id output, @rc output
if (@rc != 0)
begin
raiserror ( 'ERROR: Error occured trying to start tracing for file',10,1, @@SPID,@filename)
goto ERR_HANDLER
end
-- wait 5 seconds to make sure it starts
exec dbo.nic_aud_wait 5
-- if existing trace is running, end it
if @old_trace_id > 0
begin
exec dbo.nic_aud_end_trace @old_trace_id
end
-- update new trace information
exec dbo.nic_aud_update_trace @file_template, @new_trace_id, @new_idx, @tomorrow
-- wait 5 seconds to make sure old trace ends
exec dbo.nic_aud_wait 5
-- get previous trace filenames
set @prev_prev_file = dbo.nic_aud_get_filename ( @file_template, dbo.nic_aud_get_prev_idx ( @maxidx, @old_idx ), 'trc' )
set @prev_file = dbo.nic_aud_get_filename ( @file_template, @old_idx, 'trc' )
-- if previous previous trace file doesn't exist, set to null
exec dbo.nic_aud_file_exists @prev_prev_file, @rst output
if @rst = 0
begin
set @prev_prev_file = null
end
-- if previous trace file doesn't exist, set to null
exec dbo.nic_aud_file_exists @prev_file, @rst output
if @rst = 0
begin
set @prev_file = null
end
-- return data to client based on existence of previous trace files
exec dbo.nic_aud_get_data @prev_file, @prev_prev_file
ERR_HANDLER:
end
go
--------------------------------
--------------------------------
January 19, 2008 at 9:47 am
It looks like it gets the trace ID from the nic_aud_trace table. Check that table, see what's in there
declare get_traces cursor for
select curr_trace_id from nic_aud_trace
Also, it's not tracing to a table, it's tracing to a file
-- create new trace
set @maxfilesize = 100
exec @rc = sp_trace_create @traceid output, 0, @filename, @maxfilesize, @endtime
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply