December 29, 2008 at 12:26 pm
If you run a select for that name against
select * From msdb.dbo.sysjobs_view do you get results?
December 29, 2008 at 11:23 pm
I get the list of the scheduled jobs, which are working without problems.
In the properties of the job, under steps, advanced it is possible to enable logging to the sysjobstepslogs table in the msdb with the option append. Clicking on the button 'view' a log with details is produced. This part is also working fine. My problem is to clean up the log deleting entries older than a given date.
According to BOL (msdn) the procedure sp_delete_jobsteplog should do the work, but it doesn't. I can delete all entries but the argument @older_than is ignored (or it looks like to be ignored). Always according to BOL data type of job name and date are sysname and datetime. This is the reason I have defined variables with these data types, of course I want to set the date dynamically as for example select dateadd(m, -1, getdate()) to delete all entries older than 1 month.
December 30, 2008 at 3:34 am
I just tested it on my system and the parameter work fine for me.
Do you receive any error ?
Which version of SQL 2008 are you using?
[font="Verdana"]Markus Bohse[/font]
December 30, 2008 at 5:32 am
On our test server we have build 10.0.1600.22. I was thinking that eventually the date format could give some problems. When I run the query I don't get errors, but 0 rows affected. Which version are you running ? Were you able to delete just a part of the log ?
December 30, 2008 at 6:23 am
First thing, just try deleting any log using a delete against the view and passing the job name and the < date, see if that works. If there are no issues there then work through the proc code and see where the issue might lie.
December 30, 2008 at 11:29 am
Moreno (12/30/2008)
On our test server we have build 10.0.1600.22. I was thinking that eventually the date format could give some problems. When I run the query I don't get errors, but 0 rows affected. Which version are you running ? Were you able to delete just a part of the log ?
I have 10.0.1600 too and yes I was able to delete only the log entries before the @older_than date. I tested it with a fixed date and with a variable using DATEADD and both worked as expected.
The dateformat shouldn't be a problem when using the variable like you did. To be honest, I have no idea why it's not working for you.
[font="Verdana"]Markus Bohse[/font]
March 12, 2013 at 10:25 am
I believe the records you see when you "View History" of a job are in msdb.dbo.sysjobhistory.
October 25, 2017 at 9:17 am
Here is how I understand the function of sp_delete_jobsteplog.
The sysjobstepslogs table has one row for each job/step that you are logging. Every time the job runs, it concatenates the new log to the end of the "log" column for that step. Multiple logs are all stored as one concatenated VARCHAR(MAX).
When you run sp_delete_jobsteplog with the @older_than parameter, it does not parse through the "log" column and remove entries older than you want, as you might expect. What it does is looks at the "date_modified" column and deletes the entire row if that date is older than @older_than. So if the job runs once per hour and you want to delete everything older than 3 days, it will never delete anything because the "date_modified" will always be less than an hour old.
Here is the code from inside sp_delete_jobsteplog (SQL Server 2012 SP3).DELETE FROM msdb.dbo.sysjobstepslogs
WHERE (step_uid IN (SELECT DISTINCT step_uid
FROM msdb.dbo.sysjobsteps js, msdb.dbo.sysjobs_view jv
WHERE ( @job_id = jv.job_id )
AND (js.job_id = jv.job_id )
AND ((@step_id IS NULL) OR (@step_id = step_id))))
AND ((@older_than IS NULL) OR (date_modified < @older_than))
AND ((@larger_than IS NULL) OR (log_size > @larger_than))
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply