Monthly Archives: August 2013

70-457 Reviewer #10 Managing SQL Server Agent

This review topic is part of Managing Data lesson which covers 20% of the certification exam.

SQL Server Agent enables you to execute scheduled or event-driven administrative jobs, automating processes you would otherwise have to execute manually. Each SQL Server instance you setup has a separate SQL Server Agent.

During the installation of SQL Server 2012, the SQL Server Agent is disabled by default. You will need to start this manually. You can configure the restart process and startup type however, in the SQL Server Configuration Manager.

Configuring the SQL Server Agent Account

Some of the best practice when setting up a service account for your SQL Server Agent are;

  • The service account should not be a member of the local administrators group on the host computer.
  • The service account should be member of the sysadmin fixed server role.
  • It is best to use a domain-based security account.

Setting SQL Server Agent Security

To allow a user to use the SQL Server Agent, and he is not a member of sysadmin fixed server role, then he/she must a member in one ore more of the following fixed database roles in the msdb database.

SQLAgentUserRole – This is the role with the least privilege among the 3 roles. Members of this role have only permission on the local jobs and the job schedules they own. This role does not allow use of multi-server jobs.

SQLAgentReaderRole – Members of this role are also granted permissions the same as SQLAgentUserRole. Additionally members of this role can view the properties and history of all available jobs and schedules and multi-server jobs.

SQLAgentOperatorRole – Members of this role are also granted permissions for SQLAgentReaderRole and SQLAgentUserRole. Additionally, members of this role can execute, start and stop and delete job history for any local job. Members can also enable and disable all local jobs and schedules.

SQL Server Agent Mail Profile

SQL Server Agent uses Database Mail in sending out alerts as email messages. You will need to set up the alerts in the SQL Server Agent properties. On the Alert System page, check the Enable mail profile checkbox and select an existing mail profile. After clicking OK, you need to restart your SQL Server Agent service.

In your SSMS, right click on the SQL Server Agent and go to properties. This should open a dialog window similar below

Database Mail

SQL Server Agent Error Log

The SQL Server Agent error log stores warnings and error messages from the SQL Server Agent. Other information events by choosing ‘Information’ in the Agent log-level category in the error log properties. You can maintain up to maximum of 9 error logs, but you can cycle these logs by choosing ‘Recycle’ from the Error Logs node under the SQL Server Agent. In this case, however, the oldest log  will be deleted.

In your SSMS, right click on the SQL Server Agent and go to Error Logs. This should open a dialog window similar below

Configure Error Log

Managing Alerts

Alerts can be triggered by event logs, WMI events or performance conditions. You can configure SQL Server Agent to look for a specific event or performance condition; when SQL Server Agent detects that event or performance condition, it triggers an alert.

Creating an alert involves the following steps.

  1. Creating an alert name.
  2. Specifying the alert trigger.
  3. Specifying the alert action.

To create a new alert, from the SQL Server Agent, right click on Alerts and select New Alert. You will need to fill the all the details. On the Response page, you can choose an existing job or create a new one. You can select to notify operator from this page. On the Options page, you can choose to include alert text in an email, pager or net send message.

If the alert you are creating is based on an SQL Server event, similar to the one below, you must choose an alert based on

  • a specific error number
  • a specific error severity
  • message text

New Job Alert

Managing Jobs

Database administrators use jobs to automate routine task such as health checks, maintenance tasks or application required tasks.

The steps in creating a new job are;

  1. Define the general information on the General page.
  2. Configure the job steps
  3. Configure the job schedule
  4. Configure the job alerts
  5. Configure the notification
  6. Configure the targets for multi-server administration

When configuring a job step, you define what happens if the step is completed successfully or if it fails. You can do this on the Advanced page of the job step dialog window. See below screen shot.

New Job Step

After setting up the steps for your jobs, you need to configure when the job will execute. You configure the schedule for the job on the Schedules page. See below screen shot.

New Job Schedule

From the above dialog window, you can also configure your job to run only once or recurring and or with a trigger, for example whenever the CPU falls below the idle threshold value.

Next step, which is optional when creating a job is configuring the Alerts associated with your job. The Alerts window is similar below.

New Job Alert

Another optional step you can configure when creating your job, is setting up the notifications. You can do this in the Notifications page. See below window;

New Job Notification

To monitor all your jobs and view job history, you can go to Job Activity Monitor window. You can also use this Job Activity Monitor to determine which jobs are scheduled to run in the future and wether the job’s last execution was successful.

Monitoring Multi-Server Environments

When properly configure, you can use SQL Server Agent to manage jobs across multiple servers. This is where you need to setup master and target servers. Before enlisting a target server in your master server, the target server must be already configured successfully. A target server can only be enlisted to a single master server. You must use a windows domain account for the SQL Server Agent service account.

You can use the sp_msx_enlist stored procedure on the target server to enlist it to a master server. If you must switch master servers for a target server, you need to defect the target server from its current master server.  If you need to change the name of your target server, you must first defect it also from its current master server, change the name then enlist it again. You also need to do this defection for all your target servers if you need to remove an existing multi-server configuration.

I have written an article on scaling out your maintenance plans if you have a master and target server configured in your environment. You may read the article here

One exam tip on this lesson is you should be able to remember the fixed database role to which you add a principal to if the principal needs to be able to start, stop or execute any job.

Credit for featured image to V&A Steamworks.


Using DMV to identify sessions in your SQL Server

More than running sp_who2, which gives you more information than the good ol’ sp_who, or querying sys.dm_exec_sessions to check all sessions running in your SQL Server, you need be able to flag to suspicious or questionable sessions thats running transactions in your server. You need to identify which session has multiple connections or sessions with context switching or sessions with orphaned transactions then move on to eliminate these sessions.

Logins with more than one session

To identify logins running multiple sessions in your SQL Server you may run query below

SELECT login_name, COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name
ORDER BY login_name

If you have a baseline figure of number of connections per login, including logins you may have at the application level, then you know if a login has a doubtful number of connections. Though this may not at all present a critical problem, it’s a nice-to-have information at hand that you can discuss with the development team regarding their application design.

Sessions with context switching

Context switching is the executing a T-SQL statement under the guise of another user connection in order to use their access rights that comes with their credential. To identify if there is such session running in your SQL Server, you may run below query and still using sys.dm_exec_sessions DMV;

SELECT session_id, login_name, original_login_name
FROM sys_dm_exec_sessions
WHERE login_name <> original_login_name AND is_user_process = 1

Sessions with orphaned transactions

The query below will filter out all sessions with orphaned transactions, still using the same DMV, dm_exec_sessions.

FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_tran_session_transactions dtst ON des.session_id = dtst.session_id
LEFT JOIN sys.dm_exec_request der ON dtst.session_id = der.session_id
WHERE der.session_id IS NULL
ORDER BY des.session_id

The LEFT JOIN on sys.dm_exec_request and returning only for NULL results in sessions that are still open and yet do not have a corresponding request associated to that session.

Sessions that are inactive 

To filter out all open sessions with still active connections but have been inactive or have no active request running in your SQL Server, you can run the query below.

FROM sys.dm_exec_sessions des INNER JOIN sys_dm_tran_session_transactions dtst ON des.session_id = dtst.session_id
WHERE des.is_user_process = 1 AND DATEDIFF(dd, des.Last_request_end_time, GETDATE()) > 3
ORDER BY des.last_request_end_time

From the result of the above query, you can watch out for the [STATUS] column that is not equal to ‘Running’ (maybe ‘Sleeping’, ‘Dormant’, etc). In m sample query above, i filter out all open sessions for the last 3 days but you can change this to any number of day as your case maybe.

Now that you have identified sessions that are orphaned or inactive sessions, you may proceed to clean up your sessions and KILL those who are not needed anymore.

Who’s doing what to your SQL Server?

When doing any kind of troubleshooting in your SQL Server first thing you ask is who is currently connected to the SQL Server having problem and what the heck is going on? First thing you do? Run sp_who2. That will simply tell you about the sessions currently on in your server and the status if its ACTIVE, SUSPENDED, AWAITING or what have you. Luckily for us DBAs, in SQL Server 2008 a lot of DMVs have been created primarily for health checks, troubleshooting and verifications.

To check connections and their activity connected to your SQL Server and to know what they are actually doing you can run the following statement

SELECT dec.client_net_address, dec.host_name, dest.text

FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec

CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest

If you want to filter only those activity that is using SSMS, you can add a WHERE clause like below

SELECT dec.client_net_address, dec.host_name, dest.text

FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec

CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest

WHERE des.program_name LIKE ‘Microsoft SQL Server Management%’

You can then flag red any activity that you may deem suspicious or flag any client net address not associated with any workstations within your development team. The DMV dm_exec_sessions has so much information on the sessions that is currently open in your SQL Server. I will tackle this on my next post. 🙂

Get the Backup History in your Central Management Server for SQL Server

I have a setup of Central Management Servers in my SQL Server and it’s been a big help to me in my everyday database administration tasks. I want to share with you guys my CMS setup as below.

CMS Screenshot

So for across all these servers, and everytime I need to setup a new one, I only have one maintenance plans then I just scale it out to all the target servers. Neat!

One neat script that you can run in your CMS is a quick check of backup history across all your target servers. See my script below;

SELECT backupset.database_name,
CAST(CAST(backupset.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ MB’ AS backupsize,
backupset.[type] AS BackupType,
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmedia
ON backupset.media_set_id = backupmedia.media_set_id

It will give you the results as below, then you can just do a scan to see if all your target servers had a successful backup overnight.

Backup History Script Result

If you need to automate this script, you will need to do additional scripting to store the data in a table somewhere that you can export to a report later and then you need to create a scheduled job for this.

I hope this is helpful to you also 🙂

70-457 Reviewer #09, Auditing SQL Server 2012

This review topic is still covered under the Optimize and Troubleshoot SQL Server lesson which covers 14% of the exam.

Auditing gives us, the DBA, the means to see who’s doing what and where they’re doing whatever on our SQL Servers. Auditing will enable you to track any activity on a SQL Server instance.

SQL Server Audit

SQL Server Audit enables you to configure auditing at the instance and database level. SQL Server Audit needs to forward this audit results to a target which may be a file or the event log in the host server. If you do select Security Event log, remember that access to this is limited to users who are usually member of the local administrator group. For the Application log however, any authenticated user can read and write to this log but do remember that this is also a vulnerable risk depending on the activity you are auditing.

If you want to use the Windows Security log as target for you audit results, you need to be extra friendly with your domain administrator group as they need to do additional configuration for this in Group Policy. They will need to add additional user rights assignment and configure  Audit Object Access policy for read and write access.

If you do want to save your audit results to a file, the steps will be simpler and you can do it on your own. You just need to provide read and write access to your SQL Service account on the file. Users assigned the Audit Administrator role will need a read and write access while the users assigned the Audit Reader role must only have read access to the log file.

It is highly recommended that you place your audit results target to a separate instance from the you are auditing to minimize the chance that server admins can tamper with the results. Then on the separate instance that you have, make sure to setup only the Audit Administrators and Audit Readers have the appropriate access.

Creating a Server Audit

You can create a server audit from the SSMS or simply run the equivalent script below

( QUEUE_DELAY = 1000,

Create Audit

The important fields you need to input here are the following;

  1. Audit Name – Your name for the server audit
  2. Queue Delay – delay in milliseconds before audit actions are processed. Default value is 1000 ms.
  3. On Audit Log Failure – As with my script above, I selected SHUTDOWN. Here you must enter what must the server do in the event of audit log failure.
  4. Audit Destination – You need to specify the target where you want your audit results to be, options are Application log, Security log or a log file.

Creating a Server Audit Specification

After creating the server audit, you need to create the server audit specifications to include the group and actions which to audit. You can do this in two ways, one is via SSMS or simply run the script below, script is based on the created AppLogShutDown_Audits from the server audit script above.

FOR SERVER AUDIT [AppLogShutDown_Audits]

In the object explorer, from Security > Server Audit Specifications > right click on New Server Audit Specifications, see screenshot below. This is still based on the AppLogShutDown_Audits we created above.

Create Server Audit Specifications

Based on the server audit, you can select from the drop down list which actions you want to include in your audit results. If you select BACKUP_RESTORE_GROUP, all activities with regards to backup and restore operations in your server will be included in your audit result.

With SQL Server 2012 Enterprise Edition, you can specify audit actions at database level. You can use both audit action groups and audit actions with database audit specifications. For more information on different server and database audit specifications you may visit

Important exam tip: To create, alter or drop a database audit specification, security principals must have permission to connect to the database and be assigned either the ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permission on the database.

Configuring Login Auditing

To configure login auditing on the Security page of the Server Properties dialog box, right click on the Server instance in the object explorer and go to Properties, as show below. Select which event during login you want to audit.

Configuring Login Auditing

Using c2 Audit Mode

c2 Audit Mode configures the instance to record both successful and failed attempts to access statements and objects as defined by the c2 security standard. On the same Security dialog box (above screen shot), check the Enable c2 Audit Tracing option box. Or you can also run the script below;

sp_configure “show advanced options”, 1;
sp_configure “c2 audit mode”, 1;

Common Criteria Compliance

The c2 audit mode will be deprecated in the future version of SQL Server and will be superseded by common criteria compliance. Common criteria compliance enables security options including login auditing information in the database instance. You can access this login information via DMV sys.dm_exec_sessions but you need to enable the common criteria compliance by running the script below.

sp_configure “show advanced options”, 1;
sp_configure “common criteria compliance enabled”, 1;

Policy Based Management

Policy based management provides you with the means of managing instances across multiple servers. These policies are then you use to enforce configuration standards in all your database objects and properties.

Facets are collection of properties for an object. So for the database this would be auto shrink, auto close auto what have you property. Policies are the set of rules we want to enforce in our servers for the facets we like to enforce on.

Steps to create a policy;

1. From the object explorer drill down to Management > Policy Based Management > Facets

2. Right click on the Facet you want to create a policy based on. Click on New Policy.

Create New Policy

3. Enter the Policy name.

4. Create new condition based on the policy. Click OK to save your new policy.

To test this NoAutoShrinkPolicy we created, try to create a new database with the Auto Shrink property set to TRUE. Then go to your list of policies and open the dialog box for NoAutoShrinkPolicy. You should be able to open a similar dialog box as below.

Evaluate Policy

Your newly created database with the property Auto Shrink set to TRUE should appear in this list as they failed your policy. To correct this Auto Shrink property, click on APPLY. Then check your database property again to verify that the Auto Shrink property is now set to FALSE.

Important exam tip: There is already a policy named Database Auto Shrink so you don’t need to create this one on your own.

If you are using Central Management Servers in your administration task, you can also create policies to delegate this across multiple servers. Also you can transfer policies between instances by using the import and export method.

Learn more about policy based management at



Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment