April 20, 2016 at 1:57 am
Hello everyone.
Hope you can offer some advice or guidance on this subject.
Currently I am running SQL traces that capture Audit information and are extracted to an 3rd party application. When ever the SQL servers restarts , due to patching or maintenance the traces are not shutdown gracefully.
This being the case the application is then unable to read the .trc files until a manual stop Trace is issued. The app can then be restarted and trace info is collected.
Anyone got any thoughts or experience on how to tackle this scenario ?
Trace ID's are not the same on each machine that has the traces run.
Any automated steps I could build into the restart process, anyone have any examples ?
thanks all.
April 20, 2016 at 4:18 am
You can set up a job that is scheduled to run whenever SQL Server Agent starts. The job would perform the steps that you do manually now after a restart. You'd need to be consider what would happen on occasions where SQL Server Agent is restarted with SQL Server still running, though.
John
April 20, 2016 at 8:24 pm
Below is the code that i use. Basically there is a sproc to create a trace file and a sproc to control the trace file and load data.
-- Create table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AuditSQLAccess](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[ApplicationName] [nvarchar](128) NULL,
[ClientProcessID] [int] NULL,
[DatabaseID] [int] NULL,
[DatabaseName] [nvarchar](128) NULL,
[EventSequence] [bigint] NULL,
[GroupID] [int] NULL,
[HostName] [nvarchar](128) NULL,
[IntegerData2] [int] NULL,
[IsSystem] [int] NULL,
[LineNumber] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[LoginSid] [image] NULL,
[NTDomainName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[NestLevel] [int] NULL,
[Offset] [int] NULL,
[RequestID] [int] NULL,
[SPID] [int] NULL,
[ServerName] [nvarchar](128) NULL,
[SessionLoginName] [nvarchar](128) NULL,
[StartTime] [datetime] NULL,
[State] [int] NULL,
[TextData] [ntext] NULL,
[TransactionID] [bigint] NULL,
[XactSequence] [bigint] NULL,
[BinaryData] [image] NULL,
[ObjectID] [int] NULL,
[ObjectName] [nvarchar](128) NULL,
[ObjectType] [int] NULL,
[SourceDatabaseID] [int] NULL,
[filepath] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-- Stored procedure to start the trace
ALTER procedure [dbo].[Start_AuditTrace]
As
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
DECLARE @format_datetime char(25)
declare @file nvarchar(256)
declare @tracefile nvarchar(256)
SET @format_datetime = CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')
set @file='K:\SQLAudit_Trace\SQLAuditTRC_'+REPLACE(@@servername,'\','_')
set @tracefile=@file
select @tracefile
set @maxfilesize = 500
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, @tracefile , @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 42, 7, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 64, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 9, @on
exec sp_trace_setevent @TraceID, 42, 41, @on
exec sp_trace_setevent @TraceID, 42, 49, @on
exec sp_trace_setevent @TraceID, 42, 2, @on
exec sp_trace_setevent @TraceID, 42, 6, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 50, @on
exec sp_trace_setevent @TraceID, 42, 62, @on
exec sp_trace_setevent @TraceID, 42, 66, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 28, @on
exec sp_trace_setevent @TraceID, 42, 60, @on
exec sp_trace_setevent @TraceID, 42, 5, @on
exec sp_trace_setevent @TraceID, 42, 29, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 64, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 41, @on
exec sp_trace_setevent @TraceID, 13, 49, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 50, @on
exec sp_trace_setevent @TraceID, 13, 66, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 51, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 60, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Add your filters here. Below is an example to not inlclude application name '.net' andexclude login namees SYSTEM
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Repl-LogReader'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'.Net SqlClient Data Provider'
exec sp_trace_setfilter @TraceID, 6, 0, 7, N'SYSTEM'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
-- Script to control trace file.
ALTER PROCEDURE [dbo].[Control_AuditTrace]
AS
DECLARE @tcid INT
DECLARE @format_datetime VARCHAR(MAX)
DECLARE @file VARCHAR(MAX)
DECLARE @file2 VARCHAR(MAX)
DECLARE @cmd VARCHAR(8000)
DECLARE @cmd2 VARCHAR(8000)
DECLARE @file_path NVARCHAR(MAX)
CREATE TABLE #file_list
(
fl_name VARCHAR(500) NULL
)
SET @format_datetime = CONVERT(VARCHAR(10), GETDATE(), 112)
+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108), ':', '')
SET @file = 'K:\SQLAudit_Trace\SQLAuditTRC_' + REPLACE(@@servername, '\',
'_') + '.trc'
SET @file_path = 'K:\SQLAudit_Trace'
SET @cmd2 = 'EXEC master.dbo.xp_cmdshell ''dir "' + @file_path + '"/b /s'''
IF EXISTS ( SELECT *
FROM sys.traces
WHERE path LIKE '%K:\SQLAudit_Trace%' )
BEGIN
SELECT @tcid = id
FROM sys.traces
WHERE path LIKE '%K:\SQLAudit_Trace%'
--select @tcid
PRINT 'About to stop and disable trace'
EXEC sp_trace_setstatus @tcid, 0
EXEC sp_trace_setstatus @tcid, 2
PRINT ' About to load trace data in the table'
INSERT INTO dbo.AuditSQLAccess
SELECT
--*
EventClass ,
ApplicationName ,
ClientProcessID ,
DatabaseID ,
DatabaseName ,
EventSequence ,
GroupID ,
HostName ,
IntegerData2 ,
IsSystem ,
LineNumber ,
LoginName ,
LoginSid ,
NTDomainName ,
NTUserName ,
NestLevel ,
Offset ,
RequestID ,
SPID ,
ServerName ,
SessionLoginName ,
StartTime ,
State ,
TextData ,
TransactionID ,
XactSequence ,
BinaryData ,
ObjectID ,
ObjectName ,
ObjectType ,
SourceDatabaseID ,
@file
FROM FN_TRACE_GETTABLE(@file, DEFAULT)
PRINT 'Renaming Existing trace file'
SET @file2 = 'SQLAuditTRC_' + REPLACE(@@servername, '\', '_')+ '_' + @format_datetime + '.trc'
-- change trace file name
SELECT @cmd = 'RENAME ' + @file + ' ' + @file2
EXEC master..xp_cmdshell @cmd
END
INSERT INTO #file_list
EXEC ( @cmd2
)
-- This condition was added to see if a trace was abrutply stopped
IF EXISTS ( SELECT *
FROM #file_list
WHERE fl_name = @file )
--AND NOT EXISTS (select * from AuditSQLAccess where filepath =@file )
BEGIN
PRINT 'Second Batch: About to load trace data in the table'
INSERT INTO dbo.AuditSQLAccess
SELECT
--*
EventClass ,
ApplicationName ,
ClientProcessID ,
DatabaseID ,
DatabaseName ,
EventSequence ,
GroupID ,
HostName ,
IntegerData2 ,
IsSystem ,
LineNumber ,
LoginName ,
LoginSid ,
NTDomainName ,
NTUserName ,
NestLevel ,
Offset ,
RequestID ,
SPID ,
ServerName ,
SessionLoginName ,
StartTime ,
State ,
TextData ,
TransactionID ,
XactSequence ,
BinaryData ,
ObjectID ,
ObjectName ,
ObjectType ,
SourceDatabaseID ,
@file
FROM FN_TRACE_GETTABLE(@file, DEFAULT)
PRINT 'Renaming Existing trace file'
SET @file2 = 'SQLAuditTRC_' + REPLACE(@@servername, '\', '_')
+ '_' + @format_datetime + '.trc'
-- change trace file name
SELECT @cmd = 'RENAME ' + @file + ' ' + @file2
SELECT @cmd
EXEC master..xp_cmdshell @cmd
END
ELSE
PRINT ' Didnd''t had to do anything, nothing was running'
--Script to create job
USE [msdb]
GO
/****** Object: Job [DBMAINT - AuditSQLLogins] Script Date: 04/20/2016 21:22:06 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 04/20/2016 21:22:06 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AuditSQLLogins',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Control Trace] Script Date: 04/20/2016 21:22:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Control Trace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
exec dbo.Control_AuditTrace ',
@database_name=N'dbutil',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Start Trace] Script Date: 04/20/2016 21:22:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Start Trace',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
Exec dbo.Start_AuditTrace',
@database_name=N'yourDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 30 min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160328,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Start after server restart',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160331,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply