SQL Server Performance Forum – Threads Archive

DTS package logging sp’s

I’ve created two sproc’s to measure DTS package and DTS Step run times. It gives either an exact time or an average. Logging needs to be turned on for whatever package you want to look at. It also sets up a system function that converts time in seconds to a regular time format. I use it to tell what packages are currently running, which ones have errors, how long they ran, etc. It’s basically the logging info that you get when you right click on a DTS package, but it’s formatted and in one place. The two sp’s are set up as system sp’s so you can call them from any database. Hope it’s of use! Michael Copy below this line and run in QA ________________________________________________________________________________________ USE Master SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON EXEC sp_configure ‘allow updates’, 1 EXEC sp_MS_upd_sysobj_category 1 RECONFIGURE WITH OVERRIDE GO —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- IF EXISTS (SELECT id FROM dbo.sysobjects WHERE uid = USER_ID(‘system_function_schema’) AND name = ‘fn_timeformat’) DROP FUNCTION system_function_schema.fn_timeformat GO —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- CREATE FUNCTION system_function_schema.fn_timeformat(@ElapsedTime int) RETURNS varchar(10) AS BEGIN /*This Function accepts a time value in seconds within the bounds of an integer value and returns it in 00:00:00 format */ DECLARE @Sec int, @Min int, @Hour int, @PrintSec varchar(2), @PrintMin varchar(2), @PrintHour varchar(2), @Time varchar(10) SET @Sec = @ElapsedTime SET @Min = 0 SET @Hour = 0 WHILE @Sec >= 60 BEGIN SET @Sec = @Sec – 60 SET @Min = @Min + 1 END WHILE @Min >= 60 BEGIN SET @Min = @Min – 60 SET @Hour = @Hour + 1 END IF Len(@Sec) < 2 BEGIN SET @PrintSec = ‘0’ + CAST(@Sec AS varchar) END ELSE BEGIN SET @PrintSec = CAST(@Sec AS varchar) END IF Len(@Min) < 2 BEGIN SET @PrintMin = ‘0’ + CAST(@Min AS varchar) END ELSE BEGIN SET @PrintMin = CAST(@Min AS varchar) END IF Len(@Hour) < 2 BEGIN SET @PrintHour = ‘0’ + CAST(@Hour AS varchar) END ELSE BEGIN SET @PrintHour = CAST(@Hour AS varchar) END SET @Time = @PrintHour + ‘:’ + @PrintMin + ‘:’ + @PrintSec RETURN @Time END GO —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- IF OBJECT_ID(N’dbo.sp_PackageRunTime’) IS NOT NULL DROP PROC dbo.sp_PackageRunTime GO —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- CREATE PROCEDURE dbo.sp_PackageRunTime @RunModevarchar(7) = ‘Detail’, @StartDate datetime = NULL, @EndDatedatetime = NULL, @Ordernvarchar(100) = NULL AS SET NOCOUNT ON DECLARE @SQL nvarchar(4000) IF @StartDate IS NULL SET @StartDate = CONVERT(varchar(15), GETDATE(), 106) IF @EndDate IS NULL SET @EndDate = @StartDate IF @RunMode IN(‘Average’,’Avg’) BEGIN IF @Order IS NULL SET @Order = ‘ETime desc' SET @SQL = ’SELECT Name [Package], fn_timeformat(CAST(AVG(ElapsedTime)AS int)) [ElapsedTime], AVG(ElapsedTime) [ETime], MIN(LogDate) [MinLogDate], MAX(LogDate) [MaxLogDate] FROM Msdb.dbo.SysDtsPackageLog WHERE LogDate >= ”’ + CONVERT(varchar, @StartDate ,101) + ”' AND LogDate < ”’ + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) + ”' GROUP BY Name ORDER BY ‘ + @Order EXEC(@SQL) END IF @RunMode = ‘Detail’ BEGIN IF @Order IS NULL SET @Order = ‘Etime desc,Package' SET @SQL = ’SELECT Name [Package], Description, fn_timeformat(ElapsedTime) [ElapsedTime], StartTime, EndTime, Computer, Operator, ElapsedTime [ETime] FROM Msdb.dbo.SysDtsPackageLog WHERE LogDate >= ”’ + CONVERT(varchar, @StartDate, 101) + ”' AND LogDate < ”’ + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) + ”' ORDER BY ‘ + @Order EXEC(@SQL) END IF @RunMode IN(‘?’, ‘Help’) PRINT' Object:sp_PackageRunTime Author: Michael West Email:[email protected] Execution:sp_PackageRunTime ”Detail”, ”01/01/05” , etc or sp_PackageRunTime @RunMode = ”Average”, @StartDate = ”01/01/05”, @EndDate = ”01/20/05” or exec(sp_PackageRunTime) Parameters :mad:

RunMode varchar(7), @StartDate datetime, @EndDate datetime, @Order n varchar(100) Dependencies:fn_timeformat, logging enabled within the DTS package you want to track. Description: This procedure checks the DTS logs and provides overview info on DTS packages. It can show either a detail for each day selected within a range, or an average of those days. The default sort order is set to show either the amount of time in desc order or the package and the amount of time in desc order, but that can be changed to whatever is needed. The ElapsedTime column is varchar so sorting by it will not produce the desired results. Instead sort by ETime, which is ElapsedTime in seconds. Arguments: @RunMode (optional) – The default is ”Detail”; Also possible are ”Average” , ”Avg” , ”Help”, and ”?”. @StartDate (optional) – GETDATE() if not provided. @EndDate (optional) – @StartDate + 1 if not provided. @Order (optional) – Any sql compliant order statement. ' GO —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- IF OBJECT_ID(N’dbo.sp_StepRunTime’) IS NOT NULL DROP PROC dbo.sp_StepRunTime GO —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- —————————————————————————————— ————- CREATE PROCEDURE dbo.sp_StepRunTime @RunModevarchar(7) = ‘Detail’, @Step nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> = NULL, @StartDatedatetime = NULL, @EndDatedatetime = NULL, @Ordernvarchar(100) = NULL AS SET NOCOUNT ON DECLARE @SQL nvarchar(4000) IF @StartDate IS NULL SET @StartDate = CONVERT(varchar(15), GETDATE(), 106) IF @EndDate IS NULL SET @EndDate = @StartDate IF @Step IS NULL SET @Step = ‘%%' IF @RunMode = ‘Detail' BEGIN IF @Order IS NULL SET @Order = ‘ErrorCode asc, Package, StartTime, ETime' SET @SQL = ’SELECT Package, Step, ElapsedTime, StartTime, EndTime, ErrorCode, ErrorDescription, ProgressCount, ETime FROM (SELECT Pkg.Name [Package], Step.StepName [Step], fn_timeformat (Step.ElapsedTime) [ElapsedTime], Step.StartTime, ISNULL(Step.EndTime, ””) [EndTime], Step.ErrorCode, SUBSTRING(Step.ErrorDescription,24, LEN(Step.ErrorDescription)) [ErrorDescription], Step.ProgressCount, Step.ElapsedTime [ETime] FROM Msdb.dbo.SysDtsStepLog Step INNER JOIN Msdb.dbo.SysDtsPackageLog Pkg ON Step.LineageFull = Pkg.LineageFull WHERE Step.StartTime > ”’ + CONVERT(varchar, @StartDate, 101) + ”' AND Step.StartTime <= ”’ + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) + ”' AND Step.StepName LIKE ”’ + @Step + ”' )PackageSteps ORDER BY ‘ + @Order EXEC(@SQL) END IF @RunMode IN(‘Average’, ‘Avg’) BEGIN IF @Order IS NULL SET @Order = ‘ETime desc, Package, Step' SET @SQL = ’SELECT Package, Step, ElapsedTime, MinLogDate, MaxLogDate, ETime FROM (SELECT Pkg.Name [Package], Step.StepName [Step], fn_timeformat(AVG (Step.ElapsedTime)) [ElapsedTime], MIN(LogDate) [MinLogDate], MAX(LogDate) [MaxLogDate], AVG(Step.ElapsedTime) [ETime] FROM Msdb.dbo.SysDtsStepLog Step INNER JOIN Msdb.dbo.SysDtsPackageLog Pkg ON Step.LineageFull = Pkg.LineageFull WHERE Step.StartTime > ”’ + CONVERT(varchar, @StartDate, 101) + ”' AND Step.StartTime <= ”’ + CONVERT(varchar, DATEADD(d, 1, @EndDate), 101) + ”' GROUP BY Pkg.Name, Step.StepName )PackageSteps ORDER BY ‘ + @Order EXEC(@SQL) END IF @RunMode IN(‘?’, ‘Help’) PRINT' Object:sp_StepRunTime Author: Michael West Email:[email protected] Execution:sp_StepRunTime ”Detail”, ”01/01/05” , etc or sp_StepRunTime @RunMode = ”Average”, @StartDate = ”01/01/05”, @EndDate = ”01/20/05” or exec(sp_StepRunTime) Parameters :mad:

RunMode varchar(7), @Step nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @StartDate datetime, @EndDatedatetime, @Order nvarchar(100) Dependencies:fn_timeformat, logging enabled within the DTS package you want to track. Description: This procedure checks the DTS logs and returns that info in a readable format. The information returned helps in knowing which steps within a DTS package has errored out, what the error message was, and whether or not it is currently running. If it successfully ran then the total time it took for each individual step to complete. You also have the ability to select an average time of each step. The default sort order is set to show the packages that are running and those that have errored out in the Detail RunMode, i.e, ErrorCode asc, Package, StartTime, ETime. The default for the Average RunMode is ETime desc, Package, Step. The ElapsedTime column is varchar so sorting by it will not produce the desired results. Instead sort by ETime, which is ElapsedTime in seconds. Arguments: @RunMode (optional) – The default is ”Detail”; Also possible are ”Average” , ”Avg” , ”Help”, and ”?”. @Step (optional) – Any step name. If one is not provided then it becomes a wildcard selecting all. @StartDate (optional) – GETDATE() if not provided. @EndDate (optional) – @StartDate + 1 if not provided. @Order (optional) – Any sql compliant order statement. ' GO EXEC sp_configure ‘allow updates’, 0 EXEC sp_MS_upd_sysobj_category 2 RECONFIGURE WITH OVERRIDE GO

]]>