How to write script to kill MS Office database locks in MSSQL

I've recently inherited a MSSQL database as our DBA has moved on to greener pastures. I'm not a DBA and have limited to moderate SQL knowledge but have had to absorb some of his duties and have an issue I am having some trouble navigating around.

We have had an issue where users who access the database via Excel have a tendency to grind database operations to a halt and it impacts our ERP software. Our current method of resolution is to look at the activity monitor in SSMS and kill the offending Microsoft Office application.

I'd like to have a scripted task that runs every few minutes to keep things moving and not have users flock to IT when the system goes down.

I've found that the following query will allow me to pull the lock type and other useful data such as wait time but not the specific application found in the activity monitor.

select * from sys.dm_exec_requests where wait_type = 'LCK_M_S'

Ideally, this code would find the application Microsoft Office with the wait_type of LCK_M_S and kill the longest waiting task and repeat as needed throughout the day.

Thank you in advance for your responses.

You can look at

select session_id, program_name, host_name
from sys.dm_exec_sessions

But those program names can be chosen by the client, so there's no guarantee that you can identify the Excel sessions.

A better solution would be to see if your ERP software supports READ COMMITTED SNAPSHOT isolation, and set the database option that would prevent your Excel users from needing Shared Locks to run queries.

StackExchange Database Administrators Q#315694, answer score: 6