SQL agency history setting

Dear all,

did you all tried the case when you check the box there and set weeks of removal, and you reopen it again, the check box still uncheck?

SQL agent history
how to solve it ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Darn, looks like there's a bug in SQL and please see details at link below:
http://www.sqlservercentral.com/Forums/Topic1267858-1550-1.aspx

this is the actual sql issue:
https://connect.microsoft.com/SQLServer/feedback/details/485232/kjtap-sap-sql-server-agent-remove-agent-history-cannot-be-activated


This article will show you workaround to the issue by creating your own SQL job to do the same ting where you will have full control of the code/schedule.

https://www.mssqltips.com/sqlservertip/2200/managing-sql-server-agent-job-history-log-and-sql-server-error-log/
marrowyungSenior Technical architecture (Data)Author Commented:
so by this:
"
I haven't worried about setting that on my systems, because I use the History Cleanup task in maintenance plans to take care of the history. If you don't like maintenance plans, you can always create your own job to execute the history cleanup procedure."


and how can we control the number of date keep in the SQL job log history :

EXEC dbo.sp_purge_jobhistory ;
GO
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's happen when you click on the Script option? it should give you the correct script so you can run it manually, right? Try to run it and check want happens next.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

marrowyungSenior Technical architecture (Data)Author Commented:
"What's happen when you click on the Script option? it "

what is that sorry ? what script option it is ?

the point is right now I can only use the limit sizes of job history log to control it but it seems not that good, I want to keep 8 weeks of history.

the History Cleanup task and History Cleanup task  can override setting on the SQL agent properties page ?

or this links can help:

https://www.mssqltips.com/sqlservertip/2200/managing-sql-server-agent-job-history-log-and-sql-server-error-log/
marrowyungSenior Technical architecture (Data)Author Commented:
from that link:

https://www.mssqltips.com/sqlservertip/2200/managing-sql-server-agent-job-history-log-and-sql-server-error-log/

I am not sure why we need to do this T-SQL everyday ?

DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@OldestDate@

Open in new window


just because the SP msdb.dbo.sp_purge_jobhistory is going to do a real time purge so we need to tell it everyday (at that time only each day ?)

and this one:

DECLARE @OldestDate datetime
DECLARE @JobName varchar(256)
-- Keep Last 3 days
SET @OldestDate = GETDATE()-3
SET @JobName = 'Pay Roll Over'
EXEC msdb.dbo.sp_purge_jobhistory 
 @job_name=@JobName, 
 @oldest_date=@OldestDate

Open in new window


as another steps just make sure that only the job 'Pay Roll Over' keep the last 3 days history but not the rest, the rest keep 15 days ?
marrowyungSenior Technical architecture (Data)Author Commented:
when I run this :

DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@OldestDate@

Open in new window


SSMS return this :

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ''.


what the reason is ?

or we should simply manage the "HistoryRetentionInDays" from the  Policy Management "HistoryRetentionInDays" property ? which the syspolicy_purge_history will look at ?  so set 56 if we need to keep 8 weeks of any SQL job history for any SQL jobs?
Vitor MontalvãoMSSQL Senior EngineerCommented:
what is that sorry ? what script option it is ?
In SQL Agent Properties / History, you have a Script button and an Help button on top. Can you see both buttons?

DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@OldestDate@


Last '@' isn't necessary. It's a typo? Should be:
DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@OldestDate

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
""In SQL Agent Properties / History, you have a Script button and an Help button on top. Can you see both buttons?
"

yes, I done that !

"Last '@' isn't necessary. It's a typo? Should be:"

yeah, you are right, I just copy and paste the script from the link.

one question, I get an English warning from an American that yeah is not polite to send in the email, is that right?

please also help to answer all the update I have above.
Vitor MontalvãoMSSQL Senior EngineerCommented:
one question, I get an English warning from an American that yeah is not polite to send in the email, is that right?
I'm not a native English speaker so I don't know that but I'm ok with that. :)
marrowyungSenior Technical architecture (Data)Author Commented:
ok. .just want to have some fun ! An America told me that and I am not sure why !

do you have any idea on the rest of the post I post above?

"Your Comment
 on 2015-10-28 at 15:47:33ID: 41133166 "

and
Your Comment
 on 2015-10-28 at 15:27:32ID: 41133067
Vitor MontalvãoMSSQL Senior EngineerCommented:
did you all tried the case when you check the box there and set weeks of removal, and you reopen it again, the check box still uncheck?
Is how it works. This is for a one time run only. I asked you to click the script button so you can verify that. It runs EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2015-09-04T11:56:46' where the parameter is a fixed date and not something like DATEADD(week,-8,GETDATE()).
If you want you can create a job that runs daily to execute the following code:
DECLARE @OldestDate VARCHAR(20)=CAST(DATEADD(week,-8,GETDATE()) AS VARCHAR)

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@OldestDate

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
"Is how it works. This is for a one time run only. I asked you to click the script button so you can verify that. It runs EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2015-09-04T11:56:46' where the parameter is a fixed date and not something like DATEADD(week,-8,GETDATE())."

surprise, very correct vincent, I keep finding and find a lot of people post sth else.

my example, I script out that for keeping only 4 weeks of history:

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2015-10-06T14:59:59'
GO

Open in new window




"If you want you can create a job that runs daily to execute the following code:"

but is there any default setting to make it clean up the existing history log for any record older than x days/ y weeks, I am worrying about by this, I do it twice.

I found this, this is worrying me now as I once I change the setting to 8 weeks, I can't click ok to confirm, I am already sysadmin.

so this job can change my setting if I do this :

history-log-maintenance-console.jpg
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not good with the Maintenance Plan that comes with SSMS. I just don't like it and so never used it.
If you see the sp_set_sqlagent_properties parameters there is none related to the time but maximum rows only. Isn't that enough to let SQL Agent handle with the the job history table? I would stick with that as I always did.
SQLAgent_properties.PNG
marrowyungSenior Technical architecture (Data)Author Commented:
"I'm not good with the Maintenance Plan that comes with SSMS. I just don't like it and so never used it."

I am not sure if this job, syspolicy_purge_history, which exec this  msdb.dbo.sp_syspolicy_purge_history, which rans everyday, will over ride the change by your script, this is what I am worrying about.

from the internet, the job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management. If I right click the policy managment and see that value, it is 0 and which means the system don't purge log, but I can't see why SQL job log always can't last long.

for your script, should I enlarge the size of job history log before implementing your script?

log history setting.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am not sure if this job, syspolicy_purge_history, which exec this  msdb.dbo.sp_syspolicy_purge_history, which rans everyday, will over ride the change by your script, this is what I am worrying about.
Why? sp_syspolicy_purge_history is different from sp_purge_jobhistory. The first one purge policies history and the last one purge jobs history.

for your script, should I enlarge the size of job history log before implementing your script?
Depends on if your job history configuration will handle 8 weeks or not. You'll need to verify it by yourself by querying the table and check if the oldest record is older than 8 weeks or not.
marrowyungSenior Technical architecture (Data)Author Commented:
"You'll need to verify it by yourself by querying the table and check if the oldest record is older than 8 weeks or not."

sorry, querying which table ? or you are talking about keep checking the log history and see how many days it can show the record?

so this mean if it can't, your script will have no effect, right?
Vitor MontalvãoMSSQL Senior EngineerCommented:
sorry, querying which table ?
sysjobhistory, the one from your other related question.

so this mean if it can't, your script will have no effect, right
My script was per your request: "I want to keep 8 weeks of history.". You can obviously configure it the @OldestDate variable to the oldest date of your preference.
But I think the current log history configuration that you have will handle 8 weeks unlesse you're running the jobs every single minute.
marrowyungSenior Technical architecture (Data)Author Commented:
"unlesse you're running the jobs every single minute. "

what is that mean ? this will make log full very soon and msdb can't keep it ?

"But I think the current log history configuration that you have will handle 8 weeks "

from the policy management, historyretention setting is 0, so it must be unlimited but still controlled by the log size ?:

log history
so if log size is large enough, your script work perfectly ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
from the policy management, historyretention setting is 0, so it must be unlimited but still controlled by the log size ?:
Yes, that's correct.

so if log size is large enough, your script work perfectly ?
Yes, it should run ok.
marrowyungSenior Technical architecture (Data)Author Commented:
AHAH, I will start testing it then. I trust your script but SQL server has configuration in diff place which confuse me.

why 3 diff setting on the same thing, agree?
marrowyungSenior Technical architecture (Data)Author Commented:
you mean log size of 400000 can handle 8 weeks?
marrowyungSenior Technical architecture (Data)Author Commented:
"Why? sp_syspolicy_purge_history is different from sp_purge_jobhistory. The first one purge policies history and the last one purge jobs history."

any setting control how many days or job history log  sp_purge_jobhistory will purge ?

by this: http://www.sqlservergeeks.com/sql-server-sp-purge-jobhistory-to-delete-job-history/

seems if we don't execute that SP, the log is not going to clean up?

"exec msdb.dbo.sp_set_sqlagent_properties;
"
I run this and it returns nothing, how I return the current retention setting ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
why 3 diff setting on the same thing, agree?
Which 3 different settings?

you mean log size of 400000 can handle 8 weeks?
I would say yes but it's you that need to know that. It's simple calculation. For example, in an instance with 10 jobs and each job has only one step and runs once by hour, the calculation should be:
10 jobs x 1 step x 24 hours x 1 run by hour x 7 days x 8 weeks = 13 440 rows

Now for example if in average the jobs has 2 steps and runs every minute then:
10 jobs x 2 steps x 24 hours x 60 runs by hour x 7 days x 8 weeks = 1 612 800 rows


any setting control how many days or job history log  sp_purge_jobhistory will purge ?
The parameter that SP receives is the oldest data to keep.


I run this and it returns nothing, how I return the current retention setting ?
Of course nothing returned. Is a SP to SET and not to GET :)
Try sp_get_sqlagent_properties

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
sorry, focus on replication and deployment stuff in my new company.

"Which 3 different settings?"

1) retention policy.
2) job history log size
3) remove agent history

might be more...

"Now for example if in average the jobs has 2 steps and runs every minute then:
 10 jobs x 2 steps x 24 hours x 60 runs by hour x 7 days x 8 weeks = 1 612 800 rows
"

yeah, but how can we know the "Maximum job history log size (in rows)" ? actually it should be the much important one as it determine how long the period of the log it can save, right?

are we only focus on "Maximum job history rows per job" only ? it start to make any sense if we only focus on this as we only care how many rows we are going to store?

So now in any REAL world DB there are many jobs, we only focus on the job which run the most frequently and count the number of rows for that?

"Of course nothing returned. Is a SP to SET and not to GET :)
 Try sp_get_sqlagent_properties "

sorry, I just want to know how to clean up the job history after a certain period, if I want to make sure that all job only keep history of 4 months, what should I do ?

DECLARE @OldestDate VARCHAR(20)=CAST(DATEADD(month,-4,GETDATE()) AS VARCHAR)

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@OldestDate

Open in new window



?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry but I just missed your last comments.
Didn't you have a similar question that was closed?
Do you still need help with this one?
marrowyungSenior Technical architecture (Data)Author Commented:
I think this is the final question:

yeah, but how can we know the "Maximum job history log size (in rows)" ? actually it should be the much important one as it determine how long the period of the log it can save, right?

 are we only focus on "Maximum job history rows per job" only ? it start to make any sense if we only focus on this as we only care how many rows we are going to store?

sorry, I just want to know how to clean up the job history after a certain period, if I want to make sure that all job only keep history of 4 months, what should I do ?

DECLARE @OldestDate VARCHAR(20)=CAST(DATEADD(month,-4,GETDATE()) AS VARCHAR)

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@OldestDate

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
I feel that I'm repeating here. Maybe I wasn't clear on my previous comments but I don't know how to explain this in other way. I even added examples:
"For example, in an instance with 10 jobs and each job has only one step and runs once by hour, the calculation should be:
 10 jobs x 1 step x 24 hours x 1 run by hour x 7 days x 8 weeks = 13 440 rows

 Now for example if in average the jobs has 2 steps and runs every minute then:
 10 jobs x 2 steps x 24 hours x 60 runs by hour x 7 days x 8 weeks = 1 612 800 rows"



if I want to make sure that all job only keep history of 4 months, what should I do ?
Be careful. This is not by job history but for ALL job histories. Depending on the scheduling of each job some of them may and will have more rows stored but the example you gave it will purge the table and keep only the last 4 months of job histories.
marrowyungSenior Technical architecture (Data)Author Commented:
"Be careful. This is not by job history but for ALL job histories."

yeah, can imagine that.

"the example you gave it will purge the table and keep only the last 4 months of job histories. "


so it control by time, no number of row and it make sense.
marrowyungSenior Technical architecture (Data)Author Commented:
tks,

I might come back to this question if I need. seems some procedure clean up history for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.