Troubleshooting problems with locking in SQL Server 2014 (Part 2)
If you have problems with SQL Server 2014 locking, review this troubleshooting checklist to find
potential solutions.
1. Encapsulate transactions within SQL Server stored procedures.
This can reduce network traffic, because your client will send to server only the stored
procedure name (perhaps with some parameters), and reduce the amount of time the tables
are locked.
2. You can get the error 674.
This is the error message text: “Exception occurred in destructor of RowsetNewSS 0x%p.” This
error indicates a problem related to releasing pre-allocated disk blocks used during bulk-insert
operations. To resolve this problem, you should restart the server.
3. If you have problems with locks, consider disabling lock escalation.
To disable lock escalation you can turn on the trace flag 1211. You can turn on this trace flag
by using the DBCC TRACEON (1211, -1) command or by adding “-T1211” as a SQL Server 2014 startup
parameter.
4. You can get the error 1205.
This error indicates that transaction was deadlocked with another process and has been chosen
as the deadlock victim. In this case, your application should intercept the error 1205 and
respond by resubmitting the transaction.
5. To reduce deadlock, try to write Transact-SQL statements so that all concurrent
transactions will access objects in the same order.
Deadlock occurs when two transactions have data locked, and each transaction cannot release
its lock until other transactions have released theirs. If all concurrent transactions will
access objects in the same order, these transactions will not lock the different objects at
the same time and deadlock will not occur.
6. You can get the error 5252.
This error indicates that database cannot be shrunk to the expected size. This problem occurs
because the high concurrent workload is leading to too many deadlocks during the shrink operation.
To resolve this problem, you should re-run the shrink operation when the workload is lower.
7. Try to use as low isolation level as possible in your case.
SQL Server supports five isolation levels: read uncommitted, read committed, repeatable read,
snapshot and serializable. The lowest level is read uncommitted, the highest level is
serializable. Using a lower isolation level holds locks for a shorter duration than a higher
isolation level and reduces locking contention.
8. The error message “The database could not be exclusively locked to perform
the operation” may occur when you run any of the DBCC CHECK commands
(DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, DBCC CHECKCATALOG or
DBCC CHECKFILEGROUP).
This problem occurs when at least two connection are used the checked database and the database
contains at least one file group that is marked as read-only. To work around this problem, ensure
that there are no other connections when you run the DBCC CHECK command. To resolve this problem,
you can create a database snapshot and run the DBCC CHECK command against this database snapshot.
9. If you need to use updatable cursor, try to avoid using the SCROLL_LOCKS cursor option.
When the SCROLL_LOCKS cursor option is used, SQL Server locks the rows as they are read into
the cursor to ensure these rows will be available for later modifications. So, if you need to
use updatable cursor, avoid using the SCROLL_LOCKS cursor option whenever possible.
10. When you run a SELECT query that generates a parallel batch-mode scan, the
batch-mode scan may be involved in a deadlock situation.
In this case, the deadlock is not resolved automatically. This bug was first fixed in
Cumulative Update package 7 for SQL Server 2014. You can download the Cumulative Update
package 7 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3046038
11. Deadlock may occur when you execute the sp_estimate_data_compression_savings
stored procedure.
This problem occurs when the sp_estimate_data_compression_savings stored procedure is executed
on a table that previously had a “text in row” table option set but no longer has a text, ntext,
or image column type. This bug was first fixed in Cumulative Update package 9 for SQL Server 2014.
You can download the Cumulative Update package 9 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3075949
12. Scheduler deadlock on primary replica when you remove a replica from an AlwaysOn
availability group in SQL Server 2014.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 10
for SQL Server 2014. You can download the Cumulative Update package 10 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/3094220