DBA Services Australia News

Follow us on Google+, Facebook or Twitter to keep up with the latest news direct from our Blog!

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 http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

 

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
DBA SERVICES PTY LTD

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 (http://msdn.microsoft.com/en-us/library/ms188659.aspx);

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
DBA SERVICES

 

Top Wait Types and Descriptions

This is an extension on our post about How to configure your MAXDOP setting post, and Paul Randal‘s article on SQL Server wait statistics. The below script provides all the information from these scripts and includes a few hundred lines of description content to simply the next step of your troubleshooting!

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;

 

blockingprocess

 

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!

PCI Compliance Requirements with SQL Server

For those seeking to undertake PCI compliancing with SQL Server infrastructure, the following table details out some of these requirements along with how SQL Server (upwards from version 2008) caters for these:

PCI Requirement How SQL Server Addresses
1. Install and maintain firewall configuration * N/A – Controlled at Network Level
2. Do not use vendor-supplied credentials * SQL Server does not assign default passwords
* Most SQL 2008 features are disabled by default
* The system administrator account is disabled by default when SQL is setup using Windows Authentication
* The BUILTIN/Administrators Windows role is not a member of the sysadmin group by default
3. Protect stored card holder data * SQL Server 2008 Transparent Data Encryption (TDE)offers full data encryption
* SQL Server 2008 cell-level encryption offers encryption of individual columns
* SQL Server 2008 Extensible Key Management (EKM) offers split encryption key ownership
4. Encrypt transmission of card holder data * SQL Server 2008 supports SSL encryption
5. Use and update antivirus software * N/A – Controlled at Network Level
6. Develop / maintain secure systems / applications * Change controls are operational in nature, however segregation of duties is addressed under requirements 7.
7. Restrict access to card holder data * SQL’s Signed module facilitates segregation of duties
* SQL Supports Windows Authentication
* SQL Server supports Role-Based Access
8. Assign a unique ID to each person with computer access * SQL Server supports Windows Authentication. Unique identification is preserved even when granted access as a member of a group.
9. Restrict Physical access to card holder data. * N/A – Physical access control.
10. Track / monitor all access to network resources and card holder data * SQL Server Audit provides granular auditing capabilities
* Once target systems are identified and PCI compliant configurations set, SQL Policy-based Management can track changes
11. Regularly test security systems and processes * N/A – Controlled at Network Level
12. Maintain Plicy that addresses information security * N/A – Operational control procedures

 

There are six primary ways in which SQL Server caters for PCI complancing, which we’ll be covering in more detail in later posts;

  • Transparent Data Encryption (TDE)
  • Extensible Key Management (EKM)
  • SQL Server Audit and Change Data Capture
  • Signed Module
  • Build-in Control over Default SQL Server 2008 Features
  • Policy-Based Management (PBM)

 

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.

css.php

Request a Quote

Captcha content below

captcha