Tag Archives: SQL Server

Another resource for Free E-Books available for Microsoft Technologies on TechNet

You can download content for ASP.NET, Office, SQL Server, Windows Azure, SharePoint Server and other Microsoft technologies in e-book formats such as PDF, E-Pub or Mobi (for use on Kindle). There’s different types of books available such as References, guides, and step-by-step information.

All the e-books are free to download and new books are being posted when they become available take a look and make sure you bookmark it or add the RSS article feed to your favourite RSS Reader:


Large collection of Free Microsoft eBooks for you, including: SharePoint, Visual Studio, Windows Phone, Windows 8, Office 365, Office 2010, SQL Server 2012, Azure, and more.

Here’s two great posts which contains download links to free Microsoft eBooks that are available for SharePoint, Visual Studio, Windows Phone, Windows 8, Office 365, Office 2010, SQL Server 2012, Azure, and more.





Finding which Operators are setup against SQL Agent Job Notifications

As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.

Agent Job Properties

How do I check which operators are setup against my Agent Jobs I hear you say!

Well run this query below to find out.  If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)

SELECT SJ.NAME, SO.[name],SO.[email_address],
[notify_level_email] =
CASE notify_level_email
     WHEN '1' THEN 'When the job succeeds'
     WHEN '2' THEN 'When the job fails'
     WHEN '3' THEN 'When the job completes'
     ELSE 'No email notification configured'
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO 
ON SJ.notify_email_operator_id = SO.ID

Download  Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (299 downloads)

The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.

Agent Job Notifications


There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1.  Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2.  Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)  
3.  Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’

SQL Agent Operators Recommended Setup

Database Mail should be configured on your SQL Server, if it isn’t get it done now there are no excuses, this is one of the main methods that can be used for finding out when there are issues with your SQL Server!

It’s a good idea to setup an operator with a distribution email group email address so if a DBA leaves or moves onto another position the distribution group can be easily updated once in Active Directory as opposed to being updated on every SQL Server.

DBA Team Operator


You could also create additional distribution email groups and add them as operators this is useful if other administrators needs to be aware of certain Agent Jobs failing such as say BI Jobs.

DBA Team BI Operator

Run a Dynamics AX SQL statement as an API Cursor in SSMS

We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.

Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query below.

Simply replace the red text below with all the query variables and replace the blue text with your parameters and execute the full statement and voila you can view the exact query plan AX uses to execute the statement.

TIP: If your unsure of the variables to declare in the cursor you can find them by doing a SQL Profiler Trace.

— Normal FFO Cursor

— Script taken from AX Dynamics Performance Analyzer scripts by Rod Hansen (http://code.msdn.com/dynamicsperf)

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int

— Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)

set @p5=16+4096+8192+16384
declare @p6 int
set @p6=8193
declare @p7 int

— Number of Rows for AutoFetch.
— This is calculated by Maximum Buffer Size (24K default) / Row Length

set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N’@P1 nvarchar(5),@P2 nvarchar(21)‘,N’SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))’,@p5 output,@p6 output,@p7 output,N’ceu’,N’SO-100004′
— @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7

Show SQL Plans in Plan Cache for a table name or matching partial query text

Ever wondered what queries are calling what tables or do you have a query and want to see how many times it’s been called (since last plan cache flush) and view the query plan for it.

These queries are a good to use when performance tuning indexes and should be a part of your toolbox.  You need to know what queries are using your table before you can start to plan the best indexing strategy for your table.

Note: Query#2 will only work in SQL Server Management Studio 2008 onwards.

Here is a quick and easy way to do this:


This query will show you the plan handle value you need to extract the query plan from the plan cache, it will also show you how many times the query has been executed using that plan handle.

-- Shows SQL plan handles in plan cache for a specific piece of SQL text
SELECT plan_handle, usecounts AS ExecutionCount, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%<Enter Table name or partial Query here>%'
ORDER BY usecounts DESC


Using the plan_handle value from Query#1 insert it into the where clause and this will give you XML result which you can click on to view the graphical query plan.

-- Show XML query plan ** Only works with SSMS2008 **
sys.dm_exec_query_plan(<Paste Query "plan_handle" value from previous query here>)



Query#1 Results of ‘%EVENTINBOX%’ 

Query#2 – XML result of plan_handle (0x06001100A23AD31D40031579060000000000000000000000) from highest exection count of Query#1


Query#2 – Visual Query Plan after clicking on XML result show above

Update to Ola Hallengren’s SQL Server Maintenance Solution available – Support for SQL Server 2012

New Version Available

A new version of the SQL Server Maintenance Solution by Ola Hallengren’s is now available. The new version supports SQL Server 2012.

One new feature in SQL Server 2012 is AlwaysOn Availability Groups. This feature supports multiple replicas of a database. You can back up any replica and define a preferred backup replica.

SQL Server 2012 also supports online rebuilding of indexes with varchar(max), nvarchar(max), varbinary(max), or XML data types or large CLR types. The new version of the SQL Server Maintenance Solution makes use of this capability.


You can read more about the most recent version of the solution at

or download it at



New Documentation

Updated documentation is now available at



Microsoft SQL Server 2012 Courses Released on MVA

5 new SQL Server 2012 courses are available on Microsoft Virtual Academy’s site http://www.microsoftvirtualacademy.com/

Follow the below links to read more about them:

Breakthrough Insights using Microsoft SQL Server 2012 : Analysis Services and Credible, Consistent data

Breakthrough Insights using Microsoft SQL Server 2012 : Reporting Services and Visualization

Breakthrough Insights using Microsoft SQL Server 2012 : Scalable Data Warehouse and Beyond Relational

Cloud on your terms with Microsoft SQL server 2012 : Scale On Demand

Mission Critical Confidence using Microsoft SQL Server 2012

SQL Agent Jobs refresh times when using sysjobschedules table

A recent issue I was looking at confused me as we were adding SQL Agent Jobs to run a SSRS report but the agent job was not appearing in the ‘sysjobschedules’ table, eventually the job did appear but why did it not appear straight away.

The reason is the the sysjobschedules table refreshes every 20 minutes, which may affect the values returned by the sp_help_jobschedule stored procedure.

Read the article here on MSDN


SQL Server Administration – SQL Object Naming Convention

It’s useful to use a naming convention on Agent Jobs or Maintenance plans, this ensures they are grouped together and easier to find when you have lots of job or maintenance plans on your SQL Server.

Download (PDF Version) SQL-Server-Object-Naming-Conventions-Administration (992 downloads)

Download (Word Version) SQL-Server-Object-Naming-Conventions-Administration (1499 downloads)

Introducing Microsoft SQL Server 2012 – Free E-Book

Introducing Microsoft SQL Server 2012, by Ross Mistry (@RossMistry) and Stacia Misner (@StaciaMisner), is now available as a free download!

It’s available in the following formats:

PDF , MOBI (Kindle Format), EPUB

Introducing Microsoft SQL Server 2012 includes 10 chapters:


1.   SQL Server 2012 Editions and Engine Enhancements

2.   High-Availability and Disaster-Recovery Enhancements

3.   Performance and Scalability

4.   Security Enhancements

5.   Programmability and Beyond-Relational Enhancements


6.   Integration Services

7.   Data Quality Services

8.   Master Data Services

9.   Analysis Services and PowerPivot

10.   Reporting Services