Problem: One or more of your operators are not getting notifications from SQL Server agent alerts or jobs. Solution: This could be for a number of reasons, but more than likely it is because an operator or alert is disabled, or both. Let’s use the query below and determine what is causing the underlying problem …
T-SQL script showing why SQL Server operators may not receive notifications Read More »
Problem: Sometimes SQL Server agent jobs do not work as expected due to either syntax or logical errors. In this blog post, I attempt to show you how to identify SQL Server agent jobs that do not provide the desired outcome as a result of logic errors, that is, jobs in which some steps ‘on …
Identify SQL Server agent jobs that do not run all steps Read More »
Problem: There you are on a Monday morning being a good DBA and checking into your SQL agent estate and verifying that jobs ran as expected. You check your e-mail inbox and notice the alert that a particular job failed, so what do you do? Most of us will open SSMS (SQL Server Management Studio), …
Script to show SQL Server agent jobs that automatically delete Read More »
Problem: One of your tasks as a production DBA is to ensure that all SQL agent jobs run successfully 100% of the time. Whether this is for end-user reporting or your backup and restore jobs, it is imperative that jobs succeed without any failed steps within them. When a job succeeds but has failed steps …
Script showing successfully failing SQL Server agent jobs Read More »
Problem: How can I view all SQL Server agent logs at-one-time without querying each individual log on its own? Solution: The answer is to put this data into a single table allowing you to better query this information based on your individual needs. I’ve given a possible solution with the following T-SQL script. Let me …
Background: Following in-step with my previous blog posts, you will know that I like to keep my SQL instances as free from clutter as possible. I provide a script below that will return all database mail accounts not currently being used by any database mail profile on the current instance. Having this list allows you …
Script to identify unused SQL Server database mail accounts Read More »
Background: Some individuals like to keep jobs in their SQL Server Agent for future code reference; these jobs have no schedule and will never run. For those I say, put them into your favorite code repository, not only for you but for others on your team too. For the SQL Server instances that I manage, …
T-SQL script showing SQL Server agent jobs that have never run Read More »
Problem: SQL Server Agent jobs on your server are enabled, they are scheduled, and run as planned, but do not have notifications. For mission critical jobs, not having a job notify users can be costly. Why is this a problem? No DBA wants to have bad optics around themselves or their team. It is crucial …
Problem: SQL Server Agent jobs on your server are enabled but they do not run at the scheduled date/time. Why? There could be a number of reasons, one of which could be because the underlying schedule is not enabled as we’ve seen in this post. However, some jobs may not run (even though they are …
T-SQL script to show SQL Server agent jobs that are enabled but do not have a schedule Read More »
Problem: SQL Server Agent jobs on your server are enabled and have at least one schedule assigned to them, but they do not run at the scheduled date/time. Why? There could be a number of reasons, but one that is easily overlooked is because an underlying schedule is disabled. The following script when run on …
T-SQL script to show all disabled SQL Server Agent schedules that have jobs Read More »
