Disable then Enable SQL Agent Jobs

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected

 

Get and Kill Executing Processes of Job

This query will identify the current SQL processes or executions which belong to a specific job.

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking.  The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running.  To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.

 

2016 Learning Goals

MCSA & MCSE SQL Server Certification
  • MCSA: SQL Server
  • MCSE: Data Platform
    • 70-464 – Developing Microsoft SQL Server Databases
    • 70-465 – Designing Solutions for SQL Server
  • MCSE: Business Intelligence
    • 70-466 – Implementing Data Models and Reports with Microsoft SQL Server
    • 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server
Neural Networks and Machine Learning

Training Sources:

Other Interests
  • Internet of Things Development
  • Mobile Application Development
  • Python
  • Ruby on Rails
  • Node.js, MongoDB, and Express
  • Quartz Composer
  • Actuary Exams
Learning Websites