Can I view the value in SQL job on what value it executed?

Question

1.00/5 (3 votes)

See more:

Hi,I created the jobs for reset the table log value to 0 , and i schedule the job for 6 hours once and the value is 6000 (log value). Is this possible to known about the table value in every 6 hours on what value it reset the table.

What I have tried:

I created the job and set the time and value as 6 hours once and 6ooo values.

Posted 21-Jun-16 23:31pm

Xaavier

Updated 22-Jun-16 20:35pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ben J. Boyle · Accepted Answer · 2016-06-22T02:54:00

Solution 1

If you have a job that runs to reset the value, why not add an extra step to the job to save the value to another table before resetting it? Store the current timestamp and the value and you can query to your heart's content.

Posted 22-Jun-16 2:54am

Ben J. Boyle

Comments

Xaavier 23-Jun-16 2:35am

thank you Ben,

so this is my query "update aq_pr_log set aa_exec_count = 0 where aa_exec_count >6000;
it contain upto 100 records . how can i add another job and store the values and timestamp.. please give me some sample query..

thanks...

Ben J. Boyle 23-Jun-16 9:15am

Something like this.

Create a table:

create table aq_pr_audit (
aq_pr_id int,
reset_timestamp datetime,
aa_exec_count int
)

Then alter your scheduled job to run this (you could also put it in a stored proc and execute that in the schedule):

BEGIN TRANSACTION RESETLOG

insert into aq_pr_audit (aq_pr_id, reset_timestamp, aa_exec_count)
select aq_pr_id, getdate(), aa_exec_count
from aq_pr_log
where aa_exec_count >6000

update aq_pr_log set aa_exec_count = 0 where aa_exec_count >6000;

COMMIT TRANSACTION RESETLOG

Xaavier 24-Jun-16 2:45am

Thanks Ben ...

Xaavier 28-Jun-16 6:16am

Is there any possible way without creating the table .... is there any option...

Ben J. Boyle 28-Jun-16 11:57am

You could write to the SQL server log, perhaps. See xp_logevent https://msdn.microsoft.com/en-us/library/ms186244.aspx

You could also send en email with the data in it. It really depends on how you need to see the information, and if it's something you want to be able to look at further down the line.