Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Tracing sql job

Posted on 2012-04-04
3
Medium Priority
?
269 Views
Last Modified: 2012-04-04
I have scheduled a job that has a delete strategy based on a time-stamp column. It is working fine.

But, I would like to know how many rows were affected/deleted in a single run. Is it possible to trace??

I see the duration was 00:00:00 but it doesn't mean that no rows were affected...
0
Comment
Question by:w3brang3r
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 1200 total points
ID: 37806500
As per my knowledge by default we don't have that option, but you can use @@ROWCOUNT and insert the results to a new table, this method is just like logging.

E.g

DECLARE @Count INT

DELETE FROM myTable Where MyColumn>getdate()

SET @count = @@ROWCOUNT

-- Insert into log table
INSERT INTO LogMyTableDelete(JobRunTime, @count)
SELECT  GETDATE(),@Count
0
 

Author Comment

by:w3brang3r
ID: 37806803
But I'm trying to trace a job that ran last night.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 37806905
Sorry, There is no way unless you have any audit or dml trigger on the tale.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question