Advertisements

Archive

Author Archive

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

June 21, 2016 Leave a comment

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.

Advertisements

Issues with CU6 for SQL Server 2014 SP1

June 14, 2016 2 comments

Microsoft released an update to CU6 (link) for SQL Server 2014 SP1; this update was to address an issue where even having NOLOCK hint in queries was leading to blocking and deadlocks in the default SQL Server lock-based isolation level or high levels. From the KB article:

Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, and specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries that try to access SourceTable will be blocked.

While one transaction is holding an exclusive lock on an object (for example, an ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable by using the NOLOCK hint. In this scenario, the SELECT query that is trying to access SourceTable will be blocked.

Reference: CU6 for SQL 2014 SP1 (Deprecated) [Link].

Therefore if you have the older build installed (12.0.4449) please update to newer build (12.0.4457).

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

 

Dissecting SQL Server Execution Plans

May 31, 2016 7 comments

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.

SQL Server 2016 Launch Event in Edmonton SQLPASS

May 24, 2016 1 comment

SQL Server 2016 release date is June 1st; to help get the moment started, Edmonton SQL Pass is holding a launch event on June 25, 2016.  Keep your calendar clear and come on down.  Not a lot of seats open.  Register here!

I am going, see Edmonton SQL Geeks there :).

 

SQL Skills: SQL Server Wait Types & Latch Classes Library

May 17, 2016 Leave a comment

Microsoft has documented wait types and latches in a minimum fashion on books online; often there are wait types that are just not documented.

Paul Randel (Blog|Twitter) has put together compressive list of these with recommendations and even SQL dump traces.

Check it out here..

 

SQL Server 2016 Release Date

May 3, 2016 Leave a comment

SQL Server 2016 GDR has been set to release on June 1, 2016 (Ref).

SQL 2016 is going to be a major blast in how SQL Server is used for OLTP, OLAP and how it integrates with Microsoft Azure.  If you are not already playing with it, take time to install it and explore the gems hidden in multitude of new functionality.

 

Categories: Announcement, SQL 2016 Tags:

SQL Server 2014 & 2016 Developer Edition

April 26, 2016 Leave a comment

Microsoft SQL Server has been in developer edition as far back as 2005 (if my memory serves me); however each license was $50.00.  Now with SQL Server 2014 & 2016, Microsoft has made developer edition free.

There is no better time to get on board with SQL Server 2016, there are lots of new functionality and features available.  That can help your organization reach new levels of performance, scalability, and data insight.

Download and start playing with SQL 2014 here.

 

SQL Server 2016 RC3 Released

April 19, 2016 2 comments

SQL Server 2016 RC3 has been released, it is the final release before RTM.  In this release no major functionality is introduced.

Download from here.

Categories: Announcement, SQL 2016 Tags:

SQLPASS Edmonton (Apr. 9, 2016) – Key Note Presentation

April 10, 2016 6 comments

Friday/Saturday was a great day, got to meet lots of wonderful people from US/Canada at the Alberta’s first SQL Saturday.  I delivered they Key Note speech for SQL Server 2016.  I would love to hear back from people, so please provide evaluation on SQL Saturday site and you can download the PowerPoint deck here.

 

 

How to get orphaned logins for all databases?

April 4, 2016 2 comments

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name.  This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO
 
CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO
 
sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DP.sid
      , SP.name
      , DP.name
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
   AND DP.sid IS NOT NULL'
GO
 
  SELECT *
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO

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

Categories: Administration, Security Tags:
%d bloggers like this: