Sometimes there is a need to see last run time information for a SQL Agent job. You may need to see when the job is scheduled to run next. The msdb system database contains a ton of information about SQL Agent jobs schedules, activity, job steps and etc..
I created the below query to get a bird’s eye view of job executions. The query is based off msdb.dbo.sysjobs and msdb.dbo.sysjobactivity. These objects have a lot more information that can be added. Tweak the results to meet your needs.
I found a useful script to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time periods.
Stored Procedure Executions
For example, you have a under performing billing process. Your system fires multiple procedures multiple times. You could use something like this to get an idea of execution counts, worker time, average elapsed time and max logical reads. It will point you in the right direction procedure that can be optimized. Tweak this to fit your needs.
Read more »
Many third-party tools have pretty lead blocking graphs and etc. You may not have access to something than grand. My friend and co-work JD Pellerin wrote this script. He used this as a basis for an SSRS report that can be executed by operations teams to identify potential batch issues. Play around with tweak it to fit your needs.
Sample Blocking Results
Like Part 1 and Part 2, I use a similar query to find latency in AlwaysOn Availability Groups. The queries will return one line for each DB in the group. Part 1 , we looked at a simple query to grab the current status of the replica. Part 2, we looked at populating a Global Temp table every minute to gain a historical perspective of our AG latency. This query will look into creating an email notification when certain criteria is met. It will send the current status to a specific set of email recipients.
The example will be around specific criteria set as a step used in a SQL Agent job. If you use something like this, you need to tweak it to you organization’s needs. I do suggest that you set the job to a frequency that will not overflow your inbox with notices.
Send an email from server when latency for a specfic database datbase is over x number of minutes.
The example is pretty simple and intended for demonstration purposes.
Wanted to share this script. I pieced it together some time ago from various scripts. I think this might be the source for at least some of the query.
Removing the step_ID from the where clause will show run times by step_id and Total RunTime (step_id 0).
Read more »
I want to start out by saying I am not sure about the origin of this query. I use it frequently to find activity on my servers. I left some of the code commented to show a few example items you can add and additional search criteria. I really encourage anyone to grab a query and alter it to show information useful to them.
It uses sys.dm_exec_query_plan() (via inline select forgive me) to grab the query plan when possible. Additional information to grab performance information and SQL code executing uses these DMVs sys.dm_exec_requests, sys.sysprocesses and CROSS APPLY sys.dm_exec_sql_text(). Use the links to see the information being returned.
ARTICLE FROM ORIGINAL SITE – Young and Dumb!
Database Cursors have long been a favorite of mine. It allows you to take a subset of data and output the information in various ways. I use them for such things as building the body of an automated email with a cursor set within a stored proceedure. You can return output to build a cleaner report output, etc.Example
Select 'Case Created: '+convert(Varchar(20), @DATE ) + ' Case Number: '+ convert(varchar(10), @CaseNum)
We will start with the basic frame work and build from that:
We will use a two tables named SomeTable and Other Table
(The tables are actually from MySQL – I was lazy.)
Read more »
ARTICLE FROM ORIGINAL SITE – Young and Dumb!
Select Case allows you to present data in various way. You can take numbers and return text values and express
certian conditions based on the value of a certain column.
Another example would be that you have a table Naughty_Nice in the Santa Database a data column Nice (INT Datatype) .
There are two distinct values in the column. The Value of 0 equals ‘No’ and the value 1 equals yes. You wanted to speed
stuff up and use less space by selecting a number and not use text Naughty/Nice.
Santa needs a list and he complains that 1 and 0 will not make sense to him at 30,000 feet. It’s so hard to
communicate to a guy that still thinks wooden toys are a big hit. He’s a bit too hands-on, and lacks the technical
skills to really understand that we want to optimize the database.
Read more »
The script below is a quick way to create disable and enable SQL Agent job commands for existing jobs. It will script out the correct ‘EXEC msdb..sp_update_job’ statements. In this example, the where clause filters for “enabled = 1”, but you can use it create statements based of various job characteristics. We tend to use these when migrating to new servers and want to leave the old server in place.
/*
Script commands to disable/enable jobs
5/20/2014 JCD
Tested: 2005, 2008 R2 and 2012 SP1
You can further filter jobs with other sysjobs columns to specify
an even smaller subset of enabled jobs. Good examples for adding
filers are owner_sid, description and category_id.
*/
SET NOCOUNT ON
GO
USE msdb
GO
-- Script disable commands for enabled jobs
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=0;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
-- Script enable commands to enabled jobs after disabling. Should be run when creating the disable script
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=1;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000. A really awesome tool for data delivery. Most SQL Server environments heavily leverage this powerful tool. Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3). SSRS also supplies a text log with additional details for errors and warnings. All are useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?
Recently, I needed to identify an active report that was creating a considerable amount of blocking. We were able to identify a reporting login , verify the blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking. As I mentioned, for the most part the ReportServer database gives us historical information. I always based most of my queries on the ReportServer database. In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!
Read more »