We regularly get long periods of time where the sales line table will be locked. Earlier today there was about an hour period where the table was intermittently locked stopping users form entering anything in the sales line.
Does anyone have any tips on how you to easily identify which user is causing the table lock? It would be good to find out what the user / users were doing to cause the lock.
which Navision version?
We are on 2017
Hello Daniel,
You won’t be able to find out via the debugger.
You won’t be able to find the user.
You can only find the root cause by doing some investigation
At the moment when the lock happens have a look into SQL Server processes and find out which function or codes generates the issue.
Other approach would be to log blocked processes.
What is the exact error message?
best regarss,
Thomas Barbut
Database Locks…option via the debugger
Use SQL Management Studio/or Visual Studio Code with SQL Vsix-file:
SELECT DISTINCT
name AS database_name,
session_id,
host_name,
login_time,
login_name,
reads,
writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> ‘DATABASE’
–AND request_mode LIKE ‘%X%’
–AND name =‘YourDatabaseNameHere’
ORDER BY name
Or just test how busy your database is:
use [Demo Database 365BC]
select spid,cmd,waittime,lastwaittype,cpu,physical_io,login_time,last_batch,status,hostname,program_name,nt_username, nt_domain
from master.dbo.sysprocesses where dbid = db_id(’[Demo Database 365BC]’)
Hope this helps
https://saurav-nav.blogspot.com/2016/11/microsoft-dynamics-nav-2017-sql.html
and
https://saurav-nav.blogspot.com/2016/11/microsoft-dynamics-nav-2017-setup-sql.html
Thank you . Just what i was looking for
