SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 ClearTrace Support Forum
 Here's code to automate imports using cmdline CT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mbourgon
Starting Member

5 Posts

Posted - 08/01/2012 :  11:55:57  Show Profile  Reply with Quote
Just wrote this, figured I'd share. Bill, I absolutely love Cleartrace, the 3 operations DBAs believe it's a must-have for any DBA. Absolutely stuns me that more people don't know about it. We recently started running daily traces for certain events, and wanted a way to automatically import them so that we could read them with Cleartrace.

Hope this is useful, and please feel free to critique.
Looking forward to a 2012 version of the cmdline version!

*UPDATE*
no code change, just wanted to see if anyone was using this, if it was handy, or not. Also useful: if you run cleartrace against an open file, it will import the file but can't move it. So you could run this while your traces were open. Once the trace rolled, it would reimport it (overwriting what it currently had in the table for that file), and then move it. For us that means we can run this during the day too, and have useful information at any time.

*UPDATE2* 2013/09/19 I forgot to sub my staging table for the prod table, so missing records in Automated_Server_List could cause some to not work. This will probably be the last update; it works really well, this is just a bugfix, but obviously we're moving to Extended Events. (Bill - Any hope for an XE-compatible version? Obviously we can just query the XE XML, but I'll miss the UI capabilities).



USE Cleartrace
go
if object_id('Cleartrace..CT_AutoServerList') is not null
DROP TABLE CT_AutoServerList
CREATE TABLE CT_AutoServerList 
(id INT IDENTITY,
server_name sysname,
trace_folder VARCHAR(200),
trace_name VARCHAR(50),
is_active BIT,
insert_datetime DATETIME DEFAULT(GETDATE()),
update_datetime datetime 
)

SELECT * FROM CT_AutoServerList

INSERT INTO CT_AutoServerList
		( server_name ,
		trace_folder,
		trace_name,
		  is_active ,
		  insert_datetime 
		)
VALUES	( 'yourservernamehere', -- server_name - sysname
'e:\foldertoholdtraces',
'tracenamegoeshere',
 1 , -- is_active - bit
'2012-07-31 10:02:00' 
)


-----------------------------
--Cleartrace automated load--
--mdb 2012/08/01 1.00      --
-----------------------------
CREATE PROCEDURE ReadTrace.usp_AutomateTraceLoad
as
/*
--mdb 20120801 first version!

Purpose: Pull trace files from a variety of servers and use Cleartracecmd (Cleartrace Command Line) to 
automatically load into tables for processing, using a particular trace name and saving to a folder
on the "processing" server.  

Cleartrace can be found at www.scalesql.com/cleartrace/
and is written(?) & maintained by Bill Graziano.  It's his tool and I take no credit for it.  
It's indispensable for reading traces.  My code simply tries to automate it.

Instructions:
Download Cleartrace (full version, since we use Cleartracecmd.exe).
Run Cleartrace and choose a server/database.  It will create the DB and objects as needed.  
Run this script in that database.
Create a folder to hold the trace files
Add rows to the CTAutoServerList table

Goal: to run an import for each particular trace for a server, saving locally, and making available via cleartrace
Most of the code here is to guarantee that it gets processed once.
*/
DECLARE 
 @cleartrace_server sysname ,
 @cleartrace_database sysname,
 @archive_folder varchar(300),
 @executable_folder VARCHAR(300),
 @min INT, @max INT,
 @full_archive_folder_name varchar(300),
 @error INT,
 @cmd NVARCHAR(4000),
 @msg VARCHAR(1000)

if object_id('tempdb..#Error_Finder') is not null
    drop table #Error_Finder
create table #Error_Finder (listing nvarchar (255))

if object_id('tempdb..#File_Results') is not null
    drop table #File_Results
CREATE TABLE #File_Results (
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)

SET @cleartrace_server = 'cleartraceservernamehere'
SET @cleartrace_database = 'ClearTrace' 
SET @archive_folder = 'e:\trace_file_archive' --where to save trace files.
SET @executable_folder = 'e:\trace_file_archive' --where cleartracecmd.exe is.
SELECT @min = 1, @max = 0 --if no records at all, skip

--get rid of trailing slashes on the two fields, just in case
IF RIGHT(@archive_folder,1)='\' SET @archive_folder = LEFT(@archive_folder,LEN(@archive_folder)-1)
IF RIGHT(@executable_folder,1)='\' SET @executable_folder = LEFT(@executable_folder,LEN(@executable_folder)-1)

--Get a list of just the active servers/traces
--we could replace this with a ROW_NUMBER'd table, but we call it multiple places and I don't want more variables
--doing this so we only run against valid servers
DECLARE @server_list TABLE 
	(id INT IDENTITY,
	server_name sysname,
	trace_folder VARCHAR(200),
	trace_name VARCHAR(50))
INSERT INTO @server_list 
	SELECT server_name, 
			trace_folder, 
			trace_name 
	FROM CT_AutoServerList 
	WHERE is_active = 1

--loop through valid servers and grab trace files 
SELECT @min = MIN(id), @max = MAX(id) FROM @server_list
SELECT @min, @max
WHILE @min <= @max
BEGIN
	TRUNCATE TABLE #File_Results
	TRUNCATE TABLE #Error_Finder
	SELECT	@full_archive_folder_name = NULL,
			@error = 0,
			@cmd = NULL 

	------------------------------------------------------
	--Step 1: verify archive folder exists for that server
	------------------------------------------------------
	SELECT @full_archive_folder_name = @archive_folder + '\' + server_name 
	FROM @server_list WHERE id = @min

    --See if folder for server exists.  If not, try and create the folder.  If that fails, throw an error.
	INSERT INTO #File_Results
		(File_Exists, file_is_a_directory, parent_directory_exists)
	--verify it exists
	EXEC Master.dbo.xp_fileexist @full_archive_folder_name

	IF (SELECT TOP 1 File_is_a_Directory FROM #File_Results) = 0
	--if it does not, create it.  
	BEGIN
		SELECT @cmd = 'mkdir ' + @full_archive_folder_name
		INSERT #Error_Finder EXEC xp_cmdshell @cmd
		--throw errors if the create failed, as it'll import but not 
		SET @error = @@ERROR
		IF @error <> 0 OR 
			(SELECT COUNT(*) FROM #Error_Finder where 
				listing like '%error%' 
				OR listing LIKE '%not ready%' 
				OR listing LIKE '%not found%') > 0
		BEGIN
			SELECT @msg = '[Cleartrace] Error ' + CONVERT(VARCHAR,@error) + ' occurred during folder creation.'
				RAISERROR (@msg,16,1)
		END
	END 

	--------------------------------------
	--Step 2: import using ClearTraceCmd--
	--------------------------------------
	TRUNCATE TABLE #Error_Finder
	SET @cmd = NULL 
	SET @error = 0
	SELECT @cmd = 
		@executable_folder + '\ClearTraceCmd.exe' 
		+ ' /s ' + @cleartrace_server
		+ ' /d ' + @cleartrace_database
		+ ' /f ' + '\\' + server_name + '\' + REPLACE(trace_folder,':','$') + '\' --change e: to e$, if not using shares
		+ trace_name + '_*' --_* allows web1 and web2
		+ ' /group ' + server_name + '_' + trace_name 
		+ ' /archivedir ' + @full_archive_folder_name
	FROM @server_list WHERE id = @min

	INSERT #Error_Finder EXEC xp_cmdshell @cmd

	SET @error = @@ERROR
	IF @error <> 0 OR 
		(SELECT COUNT(*) FROM #Error_Finder where 
			listing like '%error%' 
			OR listing LIKE '%not ready%' 
			OR listing LIKE '%not found%') > 0
	BEGIN
		SELECT @msg = '[Cleartrace] Error ' + CONVERT(VARCHAR,@error) + ' occurred during cmdline import.'
			RAISERROR (@msg,16,1)
		SELECT * FROM #Error_Finder
	END

	SET @min = @min + 1
end



DROP TABLE #Error_Finder
DROP TABLE #File_Results
go

Edited by - mbourgon on 09/19/2013 10:15:30
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000