SQL Server Deadlocks

You are an accidental/junior DBA. One of your developer says he's getting a lot of deadlock messages in the application log suddenly.  He is asking for the DBA's (Your) help.  What are you going to do.  This blog post will help you to handle this issue.

What is deadlock
Deadlock is a situation when two transactions have mutually locked out each other. 

For example, Lets take two transactions
 "TRAN-1",  it has two insert statements,  First insert on Table-A and Second Insert on Table-B
 "TRAN-2" ,  This also has two insert statements, First insert in on Table-B,  Second Insert is on Table-A.

When both of these Transactions execute at the same time,
Tran-1 will finish the insert on Table-A and wait for the lock to be released (acquired by Tran-2) on Table-B. The lock on Table-A will still be active because it is inside a transaction.
Tran-2 would have completed the insert on Table-B but would not have released the lock since the transaction is not commited yet, but will be waiting for the lock to be released for Table-A.

At this point,  The deadlock situation has encountered. 

SQL Server is smart enough to diagnose the deadlock situation and identify one of the processes as victim and kill it so that the other process can continue to execute.

How to analyze deadlock
Deadlock related information will be logged to the errorlog only if the related trace flags are turned on. Otherwise, you won't find any useful info from the errorlog.

The traceflags are 1204 and 1222(After SQL Server 2005).

How to identify if the trace flags are already turned on

DBCC TRACESTATUS command will return the trace flags that are currently turned on.

How to turn on the trace flag.

The following commands will turn the trace flags on.

DBCC TRACEON (1222, -1)
DBCC TRACEON (1204, -1)

What to look for in the error logs

Here is a sample error message from the log

2014-04-11 15:24:22.22 spid4s      Requested by:
2014-04-11 15:24:22.22 spid4s        ResType:LockOwner Stype:'OR'Xdes:0x00000004E9FD8BC0 Mode: U SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000004E9BBC608) Value:0xeb284540 Cost:(0/144)
2014-04-11 15:24:22.22 spid4s     
2014-04-11 15:24:22.22 spid4s      Victim Resource Owner:
2014-04-11 15:24:22.22 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x00000004F08D6D28 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000004E97B8608) Value:0xf5b126c0 Cost:(0/144)
2014-04-11 15:24:22.22 spid18s     deadlock-list
2014-04-11 15:24:22.22 spid18s      deadlock victim=process4f9025498
2014-04-11 15:24:22.22 spid18s       process-list
2014-04-11 15:24:22.22 spid18s        process id=process4f9025498 taskpriority=0 logused=144 waitresource=RID: 8:1:301:0 waittime=5851 ownerId=32694 transactionname=user_transaction lasttranstarted=2014-04-11T15:24:16.370 XDES=0x4f08d6d28 lockMode=U schedulerid=3 kpid=3476 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-04-11T15:24:16.370 lastbatchcompleted=2014-04-11T15:24:07.050 lastattention=1900-01-01T00:00:00.050 clientapp=Microsoft SQL Server Management Studio - Query hostname=SIVA-PC hostpid=4024 loginname=Siva-PC\Siva isolationlevel=read committed (2) xactid=32694 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-04-11 15:24:22.22 spid18s         executionStack
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=4 stmtstart=16 sqlhandle=0x020000006306cb0282580a95a5146f4b2ce8d05ad05f852e0000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE [dbo].[DeadLockTest] set [col1] = @1    
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=4 stmtstart=106 sqlhandle=0x020000000f8d9d36c56b96bdcb04670ca5b75bc17de9868c0000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest SET col1 = 1    
2014-04-11 15:24:22.22 spid18s         inputbuf
2014-04-11 15:24:22.22 spid18s     BEGIN TRAN
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest SET col1 = 1   
2014-04-11 15:24:22.22 spid18s        process id=process4f04fb0c8 taskpriority=0 logused=144 waitresource=RID: 8:1:303:0 waittime=650 ownerId=32286 transactionname=user_transaction lasttranstarted=2014-04-11T15:22:53.877 XDES=0x4e9fd8bc0 lockMode=U schedulerid=2 kpid=4148 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=5 lastbatchstarted=2014-04-11T15:24:21.570 lastbatchcompleted=2014-04-11T15:24:12.090 lastattention=2014-04-11T15:23:49.210 clientapp=Microsoft SQL Server Management Studio - Query hostname=SIVA-PC hostpid=4024 loginname=Siva-PC\Siva isolationlevel=read committed (2) xactid=32286 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-04-11 15:24:22.22 spid18s         executionStack
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=1 stmtstart=16 sqlhandle=0x02000000ca356b202147fbe58aa2b109b537cf37cb3083430000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE [dbo].[DeadLockTest2] set [col1] = @1    
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=1 sqlhandle=0x0200000087d432229b1acc5bc82908fa19f6f23bbc0e4e820000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1    
2014-04-11 15:24:22.22 spid18s         inputbuf
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1   
2014-04-11 15:24:22.22 spid18s       resource-list
2014-04-11 15:24:22.22 spid18s        ridlock fileid=1 pageid=301 dbid=8 objectname=ReplB.dbo.DeadLockTest id=lock4edbe2480 mode=X associatedObjectId=72057594039828480
2014-04-11 15:24:22.22 spid18s         owner-list
2014-04-11 15:24:22.22 spid18s          owner id=process4f04fb0c8 mode=X
2014-04-11 15:24:22.22 spid18s         waiter-list
2014-04-11 15:24:22.22 spid18s          waiter id=process4f9025498 mode=U requestType=wait
2014-04-11 15:24:22.22 spid18s        ridlock fileid=1 pageid=303 dbid=8 objectname=ReplB.dbo.DeadLockTest2 id=lock4f5abe780 mode=X associatedObjectId=72057594039894016
2014-04-11 15:24:22.22 spid18s         owner-list
2014-04-11 15:24:22.22 spid18s          owner id=process4f9025498 mode=X
2014-04-11 15:24:22.22 spid18s         waiter-list
2014-04-11 15:24:22.22 spid18s          waiter id=process4f04fb0c8 mode=U requestType=wait


The above given information will be helpful in identifying the transactions involved, the logins involved, time of the deadlock etc.

You can start analysing the issue after gathering these informations.

How to turn off trace flags

Once you have obtained the necessary deadlock information from the log file, you can turn off the trace flags.

Command to turn off the traceflags

DBCC TRACEOFF (1204, -1)
DBCC TRACEOFF (1222, -1)

There is no need to restart the instance for turning these trace flags on/off.

Hope this helps.

Changing Schema/Role Ownership

Though you can change ownership using SSMS, it is always good to know the command to perform the operation.


ALTER AUTHORIZATION ON SCHEMA/ROLE::[name] TO [new owner]

This will also be helpful when you need to change ownership on many objects at a time.

Query to get a combined count of parents (with no children) and children

One of my good friends approached me this morning with this scenario.

1. Parent Id and ChildId are stored in the same table.
2. The relationship goes only one level deep (i.e. a child record is not a parent record for any other child record)

Requirement:
He wanted a combined count of
1. All Parent Ids that do not have any child records
+
2. Count of all Child records.

Here is the script I wrote and gave him.

Hope it is useful to you as well.. :-)



-- CREATE A TABLE FOR OUR SCENARIO
CREATE TABLE T1 (ID INT IDENTITY PRIMARY KEY, PARENT INT REFERENCES T1(ID))
GO 

--POPULATE THE TABLE FOR OUR SCENARIO
-- EXECUTE NEXT 6 INSERT STATEMENTS..THESE 6 STATEMENTS SHOULD CREATE 
-- ABOUT 25 RECORDS OUT OF WHICH 20 RECORDS WILL QUALIFY FOR OUR SCENARIO    
INSERT T1 (PARENT) VALUES (NULL)
GO 10
INSERT INTO T1 (PARENT) VALUES (1)
GO
INSERT INTO T1 (PARENT) VALUES (2)
GO 2
INSERT INTO T1 (PARENT) VALUES (3)
GO 3
INSERT INTO T1 (PARENT) VALUES (4)
GO 4
INSERT INTO T1 (PARENT) VALUES (5)
GO 5

-- DO A SELECT TO MAKE SURE IT LOOKS GOOD.
SELECT * FROM T1
GO

--EXECUTE THE NEXT QUERY THAT GIVES THE RESULTS.
WITH COUNT1(ID) AS 
( SELECT COUNT(DISTINCT a.ID) ID
  FROM T1 a
  WHERE PARENT IS NULL
  AND   NOT EXISTS (SELECT 1 FROM T1 b WHERE a.ID = b.PARENT) 
  UNION
  SELECT COUNT(DISTINCT t2.ID) ID
  FROM T1 t2
  WHERE PARENT IS NOT NULL
 )  
SELECT SUM(ID)
FROM   COUNT1
GO

Basic Database Maintenance Activites

I have been thinking about the core database server maintenance activities that are necessary for a DBA. Here are the basic database maintenance activities that I perform in my servers.

1. Setup Backup Jobs
One of my friends told me once that a DBA needs to have a good backup in place or a good resume in hand..!! Backups are the most important responsibility in a DBA's day to day activities. I usually setup a full backup (once a week or daily depending on the size of the database) and differential backups (only if the full backups are taken once a week) and transaction log backups every hour. I usually retain the backups for 2 weeks (There is also a system level backup happening in my company that is written to tape). I also try to store backups in a separate external hardware from the one where the data files are living..(Though it is not possible always..:-( ). It is also very important to have email notification setup to inform the DBA incase of any failures.

2. Setup Alerts
I usually setup alerts for any SQL Server event with severity 19 or more. This can be easily done using SQL Server Management Studio. I will write a separate post about setting up alerts. The most important thing is to setup email notification when the event happens.

3. Database Integrity Check
A corruption free database means peaceful life for a DBA. I usually check my database consistency once a week. This can also be done using a maintenance plan in SQL Server Management Studio.

4. Index Rebuild / Reorg
A fragmented Index can cause severe performance issues and could be a DBA's nightmare when he is on a hot seat to solve a performance issue. It is always good to be proactive and check the fragmentation level of all the indexes and perform rebuild or reorg as necessary. The thumb rule is to leave the index as is if the fragmentation is less than 10% ReOrg if it is less than 30% and Rebuild if it is more than 30%. Do not use the maintenance task for Index rebuild or Update Statistics if your database is huge. This will cause the job to rebuild all the Indexes in the database which could possibly run for days and would never end. Instead, it can be easily done using a script from the Following Link from books online. This script would only rebuild or reorg an Index if it is necessary.

I am sure there are other important maintenance activities as well. But, according to me, these are the core maintenance activities that any DBA should perform in his/her servers.

Hope this helps..!!

Range Non Existence(^) Searches Using PatIndex

We all know that PatIndex is one of the cool features of T-SQL. It helps to locate the position of the pattern in a given string.

We can also use PatIndex to do range searches. For Example, "[A-Z]" would search for any alphabetical character. "[0-9]" would search for any numeric character.  With the use of these range searches, it gets much easier to find out data issues.

Let us execute the following queries to generate some data for our learning.

  CREATE TABLE #t1 (PATCHECK VARCHAR(20))

  INSERT INTO #T1 (PATCHECK) VALUES ('ABCDEFGHIJKLMN')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA PAT INDEX CHECK')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA 123')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA 123 !@#')

  INSERT INTO #T1 (PATCHECK) VALUES ('123456789')

  INSERT INTO #T1 (PATCHECK) VALUES ('!@#$%^&')

if you select everything from this table, this is how it will look like.

Query  - 0
  SELECT * FROM #T1
    ABCDEFGHIJKLMN
    SIVA PAT INDEX CHECK
    SIVA 123
    SIVA 123 !@#
    123456789
    !@#$%^&


Now, Let us execute a simple PatIndex Search. 

Query-1

  SELECT *
  FROM #T1 
  WHERE PATINDEX('%SIVA%',PATCHECK) > 0 

This query would return any row that has the string "SIVA" in it. The results will look like below.     
   SIVA PAT INDEX CHECK
   SIVA 123
   SIVA 123 !@#

Range Search
Here is the example for a range search.
The below mentioned query means that any row that does not have an alphabetical character. 

Query - 2

  SELECT *
  FROM #T1 
  WHERE PATINDEX('%[A-Z]%',PATCHECK)  = 0

results would look like

  123456789

    !@#$%^&

The below mentioned query means that any row that contains (notice the difference in the operator. Previous one was =0, this one below is > 0) alphabetical character. 

Query - 3

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[A-Z]%',PATCHECK)  > 0

results would look like 

   ABCDEFGHIJKLMN

   SIVA PAT INDEX CHECK

   SIVA 123

   SIVA 123 !@#

Range Search with Not Operator

"Not Operator" is added in front of the range set like this [^A-Z] . This means that any non alphabetical character. 

Query - 4 

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[^A-Z]%',PATCHECK)  > 0

results would look like 

SIVA PAT INDEX CHECK

SIVA 123

SIVA 123 !@#

123456789

!@#$%^&

Note that even though query 2 and Query 4 looks similar, they are not producing the same results. 

Multiple Range Searches 

It is also possible to combine multiple range searches in a single query. The below mentioned query would return any row that does have only alphabets or numeric. 

Query - 5

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[^0-9][^A-Z]%',PATCHECK)  = 0

results would look like. 

ABCDEFGHIJKLMN

123456789

Hope this helps..!!

   

Status of All Constraints in a Database.

One of my developers today reached out to me about knowing the status of the constraints and triggers in a database. He wanted to know whether a constraint was in enabled or disabled state.

The reason behind this question was that he was planning a huge data load in that database and was planning to disable all the constraints and triggers before the load and put the constraints back in the same state after the data load. He wanted to know if some constraints were disabled on purpose and did not want to enable them by mistake after the data load.

Here are some facts about the status change of constrains

• Default, Unique and Primary Key constraints cannot be disabled. 
• Check or Foreign Key constraints can be disabled and enabled.

So, If a default, Unique or Primary Key constraint exists on the database, it can only be in the active state.

The following two queries can give the list of Check and Foreign Key constraints with their status.

SELECT name AS check_constraint_name,

       OBJECT_NAME(parent_object_id) Parent_Object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.check_constraints

SELECT name AS ForeignKeyName,

       OBJECT_NAME(parent_object_id) AS TableName,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.foreign_keys

I gave him one more query that would give the list of triggers in the database with their status.

SELECT name as trigger_name,

       OBJECT_NAME(parent_id) parent_object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.triggers

WHERE  OBJECT_NAME(parent_id) IS NOT NULL

 

Hope this helps.

Thanks and Regards,

Siva.

Email Notification when a SQL Agent job status has changed.

We can setup email alerts whenever a SQL Server Agent job fails, completes or succeeds. But what do we do when a SQL job is disabled by someone. It can go unnoticed and lead to many problems.

Today I found that one of my production DB Backup jobs was disabled by someone and I did not know it for a few days.

Here is a trigger that you can use to send you an email whenver a job status is changed.

USE [msdb]
GO

/****** Object:  Trigger [dbo].[tu_sysjobs]    Script Date: 9/5/2012 5:09:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tu_sysjobs] ON [dbo].[sysjobs]
FOR UPDATE
AS
/*
  Author : Siva Ramasamy
  Date   : 09/05/2012
 
  Description :  This trigger will send an email notification to the operator specified in the variable @v_operator_name whenever a job status is changed.
                 This trigger uses the default profile that is existing to send email notification.

  Pre Requisites : Database mail must be configured inorder for this trigger to fire.
 
*/
BEGIN  

    DECLARE @v_mail_body      VARCHAR(245)
    DECLARE @v_profile_name   VARCHAR(245)
    DECLARE @v_operator_name  VARCHAR(245)
    DECLARE @v_mail_subject   VARCHAR(245)


    SET @v_operator_name = '******PUT YOUR EMAIL ADDRESS HERE******'
    SET @v_mail_subject  = 'TMP PRODUCTION SQL Agent Job Status Change notification'

    SELECT    @v_profile_name = mp.name
    FROM      msdb.dbo.sysmail_profile mp
             INNER JOIN msdb.dbo.sysmail_principalprofile pp
             ON         mp.profile_id = pp.profile_id
    WHERE   pp.is_default = 1

    IF UPDATE(enabled)
    BEGIN      
        SELECT  @v_mail_body = 'Status of the job "'+
                              s.name + '" has changed from ' +
                              CASE d.ENABLED WHEN 1 THEN 'enabled'  ELSE 'disabled' END + ' to ' +
                              CASE s.ENABLED WHEN 1 THEN 'enabled.'  ELSE 'disabled.' END
        FROM  sysjobs s
        INNER JOIN DELETED d
        ON  s.job_id = d.job_id
        WHERE    s.enabled <> d.enabled

        PRINT 'profile name '+@v_profile_name

        EXEC msdb.dbo.sp_send_dbmail
             @profile_name = @v_profile_name,
             @recipients   = @v_operator_name,
             @body         = @v_mail_body ,
             @subject      = @v_mail_subject;
    END 
END
GO