RSS

Monthly Archives: June 2016

Alwayson Availability Groups for Disaster Recovery Solutions

Introduction

There are many users, who are not aware about disaster recovery planning with always-on availability groups. Even they do not know the terms that come in the utilization of Always-On Availability groups for disaster recovery planning. In the following section, we will discuss about the always-on availability groups for resolving the disaster Read the rest of this entry »

 
 

Tags:

Help! I have -2, -3, or -4 Session ID!

We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error:

Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.

In order to kill the session ID, you need to find the unit of work (UOW) guid.

SELECT DISTINCT(request_owner_guid) AS UOW
  FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2,-3,-4)

Now you can kill this using UOW:

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.

The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?

Session ID Description
-2 The blocking resource is owned by an orphaned distributed transaction.
-3 The blocking resource is owned by a deferred recovery transaction.
-4 Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

Reference: Books Online, sys.sysprocesses (Transact-SQL)

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on June 22, 2016 in SQl server Administration

 

Tags: , , , ,

Dissecting SQL Server Execution Plans

I remember my days before, Microsoft SQL Server PFE.  I wanted to learn everything and know everything about SQL Server.  However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator. 

Along the way I did pick up lots of books and references.  One of such books is Dissecting SQL Server Execution Plans. 

I read this book before becoming PFE, I read this now, and I recommend everyone read this book more then once. 

Grant Fritchey (Blog|Twitter) wrote the book back in 2008; I would still recommend take ready.  This book will only help you be better DBA and Developer.

SQL Central, Jeff Moden, Dissecting SQL Server Execution Plans

http://www.sqlservercentral.com/articles/Book+Reviews/69019/

Amazon, SQL Server Execution Plans

http://www.amazon.com/gp/product/1906434026?ie=UTF8&tag=dkranchnet&linkCode=as2&camp=1789&creative=390957&creativeASIN=1906434026

SQL Central, Red Gate, EBook

http://www.sqlservercentral.com/articles/books/65831/?utm_source=ssc&utm_medium=weblink&utm_content=Grant&utm_campaign=sqltoolbelt

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on June 1, 2016 in Performance MSSQL

 

Tags: ,