Note if you are patching your SQL Server then perform the following steps:
--- First Shutdown Trace
--- We keep any existing trace files.
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_init_trace]') AND type in (N'P', N'PC'))
exec nic_aud_init_trace 0
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[rsa_delete_trace_files]') AND type in (N'P', N'PC'))
drop procedure rsa_delete_trace_files
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[rsa_file_exists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function rsa_file_exists
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'RSA_MSSQLAuditStoredProcedures')
drop assembly RSA_MSSQLAuditStoredProcedures
GO
----- END SCRIPT
------
PATCH AND
RESTART SQL SERVER SERVICE
-----
SCRIPT TO RESTART TRACING
--We recreate the assemby
CREATE ASSEMBLY RSA_MSSQLAuditStoredProcedures
FROM 'C:\MyDBApp\RSA_MSSQLAuditStoredProcedures.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
---------------------------------------------
-- CLR Endpoint Function
-- to check if a file existProcedure
-- to delete trace files
---------------------------------------------
create function rsa_file_exists
(
@filepath nvarchar(255)
)
returns int
as external name RSA_MSSQLAuditStoredProcedures.[RSA_MSSQLAuditStoredProcedures.RSA_MSSQLAuditStoredProcedures].rsa_file_exists;
go
---------------------------------------------
-- CLR Endpoint Procedure
-- to delete trace files
---------------------------------------------
create procedure rsa_delete_trace_files
(
@filepath nvarchar(255)
)
as external name RSA_MSSQLAuditStoredProcedures.[RSA_MSSQLAuditStoredProcedures.RSA_MSSQLAuditStoredProcedures].rsa_delete_trace_files;
go
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_init_trace]') AND type in (N'P', N'PC'))
exec nic_aud_init_trace 0
GO
---END SCRIPT
During the period that the SQL Server is being patched you will see the following messages in the /var/log/messages of the log collector ( Taken from my test system)
Apr 19 13:30:46 REMOTELOGCOL NwLogCollector[25428]: [OdbcCollection] [failure] [mssql.ECAT_MSSQL] [processing] [ECAT_MSSQL] [processing] Publish error: Error calling SQLMoreResults: Statement: "exec nic_aud_swap_trace 30, 'c:\MyTraceFiles\', 1, 'WHERE StartTime > 2016-04-19 14:22:09.623'"; Reason: state: S1000; error-code: 139814070390809; description: [RSA][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.rsa_file_exists", or the name is ambiguous.state: S1000; error-code: 139814070390809; description: [RSA][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.rsa_file_exists", or the name is ambiguous.state: S1008; error-code: 139814070390309; description: [RSA][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The statement has been terminated.state: 23000; error-code: 139814070389315; description: [RSA][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Violation of PRIMARY KEY constraint 'PK__nic_aud___9A5818ED427D50A8'. Cannot insert duplicate key in object 'dbo.nic_aud_trace'. The duplicate key value is (c:\MyTraceFiles\).state: S1000; error-code: 139814070390809; description: [RSA][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.rsa_file_exists", or the name is ambiguous.
