Tips
Accessing SQL Server Agent Data
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.
Analyzing SQL Agent Job and Job Step History in SQL Server
Almost every DBA configures SQL Agent Jobs for automation of repetitive tasks. This valuable facility works in the background in SQL Server and saves a lot of effort and manual rework. You may have a number of jobs scheduled and often there is the need to analyze the data to see which jobs are taking a long time or which job steps are taking a long time. SSMS offers some insight into your jobs, but there is no easy way to slice and dice the data to see what is going on for a particular job or at a particular time of the day. This tip shows you a way to query the system tables directly and bypass the GUI.
Auto alert for SQL Agent jobs when they are enabled or disabled
So it turns out the reason that some of your batch process never ran or why you do not have a current backup or why your transaction logs filed up is because someone or something disabled a SQL job for some reason and neglected to re-enable it. SQL jobs can be disabled for any number of reasons, but they can cause real problems when they go unnoticed as disabled (or enabled as the case may be). It is often the case in large DBA teams where multiple DBAs can administer the same SQL Servers and it is not apparent to one DBA the importance of some of the jobs to another DBA. In this tip I show how you can be alerted of these changes.
Auto notification for failover of a server and restart of SQL Server services
Our clustered Microsoft SQL Server environment hosts over 200 databases currently across all nodes. Whereas clustering technology has been around for quite some time now, many applications still are not cluster-aware. The problem we encountered was with a single application, provided and monitored by an outside company. This application is not cluster-aware and would fail whenever a failover occurred.
Since this solution is important, but not critical, we simply need to notify the vendor when a failover occurs. While we could employ Microsoft System Center or Microsoft Operations Manager to alert when this event does occur, I would have had to enlist resources across multiple teams to get this accomplished under that plan.
Automatic cleanup of SQL Server Agent scheduled jobs
One problem that people often have is a large number of SQL Agent jobs on their servers and they don't want to remove them because they are not exactly sure what the jobs are used for or why they were created. In most cases a lot of these jobs were created to run one time, but no one took the time to enter a description or better yet delete the job after it was run. So because of this you end up with a lot of extra scheduled jobs that you probably don't really need.
Built in SQL Server Agent Performance Reports in SQL Server 2005
Finding a good reporting mechanism for your SQL Server Agent can be tedious and time consuming. You can either write your own reporting application or choose a third-party solution. You may also have to install an instance of Reporting Services in your environment, depending on the needs of the application. SQL Server 2005 includes a number of built-in reports to assist you in troubleshooting and measuring performance. In this tip I will cover the Built in Performance Reports related to SQL Server Agent within SQL Server 2005.
Collect Performance Monitor Statistics for SQL Server Agent Jobs
One big issue with scheduled jobs is that they are often batch type jobs that take a significant amount of resources when they run. Another problem is that these jobs usually run during off hours such as nights or weekends, so there is no one to monitor the jobs resource usage. Without being around to monitor the job activity it is difficult to determine whether the jobs are running efficiently or not. Also, when batch jobs are first implemented they are often done with less data, so the resource needs are not that great, but over time the database grows and often the load for these batch processes grow. So how can you monitor the jobs resource usage while you are not there?
Configuring SQL Agent Jobs to Write to Windows Event Log
SQL agent jobs are not created with notifications enabled by default. You have several options on the Notifications page; you could send an email, a page, a net send, write to the application event log, or you could delete the job (which is hardly what I would call a 'notification'). A best practice for job management would be to write information to the application event log in the event of a job failure. Some monitoring tools and software, such as Operations Manager, relies on information being written to the event logs. If no information is being logged than it is possible you could be missing some valuable alerts. In this tip I will go over how to set this up and also a simple way to turn this on for all of your jobs.
Custom job categories to organize your SQL Agent jobs
Excellent organizational skills are one of the cornerstones to being a successful Database Administrator. Sometimes the tools that Microsoft provides us with do not give us complete control over the organization of our environment. Such is the case with organizing SQL Agent Jobs. In this tip we will look at how to organize jobs using custom job categories.
Custom sp_start_job to delay next task until SQL Agent Job has completed
The system stored procedure sp_start_job is a very useful T-SQL command that starts a SQL Agent job. The problem with this is that it starts the job and it doesn't wait until the job is finished and sometimes I want to start a job and wait until the job is finished and move on to another job or task that depends on the result of the previous job. In this tip, I show a custom stored procedure to handle this scenario.
Different ways to execute a SQL Agent job
Every database person might have come across the situation of maintenance tasks such as backing up of databases, re-indexing tables and other such tasks. We often schedule jobs for such tasks, so that they execute as per the set schedule. But there is sometimes the need for these tasks to be executed “On Demand”. This tip shows various ways of executing such tasks on demand by any user regardless of whether the person is technical or not.
Disabling or Enabling SQL Server Agent Jobs
As a portion of our backup and recovery process I need to disabled (or enable) all of our SQL Server Agent Jobs depending on the situation and the SQL Server instance. I know I can use the GUI to click through and make the changes, but on a number of our SQL Server instances we have 50+ jobs, so this is a monotonous task. Do you have any scripts to enable or disable all SQL Server Jobs? What about enabling or disabling all SQL Server Jobs by Job name or Job Category name?
Enable and Disable SQL Server Agent Jobs for Maintenance Mode
Sometimes when we conduct SQL Server Maintenance we need to shutdown the SQL Server Service and the SQL Server Agent once the maintenance is complete. In these circumstances, I typically manage (shut down and start) the SQL Server service first then start the SQL Server Agent later. However, I have several jobs that run every minute to every one hour and I do not want those jobs to start with SQL Server Agent. I want to selectively test some SQL Server Agent Jobs without starting all of the Jobs. Do you have any suggestions on how to accomplish this? Check out this tip for the solution.
Enabling a Fail Safe Operator in SQL Server Agent
I rely on Database Mail to send notifications for SQL Server Agent job failures. Is there a way to ensure that the mail will be delivered if something were to happen to the operators in msdb database? In this tip we cover how this can be setup.
Failed SQL Server Agent Jobs
With most, if not all organizations, executing processes during non business hours it is imperative to know if and when the backups fail. At a minimum, full backups are typically executed, but many organizations execute resource intensive batch processes which are critical to the core business for the subsequent day. With staff members stretched to meet numerous needs, validating that the jobs have executed successfully on a daily basis can be time consuming, but better than having the users notify IT. The typical solution to determine if a job fails is to notify an operator, but SQL Server Agent mail was not always as reliable as needed. As such, in this tip we will provide scripts to determine recent SQL Server Agent Job failures.
Generate SQL Agent Job Schedule Report
I am trying to come up with a list of Job Schedules, so I can make sure that my Jobs are staggered. Is there a way to query the system tables so I do not have to manually keep track of the schedules?
How to setup and manage SQL Server Agent shared job schedules
I have configured a few SQL Server agent jobs for different purposes on my database server. Some jobs are required to run at the same time. In other words I am required to configure the same schedule for multiple SQL Server agent jobs. In this tip we look at how to share job schedules.
How to store longer SQL Agent Job Step output messages
When working with SQL Agent jobs sometimes it is difficult to determine why a job failed or even exactly what occurred. For each job step SQL Server provides a message, but it is not always that easy to determine exactly what was occurring during that step. The default message that is saved in the job history is 1024 characters. In most cases this may be enough, but if you have a long running process you may need to store more than 1024 characters. Is there any way to get additional information within the job history to help troubleshoot issues as well as just knowing what occurred?
Managing SQL Server Agent Job History Log and SQL Server Error Log
When you install SQL Server, it has a few default configuration settings for SQL Server to manage logs. In some instances, where servers generate too much log info and you need to look at the log it takes a long time to pull up. Also there maybe cases where you want to keep more log info for some jobs versus others. For example, I have a job that runs every minute and it generates a lot of log history, but I only care about the data for the last 3 days. For another job that runs daily, I want to keep the log info for 15 days. I started with a process to pull all history and store in a separate user table, which is still very useful, but for day to day operations I was looking for an easier way to manage this for all of my SQL Server 2005
Missing SQL Server Agent History
SQL Server Agent is a useful tool to allow you to schedule reoccurring jobs to run on your server. Most of these jobs are usually maintenance type jobs or data loads, but this tool can be used to run any T-SQL code or even execute programs external to SQL Server. If you have jobs that run on a frequent basis or if you have a lot of jobs that are scheduled you may notice that there is not much historical information stored when you view job history, so what can be done to keep more historical run information?
Querying SQL Server Agent Job Information
I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.
Reassign Job Ownership for All Jobs on All SQL Servers
I'm leaving my job as the primary DBA after 10 years. I have a support DBA that will be taking over the primary role. SQL Server creates objects and assigns ownership rights in many cases under the context of the current login. That assuredly means there are SQL Agent jobs owned by my login across all the servers I administer and I need to identify (and more importantly change) them before I depart and my Active Directory login is removed. I support 80+ SQL Server instances and am dreading having to do this. Is there an easier way than connecting to each instance in Object Explorer and examining each job for ownership? Read on to find out how.
Retaining SQL Server Job History
I have a set of SQL Server Jobs (transaction log backups, notification of business rules, etc.) that run every 5 minutes just about 24 hours a day. I seem to lose my SQL Server Job History in a few days and need this type of data to troubleshoot specific situations. It always seems like I lose this SQL Server Job related data when I need it most. I am trying to find the SQL Server Job that someone could have setup to delete from these tables and I only see the Jobs that my team created. I have gone over these with a fine tooth comb and none of these delete data from the SQL Server Job related tables in the MSDB database. What exactly is deleting these entries? Do I need to start backing up the MSDB database a few times a day to trouble
Running a SSIS Package from SQL Server Agent Using a Proxy Account
Recently when I created a SQL Server Agent job to execute a SSIS package, it failed with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.". What is this exception about? What causes it and how do I resolve it? In this tip we look at how to resolve this using a proxy account.
Running SQL Server Agent with a least privilege service account
For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Script to determine verbose logging options of SQL Server Jobs
We have many SQL Server instances in our enterprise with several hundreds of jobs across all servers created by many resources from the past and present each with a unique style of verbose logging options. Now, we are standardizing the verbose logging options to always append to a file and is there a way to figure out the current logging options of each job by a script instead of manually looking up all the job steps? In this tip I present a simple script to figure this out for all jobs.
Setting Up Alerts for All SQL Server Agent Jobs
One of the things you can do with SQL Server 2000 is setup Operators to be notified when there is a job failure or an alert has been triggered that should notify your DBA team. The problem with setting up notifications for jobs is that you need to edit each and every job in order to turn on notifications. If you only have a few jobs this is not that much work, but if you have several jobs and have several servers making this change may take some time. In addition, if you are using Maintenance Plans there is not a way to automatically setup notifications for the jobs, so you would have to do this manually as well. Luckily there is a stored procedure that can help make this task easier.
SQL Agent Error the CmdExec subsystem failed to load
The other day we ran into this error when running a SQL Agent job: Unable to start execution of step 1 (reason: The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed. In this tip we cover what was done to solve this problem.
SQL Server Agent Fixed Database Roles
One mantra across the industry is the need to fine tune security for all infrastructure components. In SQL Server 2005, Microsoft responded with an almost overwhelming number of granular security rights. One of these changes is with SQL Server Agent which now has 3 roles which reside in MSDB database offering a new set of opportunities to fine tune access to working with Jobs.
SQL Server Agent Job Management
At our organization we have been using SQL Server Agent since SQL Server 2000. We typically setup the Job and that is about it. If we need to make a change we do that and do not think twice about it. On some of our servers we have hundreds of jobs, so in this circumstance, we have lost track of what many of them do. Recently, we have been getting asked a number of questions from our management and Development team about when changes were made, scheduling options, dependencies, etc. Can you give me any insight into how to best leverage SQL Server Agent?
SQL Server Agent Job Management Scripts
In a previous tip, the tip reviewed several steps for a better and improved environment for scheduled jobs. I am totally convinced with the recommendations and want to implement these for my scheduled jobs. To make this easier, I want to create a template so I can produce a suitable and reliable way of updates for my production server. In this tip I provide a step by step exercise to perform on a test server to prepare the customized and tested scripts to run on the production server.
SQL Server Agent Job Ownership
I am making a number of SQL Server security changes at the server and database level all at once, which is a problem in and of itself. I will be changing SQL Server service accounts, removing rights to BUILTIN\Administrators group, limiting specific user rights, moving to group based security, changing SQL Server Agent Job owners, etc. Nevertheless, I want to prevent as many issues as possible with so many simultaneous changes. One issue (of many) that I am concerned about is related to SQL Server Job ownership because our business is dependent on specific Jobs that need to run during the night during a very short window. Ensuring that the SQL Server Jobs will run as expected is mission critical for us every day...
SQL Server Agent Jobs without an Operator
I work in a fairly large and unwieldy SQL Server shop. Our environment is the wild west. I have DBAs and Developers on my team that create SQL Server Agent Jobs all of the time and there is never an operator setup to email anyone based on a failure condition. I know the right solution here is to "tame the wild west" by locking down the environment and setting up a change management process, but I think that is a topic for another day. Can you give me some idea of how to identify the new recently created SQL Server Agent Jobs and Jobs without an operator setup to email us for a failure condition? I would like to receive emails when either of these conditions occur. Check out the solution to this tip to learn how.
SQL Server Agent Proxies
With so many security changes in SQL Server 2005, how did the security changes affect SQL Server Agent? I have heard about SQL Server Agent Proxies, but I am not too familiar with them. How do I set them up? What dependencies do they have? What is the security benefit?
SQL Server Integration Services Package Scheduling with SQL Agent
With the new features in SQL Server 2005 Integration Services, what are the related impacts to SQL Server 2005 Agent? Do I still need to build a dtsrun command (SQL Server 2000) to execute an SSIS Package in an automated manner? What insight do I have into the internal SSIS objects and some of the advanced features?
SQL Server Multi Server Administration
I have multiple SQL Servers that need the exact same SQL Server Agent Job created. I know I can script a SQL Server Agent Job out and run the T-SQL script on multiple servers or bounce from server to server creating the SQL Server Agent Jobs, but isn't there an easier way? Check out this tip to learn more.
Verbose SQL Server Agent Logging
How often have you reviewed a SQL Server Agent Job’s history to determine why a job failed only to be confronted with a minimal level of detail? When a job failure occurs it is imperative to be able to quickly identify the root cause in order to determine the correct remediation. As such, what are some of the options to consolidate the logging on a per job basis? How can I setup this functionality for my key SQL Server Agent Jobs?
Top 10
Querying SQL Server Agent Job Information
I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.
Different ways to execute a SQL Agent job
Every database person might have come across the situation of maintenance tasks such as backing up of databases, re-indexing tables and other such tasks. We often schedule jobs for such tasks, so that they execute as per the set schedule. But there is sometimes the need for these tasks to be executed “On Demand”. This tip shows various ways of executing such tasks on demand by any user regardless of whether the person is technical or not.
Running a SSIS Package from SQL Server Agent Using a Proxy Account
Recently when I created a SQL Server Agent job to execute a SSIS package, it failed with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.". What is this exception about? What causes it and how do I resolve it? In this tip we look at how to resolve this using a proxy account.
Generate SQL Agent Job Schedule Report
I am trying to come up with a list of Job Schedules, so I can make sure that my Jobs are staggered. Is there a way to query the system tables so I do not have to manually keep track of the schedules?
SQL Server Agent Job Ownership
I am making a number of SQL Server security changes at the server and database level all at once, which is a problem in and of itself. I will be changing SQL Server service accounts, removing rights to BUILTIN\Administrators group, limiting specific user rights, moving to group based security, changing SQL Server Agent Job owners, etc. Nevertheless, I want to prevent as many issues as possible with so many simultaneous changes. One issue (of many) that I am concerned about is related to SQL Server Job ownership because our business is dependent on specific Jobs that need to run during the night during a very short window. Ensuring that the SQL Server Jobs will run as expected is mission critical for us every day...
Failed SQL Server Agent Jobs
With most, if not all organizations, executing processes during non business hours it is imperative to know if and when the backups fail. At a minimum, full backups are typically executed, but many organizations execute resource intensive batch processes which are critical to the core business for the subsequent day. With staff members stretched to meet numerous needs, validating that the jobs have executed successfully on a daily basis can be time consuming, but better than having the users notify IT. The typical solution to determine if a job fails is to notify an operator, but SQL Server Agent mail was not always as reliable as needed. As such, in this tip we will provide scripts to determine recent SQL Server Agent Job failures.
SQL Server Agent Job Management Scripts
In a previous tip, the tip reviewed several steps for a better and improved environment for scheduled jobs. I am totally convinced with the recommendations and want to implement these for my scheduled jobs. To make this easier, I want to create a template so I can produce a suitable and reliable way of updates for my production server. In this tip I provide a step by step exercise to perform on a test server to prepare the customized and tested scripts to run on the production server.
Running SQL Server Agent with a least privilege service account
For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Disabling or Enabling SQL Server Agent Jobs
As a portion of our backup and recovery process I need to disabled (or enable) all of our SQL Server Agent Jobs depending on the situation and the SQL Server instance. I know I can use the GUI to click through and make the changes, but on a number of our SQL Server instances we have 50+ jobs, so this is a monotonous task. Do you have any scripts to enable or disable all SQL Server Jobs? What about enabling or disabling all SQL Server Jobs by Job name or Job Category name?
Analyzing SQL Agent Job and Job Step History in SQL Server
Almost every DBA configures SQL Agent Jobs for automation of repetitive tasks. This valuable facility works in the background in SQL Server and saves a lot of effort and manual rework. You may have a number of jobs scheduled and often there is the need to analyze the data to see which jobs are taking a long time or which job steps are taking a long time. SSMS offers some insight into your jobs, but there is no easy way to slice and dice the data to see what is going on for a particular job or at a particular time of the day. This tip shows you a way to query the system tables directly and bypass the GUI.
Last 10
Querying SQL Server Agent Job Information
I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.
SQL Agent Error the CmdExec subsystem failed to load
The other day we ran into this error when running a SQL Agent job: Unable to start execution of step 1 (reason: The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed. In this tip we cover what was done to solve this problem.
SQL Server Multi Server Administration
I have multiple SQL Servers that need the exact same SQL Server Agent Job created. I know I can script a SQL Server Agent Job out and run the T-SQL script on multiple servers or bounce from server to server creating the SQL Server Agent Jobs, but isn't there an easier way? Check out this tip to learn more.
SQL Server Agent Jobs without an Operator
I work in a fairly large and unwieldy SQL Server shop. Our environment is the wild west. I have DBAs and Developers on my team that create SQL Server Agent Jobs all of the time and there is never an operator setup to email anyone based on a failure condition. I know the right solution here is to "tame the wild west" by locking down the environment and setting up a change management process, but I think that is a topic for another day. Can you give me some idea of how to identify the new recently created SQL Server Agent Jobs and Jobs without an operator setup to email us for a failure condition? I would like to receive emails when either of these conditions occur. Check out the solution to this tip to learn how.
Running SQL Server Agent with a least privilege service account
For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Managing SQL Server Agent Job History Log and SQL Server Error Log
When you install SQL Server, it has a few default configuration settings for SQL Server to manage logs. In some instances, where servers generate too much log info and you need to look at the log it takes a long time to pull up. Also there maybe cases where you want to keep more log info for some jobs versus others. For example, I have a job that runs every minute and it generates a lot of log history, but I only care about the data for the last 3 days. For another job that runs daily, I want to keep the log info for 15 days. I started with a process to pull all history and store in a separate user table, which is still very useful, but for day to day operations I was looking for an easier way to manage this for all of my SQL Server 2005
How to setup and manage SQL Server Agent shared job schedules
I have configured a few SQL Server agent jobs for different purposes on my database server. Some jobs are required to run at the same time. In other words I am required to configure the same schedule for multiple SQL Server agent jobs. In this tip we look at how to share job schedules.
Custom sp_start_job to delay next task until SQL Agent Job has completed
The system stored procedure sp_start_job is a very useful T-SQL command that starts a SQL Agent job. The problem with this is that it starts the job and it doesn't wait until the job is finished and sometimes I want to start a job and wait until the job is finished and move on to another job or task that depends on the result of the previous job. In this tip, I show a custom stored procedure to handle this scenario.
Running a SSIS Package from SQL Server Agent Using a Proxy Account
Recently when I created a SQL Server Agent job to execute a SSIS package, it failed with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.". What is this exception about? What causes it and how do I resolve it? In this tip we look at how to resolve this using a proxy account.
SQL Server Agent Job Management Scripts
In a previous tip, the tip reviewed several steps for a better and improved environment for scheduled jobs. I am totally convinced with the recommendations and want to implement these for my scheduled jobs. To make this easier, I want to create a template so I can produce a suitable and reliable way of updates for my production server. In this tip I provide a step by step exercise to perform on a test server to prepare the customized and tested scripts to run on the production server.
