December 29, 2008 at 9:34 am
Hi, in our organisation we normally set up maintenance plans for backups and so on. As we cannot deploy packages from one central server, because of networking restriction I decided to write store procedure to replace the maintenance plan. To track failures I would like to have a log. With maintenance plans I let produce a log file. In the new job I decided to let write the log in the sysjobstepslogs table with the append option. From time to time of course I would like to delete the old entries. No problem in BOL I found the sp_delete_jobsteplog. The procedure accept also a datetime parameter to delete entries older than a custom date.
So I tried as follow
declare @date datetime
declare @job sysname
set @job = 'myjobname
set @date = (select dateadd(d, -3, getdate()))
exec dbo.sp_delete_jobsteplog
@job_name = @job,
@older_than = @date
go
It doesn't work. Even if I set date and name like in BOL instead of using variable....no chance. If I run the procedure just with the job name then it will delete everything.:pinch:
I don't know if I am doing something wrong, has anybody tried it out ?
Thanks and Happy New Year :w00t:
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply