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 »
The built-in procedure sp_spaceused can provide some useful information, especially when an object name is specified; great for showing the finer details of that object.
