Author Archives: Darren

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

Modifying Batch Job Statuses in a Non-Production Environment

There’s a few things you can do when undertaking your data refresh into a non-production environment one of them is modifying your batch jobs to ‘Withhold’ status ensuring they won’t start to run when you after you start up your AOS’s and then configure your batch server settings. After the script has been ran you can set the status of jobs you want to run back to ‘Waiting’ status at a later point during the data refresh process, happy in the knowledge that you know no batch jobs are going to start emailing customers or update/talk to other external systems.

SQL Script:

-- Change to correct DB
 USE <Your Ax Database>
-- Set all Batch Jobs to Withhold status
 UPDATE BatchJob
 SET status = 0

You could also have a scenario where only certain batch jobs should be set to Withhold status, you could do this by adding the batch jobs you want to continue to run into a table and then only update the ones not in that table to Withhold. An example script to do this can be found below:

Pre-Requisites: Create a table with one data field called ‘Caption’, populate table with batch job names that you want to continue to run in your non-production environment (e.g. not be at withhold status)

SQL Script:

-- Change to correct DB
USE <Your Ax Database>

SET status = 0
Caption NOT IN (SELECT Caption FROM <the table name created in pre-requisites>)

Disclaimer: Ensure any batch jobs you add to this table don’t have other parameters that need to be changed prior to them running again in the non-production environment.

Removing Email Addresses from Non-Production Environments

A few simple scripts to run when building a 2009 non-production environment from your 2009 production database to remove email addresses from the ‘CustTable’, ‘VendTable’ and ‘Address’ table this will ensure no emails can be sent in error to your customers or vendors from your non-production environment.

-- Change to correct DB
USE <Your Ax Database>

UPDATE Address
EMAIL != ''

UPDATE VendTable
EMAIL != ''

UPDATE CustTable
EMAIL != ''

DISCLAIMER: You may need to add additional tables/fields for any customizations you may be using.

Removing User Login Data from Non-Production Environments

As part of updating your AX 2009 non-production environments I recommend running a script to remove user login data so you can see who is using your environment and it doesn’t cloud things with it having LIVE login data in there.

This script will delete user log data prior to the database creation date (which should be the restored date of the database if a RESTORE DATABASE WITH REPLACE was used)

The user log data can be viewed in AX2009 via ‘Administration > Inquiries > User Log’

Initially run this query to determine the AX DB creation date:

SELECT create_date
name =  '<Your AX Database Name>'

Once you’ve verified the date is okay run the below script:

/* Delete User Login Data based upon Database Creation Date */
DECLARE @dbcreatedate DATETIME

/* Get Database Creation Date */
SELECT @dbcreatedate = create_date
WHERE name = '<Your AX Database Name>'

SELECT @dbcreatedate AS Database_Creation_Date

/* Delete User Login Data */
CREATEDDATETIME < @dbcreatedate

Download  Delete-User-Login-Data-using-Database-Creation-Date (750 downloads)

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 (

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

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

Dynamics AX 2009 components are installed into an incorrect directory and the install fails

I recently encountered an issue when installing a 2nd AOS on a server that it failed to install correctly.  The components were attempting to install into an incorrect directory, therefore the install failed as it was unable to register the service correctly.

You may see the following error message within the “DynamicsSetupLog.txt” install log.

AOS.1: Regasm C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe   "C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll"
RegAsm : error RA0000 : Unable to locate input assembly 'C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll' or one of its dependencies.

An error occurred during setup of Application Object Server (AOS).
Reason: Error unregistering  Microsoft.Dynamics.IntegrationFramework.dll. Program run: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe.  Parms: "C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll" /unregister

As you can see it’s attempting to install the AOS into an incorrect directory namely:

C:\Program Files\Microsoft Dynamics AX\50\Server\Server\

The correct folder is:

C:\Program Files\Microsoft Dynamics AX\50\Server\

A hotfix exists to resolve this issue it can be download from Customer Source here:;en-us;959494

View the knowledge base article on the MS Support site here:

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 (983 downloads)

Download (Word Version) SQL-Server-Object-Naming-Conventions-Administration (1492 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