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

SQL Server General

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;

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;

How to Kill all Database Connections

The easiest way to kill all database connections is to set the database to SINGLE User mode;

then once finished, change this back to normal;

The slightly more involved method is to use a scripted method to remove all connections to the DB;


Request a Quote

Captcha content below