trace
-
Hi there everyone?i have setup a trace for my datadase to audit all transactions and login information,
my problem is that i need some to get my trace running even if i close it, and then i log in again.
is it possible to create a trigger or a procedure or something else for when i log into sql 2000 server to start running that trace?
please help
2 weeks to project deadline.
-
Okay.
Just went through this several weeks ago. 
This ended up being my solution. Because of the amount of events that I'm tracking I had to create a stored procedure because the set events went beyond the length of the Jobs window. Here is the header for the stored procedure. First step is to output your trace setup to a SQL Script. Then use the following to modify the filename and size bits.-------------------------------
CREATE PROCEDURE CP_Start_Trace
AS
IF not exists (SELECT * FROM :: fn_trace_getinfo(default))
-- Create a Queue
-- declare error label
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 25
declare @TrcFileName NVarchar(245)
set @TrcFileName =cast('%path%\%filename%_' as NVarchar) +
cast(right('0000' + cast(datepart(year,getdate()) as varchar),4) as NVarchar) +
cast(right('00' + cast(datepart(month,getdate()) as varchar),2) as NVarchar) +
cast(right('00' + cast(datepart(day,getdate()) as varchar),2) as NVarchar) +
cast(right('00' + cast(datepart(hour,getdate()) as varchar),2) as NVarchar)
exec @rc = sp_trace_create @TraceID output, 2,@TrcFileName , @maxfilesize, NULL
if (@rc != 0) goto error
------------------------------
I'm outputting to disk -- it just seems easier. I use the date and hour in the filename because the trace fails if you try to recycle the file name.
After you have built your SP. In the EM go to SQL Server Group -> %servername% -> Management -> SQL Server Agent -> Jobs. Do a "New Job", Name it, run as SA. Under the Steps add a new step and put in "EXECUTE CP_Start_Trace". Under the schedule set it to start whenever the SQL Server Agent starts.
I'll follow on in another post about deletes.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Now the other half of that coin -- the files consuming disk space. Being a good DBA, you should be playing Doom or Quake not watching your SQL server chug along, right?
You need to blow out the old files, without worrying. Again it depends on how much your tracing and audit requirements. I can do a 1-2GB on a real active day.
Also, you if this for auditing purposes, you just may need the last three days on disk - and the rest on tape. The following procedure goes through and deletes the files older than 3 days, and all weekend files. I think I even have it blow out holidays. But it accounts for the weekends by being aware of the day of the week.
Hope this helps.
BTW, I set this as scheduled job to run every 6 hours.-------------------------------------
CREATE PROCEDURE CP_ClearOldTCLTraceFiles
AS
DECLARE @CurrentDate as datetime
DECLARE @DeleteDate as datetime
DECLARE @StrSQL AS VarChar(3000)
DECLARE @StrCMD AS VARCHAR(3000)
select @CurrentDate = CAST(cast(right('0000' + cast(datepart(year,getdate()) as varchar),4) as NVarchar) + '-' +
cast(right('00' + cast(datepart(month,getdate()) as varchar),2) as NVarchar) + '-' +
cast(right('00' + cast(datepart(day,getdate()) as varchar),2) as NVarchar) +
CAST (' 00:00:00.000' AS VARCHAR ) AS DATETIME)
if NOT exists (select * FROM dbo.sysobjects where id = object_id(N'[Holiday_Schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Holiday_Schedule] (
[Holiday] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Day_Of_Week] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Observed] [datetime] NULL
) ON [PRIMARY]
INSERT Holiday_Schedule
VALUES('New Years Day','Saturday','01-Jan-05')
INSERT Holiday_Schedule
VALUES('Martin Luther King, Jr. Day','Monday','17-Jan-05')
INSERT Holiday_Schedule
VALUES('Presidents Day','Monday','21-Feb-05')
INSERT Holiday_Schedule
VALUES('Memorial Day','Monday','30-May-05')
INSERT Holiday_Schedule
VALUES('Independence Day','Monday','04-Jul-05')
INSERT Holiday_Schedule
VALUES('Labor Day','Monday','05-Sep-05')
INSERT Holiday_Schedule
VALUES('Columbus Day','Monday','10-Oct-05')
INSERT Holiday_Schedule
VALUES('Veterans Day','Friday','11-Nov-05')
INSERT Holiday_Schedule
VALUES('Thanksgiving Day','Thursday','24-Nov-05')
INSERT Holiday_Schedule
VALUES('Christmas Day','Monday','26-Dec-05')
END
if exists (select * FROM dbo.sysobjects where id = object_id(N'[TRC_DEL_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [TRC_DEL_TABLE]
END
if exists (select * FROM dbo.sysobjects where id = object_id(N'[TRC_TEMP_TABLE1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [TRC_TEMP_TABLE1]
END
if NOT exists (select * FROM dbo.sysobjects where id = object_id(N'[TRC_TEMP_TABLE1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TRC_TEMP_TABLE1] (
[Input_Data] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileDate] [datetime] NULL ,
[FileDateTime] [datetime] NULL
) ON [PRIMARY]
END
INSERT into TRC_TEMP_TABLE1 (Input_Data)
exec xp_cmdshell 'dir %path%\*.*'
DELETE FROM TRC_TEMP_TABLE1
WHERE Input_Data NOT LIKE '%.trc%'
OR Input_Data IS NULL
UPDATE TRC_TEMP_TABLE1
SET FileDate = CAST (LEFT(Input_Data,10) AS DATETIME ),
FileDateTime = CAST (LEFT(Input_Data,10) + ' ' + SUBSTRING(Input_Data, 12,6) AS DATETIME )
-- SELECT * FROM TRC_TEMP_TABLE1
DELETE FROM TRC_TEMP_TABLE1
WHERE FileDateTime >= @CurrentDate
select LTRIM(RTRIM(SUBSTRING(Input_Data, 40,50))) AS DEL_CMD, FileDateTime
INTO TRC_DEL_TABLE
FROM TRC_TEMP_TABLE1
WHERE DATEPART(dw,FileDateTime) IN (1,7)
DELETE FROM TRC_TEMP_TABLE1
WHERE DATEPART(dw,FileDateTime) IN (1,7)
INSERT INTO TRC_DEL_TABLE
select LTRIM(RTRIM(SUBSTRING(Input_Data, 40,50))) AS DEL_CMD, FileDateTime
FROM TRC_TEMP_TABLE1
WHERE FileDate IN (SELECT Date_Observed FROM Holiday_Schedule)
ORDER BY CAST (LEFT(Input_Data,10) + ' ' + SUBSTRING(Input_Data, 12,6) AS DATETIME )
DELETE FROM TRC_TEMP_TABLE1
WHERE FileDate IN (SELECT Date_Observed FROM Holiday_Schedule)
PRINT DATEPART(dw,@CurrentDate)
IF DATEPART(dw,@CurrentDate) = 2 AND DATEPART(dw,@CurrentDate) = 3
SELECT @DeleteDate = @CurrentDate - 6
ELSE
SELECT @DeleteDate = @CurrentDate - 4
INSERT INTO TRC_DEL_TABLE
select LTRIM(RTRIM(SUBSTRING(Input_Data, 40,50))) AS DEL_CMD, FileDateTime
FROM TRC_TEMP_TABLE1 WHERE FileDateTime <= @DeleteDate
ORDER BY FileDateTime
DELETE FROM TRC_TEMP_TABLE1
WHERE FileDateTime <= @DeleteDate
UPDATE TRC_DEL_TABLE
SET DEL_CMD = 'DEL L:\MSSQL\LOG\TCL_TRACE\' + DEL_CMD
-- PRINT @CurrentDate
-- PRINT @DeleteDate
DECLARE CmdLine CURSOR
FOR SELECT DEL_CMD FROM TRC_DEL_TABLE
ORDER BY FileDateTime
OPEN CmdLine
FETCH NEXT FROM CmdLine INTO @StrSQL
WHILE (@@fetch_status = 0)
BEGIN
PRINT @STRSQL
EXEC XP_CMDSHELL @STRSQL, no_output
--WAITFOR DELAY '00:00:10'
FETCH NEXT FROM CmdLine INTO @StrSQL
END
CLOSE CmdLine
DEALLOCATE CmdLine
--SELECT * FROM TRC_TEMP_TABLE1
--SELECT * FROM TRC_DEL_TABLE
if exists (select * FROM dbo.sysobjects where id = object_id(N'[TRC_DEL_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [TRC_DEL_TABLE]
END
if exists (select * FROM dbo.sysobjects where id = object_id(N'[TRC_TEMP_TABLE1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop table [TRC_TEMP_TABLE1]
END
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply