Blocking Due to SPID in Sleeping State in SQL Server


Blocking is a common issues noticed in any of any relational database management system (RDBMS) which uses lock-based concurrency. In SQL Server, blocking generally occurs when one SPID holds a lock on a specific resource and a another SPID attempts to acquire a conflicting lock type on the same resource. Generally, the time frame which the first SPID holds locks on the resource is very small, so when it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and happens many a times throughout with no noticeable effect on system performance.

It is common for DBAs to notice blocking on various servers and typically the locks held for a short time does not cause any issues, nor is identified by any monitoring tool. Sometimes, blocking can get severe and cause performance issues from timeouts to slowness, etc. DBAs generally deal with blocking issues by checking on the head or lead blocker causing the blocking and try to understand why the head blocker is taking long time to complete and it is not very important, then DBAs kill the SPID. If the blocking issue is persistent then DBAs identify the culprit and tune it by adding indexes or rewriting the query.

However, sometimes there can be strange blocking issues, one of such issue is blocking caused by a SPID which is in sleeping state. In general, a SPID which finishes its task will leave any locks held, so the sleeping sessions are not expected to cause any blocking issues, however in some environments, persistent blocking issues are noticed due to SPID which is in sleeping state still holds the locks and does not release it until the connection is closed from the application of till the SPID is killed by the DBAs. If we try to understand why the blocking occurs with a session in sleeping state, there could be different reasons and some of the are mentioned below.

– Mostly this issue happens when someone uses SQL Server Management Studio(SSMS) query analyzer window, runs some bad queries and leave the session open. The queries which were run either have a explicit open transaction or an implicit transaction which holds locks and does not release the locks because no commit or rollback was issued. We need to identify such sessions, the user who is running queries from that session, what queries are run, what locks are held, from which application are the session from, hostname, etc and then DBAs can go ahead and kill the sessions. Then DBAs need to contact the respective user or management and report the issue to avoid happening in future.

– This can also happen when an application opens a connection with SQL Server, runs some query and then due to some issues, the application connection is lost either due to application crash or hang or some application problems. No signal is sent to SQL Server due to sudden crash, so SQL Server still assumes that the application may still send additional queries and keeps the session available. This is due to bad application design and needs to fix the application code.

– This can also happen if application has bad code, which opens transactions and fails to commit or rollback in case of query failures or other issues.

– Applications also uses session pooling, where is sessions are reused when required. Periodically applications need to close sessions which have not been reused after sometime.

– We can also suggest developer to use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client. It is important to note that T-SQL statements following the statement which caused the error will not be executed.

– Some older or incompatible client components may cause these kind of issues too, so need to keep them updated and use latest and compatible connectivity components.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Tips for DBAs to Troubleshooting SQL Server Performance Issues


Troubleshooting SQL Server performance issues is one of the common and important task of DBAs. It is not very easy to troubleshoot performance issues without prior experience or good knowledge about how SQL Server query execution works and what factors can contribute to performance issues with the SQL Server instances. It is very common to see many entry level DBAs or even DBAs with more than five years of experience still struggle dealing with performance issues, as there are just many things that can cause the performance problems and many symptoms may be interrelated and not easy to diagnose and come to a conclusion after which the solution to be found and implemented. In many organizations troubleshooting performance problems take many hours to days and even weeks too.

To help DBAs to be able to find a quick list of things which they can check or look into in case any SQL Server performance issues are related. It is important to note that troubleshooting performance issues with SQL Server is very broad ranging from SQL Server configuration, OS configuration, Network or other hardware configuration, blocking, waits, fragmentation, outdated statistics, etc. Below are some of the things which DBAs can look into when performance issues are reported with SQL Server instances.

– Understand about the problem. Generally performance issues are reported by developers, Managers, application users or some monitoring tools. Depending on the source of the issue reported, gather data by asking questions like what do their interpretation of performance issues mean, why do they think SQL Server is cause of performance issues, what data they have collected, what symptoms do they see with application, did they collect any data so far, etc. Collecting as much data can be helpful to move in the right direction.

– If the performance issues are reported by all users, that means it is general slowness, can be anything from Hardware configuration issues to SQL Server query problems. We can start looking into basic SQL Server configuration settings like MaxDoP, Max Server Memory, SQL Server version and patch details, platform, etc. Gather OS configuration details like CPU, Memory and disk details and if they are as per best practices like data and log files on different disks, tempdb on its dedicated disk, etc. Also, sometimes OS settings related to instant file initialization, lock pages in memory, CPU power plan set to balanced can show impact too.

– Quickly check on perform to see if there are any resource bottleneck. Look into counters for CPU, Memory, Disk IO and network to see any issues or any values which are outside of expected values.

– From SQL Server, we can check if there are regular maintenance like rebuild of indexes and statistics updates. Check for fragmentation and when stats were last updated. Check if there are any jobs currently running, if jobs related to reindexing, stats update, checkdb or backups running, then stop them. Check if there is any blocking happening on the SQL Server instance, if found, check the headblocker and take appropriate action.

– Check wait stats on the server and see the top contributing wait and see if we can related to any physical resource bottleneck.

– Check currently running sessions which are taking more time and see why they are taking more time. Try tune them by creating any missing indexes or rewrite queries, etc.

– If the application is related to Vendor, then check for any best practices from Vendor for improving the performance.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Unable to Connect to SQL Server Embedded edition Instance Used by SharePoint

On one of the servers, we started receiving alerts related to SQL Server instance MICROSOFT##SSEE. DBAs were not aware of this SQL Server instance as none of the DBAs have installed it on the server. We tried to connect to this SQL Server instance from SQL Server Management Studio, but it returned below error.

Cannot connect to SERVER\MICROSOFT##SSEE.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

After a quick search, found that this instance is related to Windows Internal Database. SSEE refers to SQL Server Embedded edition as well in previous versions. This is generally gets installed and used by Windows Server Update Services (WSUS) or Windows Sharepoint Services (WSS).

At times, there can be requirement to connect to this particular instance to manage or administer using SQL Server Management Studio (SSMS) by DBAs, however we cannot connect to it using TCP/IP or SharedMemory protocols, we would need to use NamePipes to connect to this instance.

We will need to use the below name in the server name box in SQL Server Management Studio (SSMS)

If you do not have SQL Server Management Studio installed on the system, then best way to go would be to download and install SQL Server Express Edition Management Studio (SSMS). Once installed, we can use the above mentioned command to connect to this instance and administer it if required.

What is Windows Internal Database?
Windows Internal Database is a relational data store used by the following applications and services.
Active Directory Rights Management Services (AD RMS)
Windows Server Update Services
Windows System Resource Manager

These applications and services share a single instance of the data store. Windows Internal Database is installed by the first application or service that requires it. Because multiple applications and services use the same instance of Windows Internal Database, removing these applications and services does not remove the data store. If you have removed all applications and services that use Windows Internal Database, you can then remove it by using the Server Manager Remove Features Wizard.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Unable to Open Logshipping BackJob Properties for Database with AlwaysON AG Created

Recently while working on a production deployment, faced an issue with SQL Server LogShipping. As, it was planned maintenance, we had to find and fix the issue immediately. Writing this blog, so that others can get an idea on what can be done when faced with this type of issue.

Coming to the issue, we had three SQL Server 2012 servers Server01, Server02 and Server03. AlwaysON Availability Group had been setup between Server01 and Server02. Logshipping is setup between Server 01 and Server 03.

We had a planned maintenance where the two servers Server 01 & Server02, hardware was planned to be replaced, so we planned in a way that we will take down one server at a time and perform the maintenance, while the other server will be serving production traffic.

So, we failed-over the AlwaysoN AG with listener name as “Prod_RPT_LSTNR” from Server01 to Server02 which was successful. We disabled Logshipping backup job on Server01, disabled copy and restore jobs. on Server03. Configured logshipping from Server02 to Server03, which completed successfully, new backup, copy and restore jobs started working fine. Now Server01 was taken down for maintenance.

Later, logshipping started failing with errors related to backup path being incorrect. We checked and found that drive where we pointed backup path was removed and we were told that the drive cannot be added back, so we had to change the backup path in logshipping.

We tried to change the path and tried to click on the Logshipping backup job button in logshipping configuration tab, but it was returning below error

TITLE: Microsoft SQL Server Management Studio
SQL Server Management Studio could not save the configuration of 'Server01' as a Primary.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
An entry for primary server Server01, primary database ReportServerTempDB does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

– After some troubleshooting and research, we found that, when we configured logshipping from Server02 to Server03, we connected to the SQL instance Server02 from SSMS via AlwaysON AG listener name which was Prod_RPT_LSTNR, but later when we tried to change the backup settings we connected to SQL instance Server02 SSMS using server name which was Server02. Now, we connected back again to SQL instance Server02 via SSMS using Listener name, then it allowed us to click on logshipping backup job button and we were able to open the job properties with out any errors.

Running below command on Secondary server, will provide us with the details of logshipping primary database like primary_server, primary_database, backup_source_directory, backup_destination_directory, file_retention_period, etc.

@primary_server = 'SQLInstanceName',
@primary_database = 'DatabaseName'

Running below command on Primary server will provide details of primary database settings

@database = 'DatabaseName'

Now, important thing to understand here is that, when we configure logshipping in environments where we have multiple ways of connecting a SQL instance, example via AlwaysON Listener name, IP Address, Alias, etc, we need to connect using the same method later as well, if we want to make any changes to the logshipping configuration. This happens because, the SQL Server instance name which we used to connect will be stored in MSDB logshipping metadata and will look for same server name later, so if later we try to connect using another method by specifying different instance name like IP Address/listener/alias, it will mis-match with the metadata and will return errors.

Hope this was helpful.

SQLServerF1 Team


What Happens to the Connections When we Move or Failover SQL Server Group to Another Node

It is common to see many people ask this question, What happens to the connections from Application to SQL Server, when we move or failover SQL Server Group from one node to another Node?

If we understand what happens when we Failover or Move SQL cluster instance from one node to other, we will get the answer to the question.

We we initiate a failover, All the resources in that group which include(SQL Server resource, SQL Server Agent resource, Multiple Disk Resources, Network Name and IP Address resource, other third party resources like backup, fileshare resources) will be taken OFFLINE on that node, then these resources are moved to the new node and then all these resources are brought ONLINE. Once all the resources are ONLINE, we can make connections to the SQL Server.

As we can see from the above process, SQL Server will be stopped, which means that any existing connections will fail, it may be possible that these failed transactions already would have modified some data in the database, but as per design of SQL Server engine, this ingormation is also logged into SQL Server Transaction log file(.ldf). When SQL Server starts on new node, it will read the transaction log file to see which transactions were committed and which were not, based on that it will rollback any incomplete work done by uncommitted transactions.

Hope this answers the question.

Happy Reading,
SQLServerF1 Team


Authentication Mode Changed After Applying Service Pack 4 for SQL Server 2005

I recently worked on an issue wherein after applying Service Pack 4(SP4) for SQL Server 2005 on a cluster, we were unable to connect to the SQL Server instance using SQL Server authentication.

To explain it better, let’s say, there are 2 nodes in a cluster with names nodes Node1 & Node2, SQL Server 2005 instance was active on Node1. SP4 was run from Node1 which first applies the SP4 on passive node and then applies on active node. From Management studio both windows and SQL authentication works fine on Node1, but once the SQL instance is failed over to Node2 it comes online without any issue, but when we try to connect to the SQL instance using management studio using SQL Server authentication, it gives login failed error message.

Based on our initial research we found that the connectivity issue occurred because the Authentication Mode of the SQL instance was changed to WINDOWS ONLY on passive node whereas on active node the authentication mode was still MIXED. We changed the following registry key value to 2 and now both the windows and SQL authentication works fine.

SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INST1\MSSQLServer\LoginMode

Now, the million dollar question was why the authentication mode was changed to WINDOWS ONLY on Node2?

Based on our research, we found that in the SQL Server 2005 setup code when patch/reinstall scenario runs on secondary cluster node the authentication mode is always set to NT authentication (WINDOWS ONLY).
This registry key which has LoginMode is part of SQL Server cluster registry checkpoint. Once the failover happens the login mode on the secondary node is updated automatically from the primary node to the correct value. There is no manual work required in this process and is transparent to the user. The following are the Engine registry sub-hives that are checkpointed under the InstanceID registry hive in SQL Server 2005:

“Cluster” , “MSSQLServer” , “Replication” , “SQLServerAgent” , “PROVIDERS”, “SQLServerSCP” , “CPE”

In my clustered environment, I found that only CPE has checkpoint, all other registry hives do not have checkpoint. That means any change applied to any registry key value which belong to registry hives other than CPE will not get updated automatically from primary node to the correct value. And LoginMode registry key is one such key which belong to MSSQLServer which do not have checkpoint on it. Hence, during SP4 installation once the value of this key is changed to 1, it will remain one until it is changed manually to 2. To resolve this issue, we have to make sure that the above registry hives should have checkpoint. KB article will help you in adding the checkpoint to the registry hives and the precautions you need to take in order to execute the steps. However, I have mentioned the steps below to add checkpoint to the above registry hives:

Run the following commands using Command Prompt:

Imp: Beware that change to checkpointed registry keys are saved only when the associated resources are online (In this case, SQL Server). When the resources are not online, changes to the checkpointed keys will be overwritten by the last saved values from the checkpointed file.
If the instance is default, please replace the InstanceName with MSSQLSERVER

cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\Cluster"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\Replication"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\PROVIDERS"

Once these changes are performed, try failover failback.

Happy Reading,
SQLServerF1 Team


Installation of SQL Server 2008 R2 on cluster may fail while trying to bring the resource online

Recently I worked on an issue wherein the installation of SQL Server 2008 R2 on cluster fails with the below error

The cluster resource 'SQL Server (CWTSQL02)' could not be brought online.
Error: The group or resource is not in the correct state to perform the requested operation.
(Exception from HRESULT: 0x8007139F)

In cluster administrator I found that SQL resource was created but SQL Agent resource was not created and the SQL resource was in failed state. I tried to start SQL service from services.msc console, but not able to start as cluster instance. However, I wasn’t able to connect to SQL Server without providing port number. As I wasn’t able to connect to SQL instance without using port number, it points towards issue related to SQL browser service. This can occur when the SQL browser service is not started or is not functioning properly.

Not to my surprise I found that SQL Browser service was in a stopped state. I tried to start it but it failed, so tried to start it from command prompt which failed with below errors

C:\Program Files (x86)\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c

SQLBrowser: starting up in console mode
SQLBrowser: Both SSRP and OLAP redirection services are disabled. Shutting down browser service

Then I looked at the below registry hive and could see the value of SsrpListener was 1, but AnalysisServiceListener was missing which should have been present if analysis services is installed on the server.
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser

SsrpListener – Value allows or disallows the SQL Browser to expose Engine instance discovery information, So if the value is 0 it disallows the SQL Browser to expose Engine instance discovery.

AnalysisServiceListener – Value allows or disallows the SQL Browser to expose Analysis Service discovery information.

Created the registry entry for AnalysisServiceListener with value 1. Even though the SsrpListener and AnalysisServiceListener are set correctly as 1, but still SQL browser was not starting.

Then I collected process monitor and from the log I found that the dll’s related to “some third party component” was loaded in SQLBrowser. We cannot name the third party component in this blog! If you encounter a similar issue, please manually verify if any third party dll (non-Microsoft) is loaded and interfering with SQL browser. After uninstalling the third party tool from the machine and then reboot it, we were able to bring the SQL browser online and then SQL Server resource online on cluster.

As a matter of fact, Microsoft has always recommended stopping any unwanted third party services and Anti-Virus for the duration of the setup as these components can interfere with the setup which may cause either setup to fail or may cause services to fail like the one we experienced.

You can find more information in below articles about Microsoft policy about antivirus and detouring

Keerthi Deep

1 2