December 11, 2008 at 10:33 am
I have a logging table for SSIS packages that I want to examine for repeated failures.
I have a column named 'status' that has a controlled vocabulary of 'STARTED|SUCCESS|FAILURE'
so basically, I want a query that shows me what jobs have failed X out of the last Y times.
We'll say 1 out of 3 for the sake of example.
There are some datetimes as logged to the table by SSIS, which would be how you know it is the N most recent times.
Another catch is that several types of jobs are dumped to this same logfile each having different steps...
so it would need some grouping on packagename and stepnames that failed 1 out of the last 3 times (for example).
The GUIDs are to join to another table, and are unique for each time the package ran.
The architecture is set, so the below table structure is what you have to work with.
There should be a set-based way to get this information and you geniuses out there can figure it out, I know it! 😀
Thank you for any help you can offer, even hints.
Here's some setup code for you
~BOT
CREATE TABLE dbo.logtable (
[id] [int] PRIMARY KEY,
[packagename] [varchar](100) NOT NULL,
[stepname] [varchar](512) NULL,
[executionguid] [uniqueidentifier] NOT NULL,
[dbname] [sysname] NULL,
[status] [varchar](100) NOT NULL,
[stepstarttime] [smalldatetime] NOT NULL,
[stependtime] [smalldatetime] NOT NULL,
[reported] [bit] NOT NULL,
[message] [nvarchar](2048) NULL
)
INSERT dbo.logtable
SELECT '775' AS [id], 'MAINT_LOGBACKUP 60 Minute' AS [packagename], 'TRAN LOG BACKUP' AS [stepname], 'CEC76836-1B29-4733-AACF-366495F85B02' AS [executionguid], 'fakedb2' AS [dbname], 'FAILURE' AS [status], '2008-12-11 00:00:00' AS [stepstarttime], '2008-12-11 00:00:00' AS [stependtime], '0' AS [reported], 'database set to simple recovery' AS [message] UNION ALL
SELECT '776' AS [id], 'MAINT_LOGBACKUP 60 Minute' AS [packagename], 'TRAN LOG BACKUP' AS [stepname], 'CEC76836-1B29-4733-AACF-366495F85B03' AS [executionguid], 'fakedb2' AS [dbname], 'FAILURE' AS [status], '2008-12-11 01:00:00' AS [stepstarttime], '2008-12-11 01:00:00' AS [stependtime], '0' AS [reported], 'database set to simple recovery' AS [message] UNION ALL
SELECT '777' AS [id], 'MAINT_OPTIMIZE' AS [packagename], 'OPTIMIZE INDEXES' AS [stepname], '20370053-67CA-4DF0-A6F7-C5E1927CAE20' AS [executionguid], 'fakedb1' AS [dbname], 'SUCCESS' AS [status], '2008-12-11 00:00:00' AS [stepstarttime], '2008-12-11 00:00:00' AS [stependtime], '0' AS [reported], NULL AS [message] UNION ALL
SELECT '778' AS [id], 'MAINT_LOGBACKUP 60 Minute' AS [packagename], 'TRAN LOG BACKUP' AS [stepname], 'CEC76836-1B29-4733-AACF-366495F85B05' AS [executionguid], 'fakedb2' AS [dbname], 'FAILURE' AS [status], '2008-12-11 02:00:00' AS [stepstarttime], '2008-12-11 02:00:00' AS [stependtime], '0' AS [reported], 'database set to simple recovery' AS [message] UNION ALL
SELECT '779' AS [id], 'MAINT_LOGBACKUP 30 Minute' AS [packagename], 'TRAN LOG BACKUP' AS [stepname], 'D993BFEC-8AA2-47AD-A6A2-84AF14CDC38F' AS [executionguid], 'fakedb3' AS [dbname], 'SUCCESS' AS [status], '2008-12-11 00:00:00' AS [stepstarttime], '2008-12-11 00:00:00' AS [stependtime], '0' AS [reported], NULL AS [message]
Craig Outcalt
December 11, 2008 at 10:44 am
This should be easily solved by using a running totals method. If you can *add* a column to the table and change the clustered index, you can do it without temp tables.
(I know you said the structure couldn't change, but this is only additive, not doing anything to what's already there). Either way, give this a read, let us know if you have any questions
with the implementation.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Hint:
Clustered index on PackageName, StepName, StepEndTime
December 11, 2008 at 11:54 am
Thanks for your help, but I figured it out.
It uses a CTE and row_number() over().
with temp1 as (
select packagename, stepname, dbname, stepstarttime, row_number()
over( partition by packagename, stepname, dbname order by stepstarttime desc) as rn, status
from maint.packagesteplog
where stepname <> 'CHECK FOR WORK TO DO'
)
select * from temp1
where rn <= 3 and status = 'FAILURE'
order by packagename, stepname, dbname, rn
Craig Outcalt
December 11, 2008 at 3:02 pm
I keep forgetting about the new 2K5 functions. :blush:
Same concept, better implementation. Thanks for the feedback.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply