Getting DeadLock in SQL Server even after restarting server

0
votes

I am getting deadlock error when updating a table :

Transaction (Process ID 67) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

All other works perfectly fine. The thing making me uncomfortable is I have restarted the server itself 4-5 times how is the dead lock still on table.

Any suggestions?

1
sql-serversql-server-2016
It looks like an open transaction than needs a commit or rollback – Peter Smith
How can I narrow it down to that ? As I am only good with making , table and using them when it comes to sql server. (also a little bit of indexing) – confusedMind
Or you could kill the process (in Activity Monitor) – Peter Smith
Sounds like your table has an update trigger that's trying to insert/update rows in a different table that is then conflicting with other processes. You could try select * from sys.dm_exec_sessions where open_transaction_count > 0 to look for long-running transactions in other sessions and then investigate what they're doing. If you've rebooted/restarted SQL Server multiple times already it sounds like something (a service/application) has a persistent problem somewhere. – AlwaysLearning
Deadlocks are most often due to suboptimal plans (scans) that touch more data than needed. Perhaps the plan changed at some point. Add the deadlock xml report to your question (see this answer) and upload the plan to Paste The Plan. – Dan Guzman

1 Answers

0
votes

Transaction (Process ID 67) was deadlocked on lock | communication buffer resources

This is probably caused by a bug while running a parallel query. Upgrade SQL Server to a later version, or disable parallelism for this query or the whole server.