Remember, when scripting out jobs for reuse...

That they all use the same schedule ID, and thus the same schedule.  Modify the schedule in one job, it's inherently changed on the other.

[Automation] importing trace files via Cleartrace for several servers

(update 2013/09/19 minor bug to deal with a staging table that had non-sequential IDs)

I just posted this on the Cleartrace forums, but wanted a copy here.

Basically, Cleartrace takes trace files, imports the code calls, and turns it into information.  Here's the code that runs most often, here's the code that takes the longest to run, here's your I/O hog, etc.  

Everybody I ask about Profiler typically talks about running it then manually going through the results.  While that's fine for small stuff, I'm more interested in aggregations and baselines.  So were the people behind Cleartrace, fortunately.  But I need it automated, and on several machines. Hence, this code.  

Once cleartrace is running and set up to use a server, run this code to add a config table, then set up some servers.  

I assume you're running with adequate permissions; if not, set up the folders put the files in a share and set that as your trace_folder.  Set up a specific name for the trace.  

Once it's imported some, bring up Cleartrace (the real version) and choose an import group, then start looking at data.  Slice and dice as necessary.  

Note that this does NOT keep the details - it's all aggregates, even in the CT DB.  Which is why we save the trace files for posterity.

This is not any big deal, it's a rough first pass, I'm sure I'll have to fix it later.  But it works and it lets me check out servers for user requests.  (I didn't use XE or EN for several reasons, not the least of which because XE doesn't have all the capabilities I need under 2008)

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.  Import a file to make sure it's all created.
Run this script in that database.
Create a folder to hold the trace files
Add rows to the CTAutoServerList table
Set up traces on the servers you want to manage.  Set a beginning/end time so that this has time to run at night and import.

Run traces.

While the traces are stopped, run this code

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