The Following Sections Describe the Steps in the DTS Package: P

The following sections describe the steps in the DTS package:

Ø Create tables in production:

-- Create table to hold the active trace number
-- (for example, in database PerformanceTraces):

CREATE TABLE [dbo].[TRC_ActiveTraceNumber] (

[TraceNumber] [int] NOT NULL ,

[InsertDate] [datetime] NOT NULL

)

Ø Create tables in “Monitor:”

-- Create table to hold the trace results
-- (for example, in database PerformanceTraces)
-- Make sure the table has the structure of the trace file you are importing:

CREATE TABLE [dbo].[TRC_SqlTraceData] (

[InsertDate] [datetime] NULL ,

[TextData] [ntext] NULL ,

[DatabaseID] [int] NULL ,

[SPID] [int] NULL ,

[Duration] [bigint] NULL ,

[StartTime] [datetime] NULL ,

[Reads] [bigint] NULL ,

[Writes] [bigint] NULL ,

[CPU] [int] NULL ,

[EventClass] [int] NOT NULL

)

-- Create table to hold the database's list imported from production:

CREATE TABLE [dbo].[TMP_DatabaseList] (

[DatabaseID] [int] NOT NULL ,

[DatabaseName] [sysname] NOT NULL

)

-- Create table to hold the database's list for the reports:

CREATE TABLE [dbo].[TRC_DatabaseList] (

[DatabaseID] [int] NOT NULL ,

[DatabaseName] [sysname] NOT NULL

)

Ø Purge data from “Monitor:”

Declare @NumberOfDays int

Set @NumberOfDays = 45 -- to delete data older than 45 days

Delete from TRC_SqlTraceData where InsertDate <= dateadd(dd,@NumberOfDays,getdate()

Ø Stop existing trace in Production:

Declare @TraceID

If exists (select 1 from TRC_ActiveTraceNumber)

Begin

Select @TraceID = TraceNumber from TRC_ActiveTraceNumber

exec sp_trace_setstatus @TraceID, 0

-- The trace file can be imported and deleted only if the trace is STOPPED:

exec sp_trace_setstatus @TraceID, 2

End

Ø Gather traced data to “Monitor:”

Declare @TraceFile varchar(500)

-- The trace file (using a share name you created):

Set @TraceFile = ‘\\ProdServer\PerfTraces\MyTrace.trc’

INSERT INTO TRC_SqlTraceData

(InsertDate, TextData, DatabaseID, SPID, Duration, StartTime, Reads, Writes, CPU, EventClass)

select getdate(),TextData, DatabaseID, SPID, Duration, StartTime, Reads, Writes, CPU, EventClass

FROM ::fn_trace_gettable(@msg, default)

Ø Start/create trace in Production:

-- Delete active trace id from the table:

If exists (select 1 from TRC_ActiveTraceNumber)

Truncate table TRC_ActiveTraceNumber

-- The existing trace file has to be deleted first
-- You can delete it using xp_cmdshell or by running a batch file:

exec master..xp_cmdshell 'del c:\PerformanceTraces\MyTraces\MyTrace.trc'

-- Create the trace
-- (the following commands are copied from the trace definition scripted with Profiler
-- as explained above, please note that at the end, the TraceID is inserted into
-- the TRC_ActiveTraceNumber table):

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5

-- Please replace the text c:\PerformanceTraces\MyTraces, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure the server has

-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N' c:\PerformanceTraces\MyTraces ', @maxfilesize, NULL

if (@rc != 0) goto error

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 9, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

set @bigintfilter = 500000

exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

set @bigintfilter = 10000

exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

set @bigintfilter = 5000

exec sp_trace_setfilter @TraceID, 17, 0, 4, @bigintfilter

set @intfilter = 500

exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- Instead of displaying the TraceID as in the initial script,
-- insert it into the active trace table:

TRC_ActiveTraceNumber] ( [TraceNumber],[InsertDate])

Select @TraceID, getdate()

Ø Truncate database list table in “Monitor:”

-- The table is truncated before it is imported by the DTS:

Truncate table TMP_DatabaseList

Ø Transfer database list from Production to “Monitor:”

The query transferred into the TMP_DatabaseList table is:

SQL 2000:
select dbid, name from master..sysdatabases

SQL 2005:

select database_id, name from master.sys.databases

Ø Update database list in “Monitor:”

-- Insert new databases:

If exists (select 1 from TMP_DatabaseList

where DatabaseID not in (select DatabaseID from TRC_DatabaseList)

insert into TRC_DatabaseList (DatabaseID, DatabaseName)

select DatabaseID, DatabaseName

from TMP_DatabaseList

where DatabaseID not in (select DatabaseID from TRC_DatabaseList)

-- Update different database name:

If exists (select 1 from TMP_DatabaseList tmp inner join TRC_DatabaseList t

On tmp.DatabaseID = t. DatabaseID

Where tmp.DatabaseName > t.DatabaseName)

Update TRC_DatabaseList

Set DatabaseName = tmp.DatabaseName

from TMP_DatabaseList tmp inner join TRC_DatabaseList t

On tmp.DatabaseID = t. DatabaseID

Where tmp.DatabaseName > t.DatabaseName

-- Old databases are not deleted from the list for reporting purposes