Automated Profiler trace import

,

This stored procedure was written to complement the automated profiler trace create procedure (should be here somewhere - check the scripts).

Bascially, given the database (sample script to create the database included, but you may want to personalize it), this stored procedure will import the tracefile content into the table, which is indexed on expected search columns.

I recommend that this database and stored procedure live on a seperate server from any critical environment, ideally one where you can create copies of your databases, so that after reviewing the information extracted from yur traces, you can review the impact of changes in a stand-alone environment.

((As usual, please carefully read and understand ANY script you get before using it, and TEST it before it goes anywhere near a critical environment. No waranties etc. implied ))

****DB CREATE SCRIPTS****
/****** Object:  Database ProfilerLog    Script Date: 04/03/2005 09:59:07 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ProfilerLog')
	DROP DATABASE [ProfilerLog]

CREATE DATABASE [ProfilerLog]  ON (NAME = N'ProfilerLog_Data', FILENAME = N'D:\DB_Data\ProfilerLog_Data.MDF' , SIZE = 1024, FILEGROWTH = 10%) LOG ON (NAME = N'ProfilerLog_Log', FILENAME = N'D:\DB_Log\ProfilerLog_Log.LDF' , SIZE = 128, FILEGROWTH = 10%)
exec sp_dboption N'ProfilerLog', N'autoclose', N'false'
exec sp_dboption N'ProfilerLog', N'bulkcopy', N'false'
exec sp_dboption N'ProfilerLog', N'trunc. log', N'true'
exec sp_dboption N'ProfilerLog', N'torn page detection', N'true'
exec sp_dboption N'ProfilerLog', N'read only', N'false'
exec sp_dboption N'ProfilerLog', N'dbo use', N'false'
exec sp_dboption N'ProfilerLog', N'single', N'false'
exec sp_dboption N'ProfilerLog', N'autoshrink', N'false'
exec sp_dboption N'ProfilerLog', N'ANSI null default', N'false'
exec sp_dboption N'ProfilerLog', N'recursive triggers', N'false'
exec sp_dboption N'ProfilerLog', N'ANSI nulls', N'false'
exec sp_dboption N'ProfilerLog', N'concat null yields null', N'false'
exec sp_dboption N'ProfilerLog', N'cursor close on commit', N'false'
exec sp_dboption N'ProfilerLog', N'default to local cursor', N'false'
exec sp_dboption N'ProfilerLog', N'quoted identifier', N'false'
exec sp_dboption N'ProfilerLog', N'ANSI warnings', N'false'
exec sp_dboption N'ProfilerLog', N'auto create statistics', N'false'
exec sp_dboption N'ProfilerLog', N'auto update statistics', N'false'
if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'ProfilerLog', N'db chaining', N'false'

go

Use [ProfilerLog]

/****** Object:  Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 09:59:51 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sqltext_combined]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sqltext_combined]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sqltext_combined]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[sqltext_combined] (
	[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
	[DateEntered] [datetime] NOT NULL CONSTRAINT [DF__sqltext_c__DateE__7C8480AE] DEFAULT (getdate()),
	[EventClass] [int] NULL ,
	[DatabaseID] [int] NULL ,
	[TextData] [varchar] (6000) NULL ,
	[SPID] [int] NULL ,
	[Duration] [bigint] NULL ,
	[EndTime] [datetime] NULL ,
	[Reads] [bigint] NULL ,
	[Writes] [bigint] NULL ,
	[CPU] [int] NULL ,
	[TransactionID] [bigint] NULL ,
	[NTUserName] [varchar] (128) NULL ,
	[NTDomainName] [varchar] (128) NULL ,
	[HostName] [varchar] (128) NULL ,
	[ClientProcessID] [int] NULL ,
	[ApplicationName] [varchar] (128) NULL ,
	[LoginName] [varchar] (128) NULL ,
	[StartTime] [datetime] NULL ,
	[Permissions] [int] NULL ,
	[Severity] [int] NULL ,
	[EventSubClass] [int] NULL ,
	[ObjectID] [int] NULL ,
	[Success] [int] NULL ,
	[IndexID] [int] NULL ,
	[IntegerData] [int] NULL ,
	[ServerName] [varchar] (128) NULL ,
	[ObjectType] [int] NULL ,
	[NestLevel] [int] NULL ,
	[State] [int] NULL ,
	[Error] [int] NULL ,
	[Mode] [int] NULL ,
	[Handle] [int] NULL ,
	[ObjectName] [varchar] (128) NULL ,
	[DatabaseName] [varchar] (128) NULL ,
	[FileName] [varchar] (128) NULL ,
	[OwnerName] [varchar] (128) NULL ,
	[RoleName] [varchar] (128) NULL ,
	[TargetUserName] [varchar] (128) NULL ,
	[DBUserName] [varchar] (128) NULL ,
	[TargetLoginName] [varchar] (128) NULL ,
	[ColumnPermissions] [int] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[RowNumber]
	)  ON [PRIMARY] 
) ON [PRIMARY]
END
/****** Object:  Index SQLTEXT_COMB_IX1 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:12 ******/
if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX1' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX1]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX1] ON [dbo].[sqltext_combined]([RowNumber]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX2 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:17 ******/
if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX2' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX2]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX2] ON [dbo].[sqltext_combined]([SPID], [Duration]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX3 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:20 ******/
if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX3' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX3]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX3] ON [dbo].[sqltext_combined]([SPID], [CPU]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX4 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:26 ******/
if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX4' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX4]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX4] ON [dbo].[sqltext_combined]([SPID], [Reads]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX5 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:29 ******/
if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX5' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX5]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX5] ON [dbo].[sqltext_combined]([SPID], [Reads]) ON [PRIMARY]
GO

****END DB SCRIPTS*****
****SP Scritp****
if exists (select 'SP exists' from sysobjects where name = 'UP_AutoImport_TraceFiles' and xtype = 'p')
	drop proc UP_AutoImport_TraceFiles
go
create procedure UP_AutoImport_TraceFiles
	@TraceFilePath varchar(255)
--	Create By:	Regan Galbraith
--	Create On:	2004-12-28
--	Purpose:	
--		This stored procedure was written to facilitate the automatic import of profiler trace files.
--		
--	Example:
--		exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'
--
--	Possible future additions:
--		1> 	simple enhancement to specify DB to store data, and table.
--		2>	accepting a parameter instead of apply the default .trc. Use the .trc as default
--		3>	implementing default value's for dir's, so that it can run without parm's ... good or bad?
--
--	Change Control:	version 1 - creation and adding of comment
--
--
as
begin
set nocount on
set quoted_identifier off

create table #TraceFileList(TraceFileName varchar(128))

create table #DirExists
(FileExists int,
FileIsDir int,
ParentDirExists int)

declare @TraceFileName varchar(255)
declare @dirSQL varchar(255)
declare @ErrorDir varchar(6)

insert into #DirExists exec master..xp_fileexist @TraceFilePath
if not exists(select 'Dir Exists' from #DirExists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
	begin
	set @ErrorDir = 'Data'
	goto DirError
end

set @dirSQL = 'dir '+@TraceFilePath+'*.trc /b'
--print @dirSQL

delete from #TraceFileList
insert into #TraceFileList
exec master..xp_cmdshell @dirSQL
--select * from #TraceFileList

declare TraceFileList cursor for select TraceFileName from #TraceFileList

open TraceFileList
fetch next from TraceFileList into @TraceFileName

while @@fetch_status <> -1
begin
-- this assumes a naming standard where your back names are databasename_ and then more detail. If you have 
-- a naming standard for backups of simply databasename.bak, then change this to charindex ('.' , for example.
select @TraceFileName = @TraceFilePath+@TraceFileName
select 'Files Processed : ' +@TraceFileName
if @TraceFileName is not null
	begin
	INSERT INTO [ProfilerLog].[dbo].[sqltext_combined]
		([EventClass], [DatabaseID], [TextData], [SPID], [Duration], [EndTime], 
		[Reads], [Writes], [CPU], [TransactionID], [NTUserName], [NTDomainName], 
		[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime], 
		[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], 
		[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode], 
		[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], 
		[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions])
	SELECT [EventClass],[DatabaseID],Cast([TextData] as varchar(6000)) as TextData,
		[SPID],[Duration],[EndTime],[Reads],[Writes],[CPU], [TransactionID], [NTUserName], [NTDomainName],
		[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime], 
		[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], 
		[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode], 
		[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], 
		[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions]
		
	FROM ::fn_trace_gettable(@TraceFileName, default)
--	where max(DATALENGTH(textdata)) <= 6000
end	
    fetch next from TraceFileList into @TraceFileName
end

close TraceFileList
deallocate TraceFileList
goto finish

DirError:
Print 'An error with the '+@errordir+' Directory given - please check that it exists. Thanks'
drop table #TraceFileList
drop table #DirExists
RETURN -1

Finish:
drop table #TraceFileList
drop table #DirExists
RETURN 0

end

--		exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'


****END SP Script****

Rate

Share

Share

Rate