Relax in the knowledge that your data is in the hands of qualified professionals!
Sia Shamsai

SQL Server General

How to Drop and Recreate Foreign Key Constraints

The following script provides for a convenient to generate the drop foreign key commands and (more importantly) their corresponding “recreate foreign key” commands which allow you to easily manipulate data across constrained tables.

Care should be using this script on high transaction volume systems where there may be a risk of un-constrained data entry taking place and potentially impacting on the data integrity of the environment – not to mention complications when trying to recreate the FK constraints again.

Just update the @table and @schema variables below, and you should be good to go!

How to Find and Alert on Deadlock Conditions

As with anything in SQL Server, there’s always multiple ways to approach any problem you come across. When looking to identify dead lock conditions for instance, some of your options include;

  • SQL Server Profiler Trace
  • SQL Server Extended Events Trace
  • SQL Server Trace Flags
  • SQL Server Alerts


There’s heaps of details examples available online which step you through setting up a SQL Profiler trace, and even an Extended Events trace via the new 2012 user interface. For reference purposes you can check out the following;

One thing however that many might not be aware is that Extended Events have actually been available in SQL Server since their 2008 release, however via a scripted interface only (which for most makes this much less desirable to use).. BUT, there is an add-on available which does bridge this gap for the command line impaired;

Admittedly the interface isn’t quite as sexy as the 2012 EE interface, but definitely helps to sooth the burn for those point-and-click DBAs out there ;)


SQL Server Trace Flags

SQL Server Trace Flags allow you to monitor for specific server conditions and log these events into the SQL server error log. For a full list of these flags (and noting that different flags may be SQL version specific), refer to:

Now to setup a SQL Trace on Deadlock conditions, the Trace Flags you’ll want to enable are 1204 and 1222. The following command enables both these flags globally on your SQL Server;



If you want to ensure these flags are enabled at time of starting SQL Server, you’ll need to add -T1204 and -T1222 to your instance’s start up parameters (done via SQL Server Configuration Manager). For more information on these flags, refer to


SQL Server Alerts

If you are setting up SQL Server Alerts, there are a few which you’ll want to include. Make sure that you have your Database mail enabled and configured along with your SQL operations so that you get the notifications for these when they occur. The following script creates the alert conditions under SQL Agent. Once implemented you’ll need to open these and define the notification response (so that it emails the SQL operations) and alert messages you’d like to send.


The following Alert condition alerts on deadlock events;


The following Alert condition alerts on lock allocation events;


And finally, the following Alert condition alerts on lock request time-out events;


Remember though, the above will only create the alerts under the SQL Agent – you’ll need to open each one to specify the alert response and notification message!

Happy Hunting!

How to Find Which Files Belong to Which SQL Database

In the life of anybody who is actively engaged in SQL server support services, there will come a time when a client takes it upon themselves to refresh a database from their production environment into their dev environment for the purposes of further development on a current data set.


Now this in and of itself is usually a problem free and joyous time for our client, however for a DBA this can present a number of potential problems. The first of which being that these production databases are typically using a full recovery model and as such will continue to generate logs in the dev environment which most likely geared to handle simple recovery model DBs only… and so the SQL support technician invariably wakes up one morning to find a low disk space alarm waiting for them due to the log files blowing out.


We then move onto our next challenge; after identifying the offending log file, the DBA will need to establish which database that log file belongs to. Now as our free spirited client would most probably not have adhered to best practices for naming the database and data files in a way that is intuitive to associate the two – the SQL Support technician will then need to try and identify which database these hefty, unmaintained log files belong to.


Enters the below script which will help you to find which files belong to which SQL database, and then onto correcting the recovery model, shrinking the log file down and continuing on with your day!



The SQL Server Support Team

SQL Server Fixed Server-Level Roles

SQL Server’s fixed security roles provides powerful functionality for granting delegated access for administering your SQL environment, however contrary to popular usage, these roles are not intended to be used for application level access to your SQL Server databases, which is why I thought it might be good to provide a quick review on what each of these roles is and the functions which they perform.

You can get a quick list of SQL Server fixed roles through the following query;


Once armed with this information, the below screenshot from Microsoft’s own reference article explains what access each role provides (;

Remember, play it safe and always adhere to proper role based security assignments to keep your environment under control and easily managable!

The SQL Server Support Team


How to Find a Blocking Process

Whereas the TSQL commands sp_who2, sp_lock and select * from sys.sysprocesses can provide a wealth of information when dealing with blocking processes, for less experienced DBAs the output of these queries can be quite overwhelming – especially when the we’re talking about the life and death of a process that may be deadlocked in your environment and preventing others from completing. Given the destructive nature of killing such a process, it’s very important that the correct process is identified to avoid unnecessary data loss.

The following query takes the guess work out of this and provides a very simple output that clearly identifies the offending process;



The following is an example of the output this query generates;




From here, you can confidently eliminate the blocking process so your environment so your systems can continue operating normally using the following command;




How to Tune Maximum Degree of Parallelism (MAXDOP)

The Maximum Degree of Parallelism (MAXDOP) setting will determine the degree to which SQL server will attempt to parallel process transactions which are received from the scheduler. As a general rule of thumb, OLTP environments with high transaction volume and repetitive queries should normally have a MAXDOP value of 1, as any benefit gained from parallel process would quickly be lost in overheads introduced in managing this. OLAP environments however (such as analysis services / data warehousing etc) where transactions are generally much fewer, however considerably more complex – these environments would benefit from a MAXDOP setting of 0 (unlimited) or near the number of CPUs in the system.

A good indicator on whether this value has been correctly configured is the CXPACKET performance counter. Where more than 5% of the total wait conditions are due to this counter, it’s most likely that the MAXDOP value will need to be adjusted.

Below are 2 code snippet to retrieve the wait types as a percentage. The first is from Paul Randal;

The 2nd snippet is a reduced version of this which only returns wait_time_ms and percentage;

The MAXDOP value can be updated during run time by the following command, noting that the value below should be adjusted to suit your requirements;

You may also want to clear the wait statistics after this to ensure your next checks on the system are clear / fresh statistics since this change has come into effect;

SQL Server Migration Checklist

Are you thinking about migrating your SQL Server infrastructure?

The following are some of the SQL Server components that you will need to keep in mind to ensure that your migration between SQL Servers goes smoothly;

  • Data files 
  • Database objects 
  • Logins
  • Linked Servers
  • System DSNs and Drivers
  • Extended stored procedures
  • Configuration settings 
  • Security settings 
  • SQL Server Agent jobs 
  • SQL Server Integration Services (SSIS) packages 
  • Maintenance Plans
  • User-Defined error messages


If you’re migrating to a newer version of SQL Server, ensure that you first run the appropriate upgrade advisor to alert you to any potential issues that you may encounter between SQL editions, and always verify that your business applications are supported by your vendors at that database level otherwise you may risk voiding your support agreements!

For more information on this, check out our database migration services!

How to Check Authentication Method into SQL Server (SharePoint)

If you’re deploying SQL Server Reporting Services 2012 (integrated mode) into SharePoint Server 2010, you will want to ensure that your SharePoint environment is successfully passing Kerberos authentication into your supporting SQL servers.

The following T-SQL script will allow you to easily interrogate your SharePoint’s SQL server for authentication schemes which are being used to connect to it. From this you’ll quickly determine whether your authentication in SharePoint has been correctly configured to pass Kerberos tickets, or whether it is only passing NTLM based authentication;

Kerberos is required in order for SharePoint’s Claims to Windows Token service to successfully translate a Claims based authentication (used within SharePoint) back to a Kerberos ticket for authentication into SQL Server Reporting Services.

How to Search every Table in a SQL Database for a string

The below stored procedure lets you parse all tables in a database in search for a string value. Once created, run the following from a query window;

The stored procedure is below;


Request a Quote

Captcha content below