How to Stop SQL Server Agent Job while it is actively running?

How to Stop SQL Server Agent Job while it is actively running?

Try running sp_who2 and find the SPID and kill the process using KILL SPID with status only so that it will show the rollbackup completed as well.

Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.

I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was killed already and it will not kill the SPID also KILL WITH STATUSONLY will not help to track the progress of ROLLBACK issued by ROLLBACK TRAN. This was something new to him that KILL WITH STATUSONLY will not kill the SPID instead it only reports the progress. 

I thought of sharing this in a blog so that it will be useful for others who overlooked the KILL command.

Here is what BOL says:

KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

Lab:

Step 1: I ran a long running batch from SPID 52 

Step 2: Issued a KILL using the command KILL 52

Step 3: Here is what I observed from SP_WHO2 52:

SPID	Status	Login	HostName	BlkBy	DBName	Command
52	SUSPENDED	abc	def	.	microsoft	KILLED/ROLLBACK

Step 4: Here is what I got when I ran KILL 52 WITH STATUSONLY:

spid 52: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

Step 5: I tried to run the same statement from a different SPID 53 inside an explicit transaction and issued a ROLLBACK TRAN and this is the status when I ranSP_WHO2 53:

SPID	Status	Login	HostName	BlkBy	DBName	Command
53	ROLLBACK	abc	def	.	microsoft	ROLLBACK TRANSAC

Step 6: Here is the output of KILL 53 WITH STATUSONLY :

Msg 6120, Level 16, State 1, Line 1

Status report cannot be obtained. Rollback operation for Process ID 53 is not in progress.

So KILL WITH STATUS ONLY cannot be used to track the progress of  ROLLBACK issued by ROLLBACK TRAN. Hope this helps!

Reference: Sakthivel Chidambaram

SQL Server agent job running at this moment.

SQL Server Agent has a lot of great information stored in the system tables and the GUI provides a nice way of retrieving and viewing the data, but it does not always give you the data the way you would like it displayed.  From a high level it is nice to see the current status of all jobs and the last run status of the jobs, but what if you want to see additional information or the data displayed in a different format.  On a one by one basis you can get this information from the GUI, but it is not very easy to correlate this data across jobs or even across different time periods.

There are probably a million reasons why you could want to try and track down what job(s) might have been running at a particular time. Most of them, of course, will be due to various performance issues, side effects, and other problems that you might end up observing on one of your servers at a given time—and having the ability to quickly rule out a SQL Server Agent Job (or know that it was the culprit) is therefore, a godsend.

One of the things SQL Server doesn’t make all that easy is to know what SQL Agent jobs are running.

exec msdb.dbo.sp_help_job @execution_status=1

This little thing tells precisely which SQL Agent jobs are running at the moment, and that fills a great need in a lot of cases to all the DBA's

Source:mikehillwig

How to find percentage of a database backup job done in SQL Server?

Script to find percentage of a database backup job is done

SELECT percent_complete
 ,*
FROM sys.dm_exec_requests
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Elapsed time in Hours to complete the Job

SELECT command
 ,percent_complete
 ,'elapsed' = total_elapsed_time / 3600000.0
 ,'remaining' = estimated_completion_time / 3600000.0
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%'

Change the ownership of SQL Server agent jobs

The jobs may start failing when their AD accounts are removed. If the users have left it is better to change the job owner to a different account.
To ensure such issues wont happen, it is better to create a generic AD account with only necessary permissions and use it exclusively for this purpose.

List down all the jobs which are tied to the users and share it with all the respective project managers and technical architects and get a approval .
Decide on a common user account and create new one .

SSIS Package in SQL Server Agent Job Fails

Error:

Argument "xyz" for option "connection" is not valid.  The command line parameters are invalid.  The step failed.

Solution:

·         If your SQL Server is 64 bit

·         In SQL Server JOB-->Properties-->Steps--->

·         In the Execution Option Tab -- Check "Use 32 bit runtime"

·         In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if already checked.

·         Schedule the Job and run it.