How to find the MSSQL Lock on the database directly?
Connect to the MSSQL database and open the query editor for service desk database(any database should work)
1. Execute the query sp_who2
2. Above command will show the list of database query process which is currently in execution that causes the database table locks.And the query which block
thesubsequent operation process can be identified by viewing the BlkBy ( Blocked by ) column in the output of the above "sp_who2" command execution.
Find the BlkBy id's for the ServiceDesk Plus database.
From the above screenshot, the BlkBy id's for ServiceDesk database can be identified as 51 and 58.
3. And to identify the exact query which causes the locking, we need to execute dbcc inputbuffer(specify the query id identified in the BlkBy column).The query is displayed on the field called EventInfo,ff the query isn't visible in full,you may click on the query and copy that to a notepad.
For example results for dbcc inputbuffer(58) is displayed below.
These queries with the help of the Thread dump and the serverout logs should help us identify the root cause.
These queries with the help of the Thread dump and the serverout logs should help us identify the root cause.
New to ADSelfService Plus?
Related Articles
How to change the database from MySQL/PostgreSQL to MSSQL
The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB. For ...How do I convert the database from PGSQL to MSSQL?
1. Stop ManageEngine ServiceDeskPlus – MSP service. 2. Take a backup of the existing data and configuration under PGSQL database by following the below steps. Open CMD as an administrator and execute command to start the data backup. ...MSSQL error 'Invalid Object name'
When you get a similar error as mentioned below java.sql.BatchUpdateException: Invalid object name 'TABLE NAME' please change the COLLATION in the MSSQL server for the database 'servicedesk' as CASE-INSENSITIVE. You can get this error at the time of ...The transaction log (Servicedesk_log.ldf) for database 'servicedesk' is full
There are two methods to fix this issue. Method 1 To fix this issue, we have to detach ServiceDesk database, after detaching we can delete the transaction log ( .LDF log) and attach the ServiceDesk database back. The important part in this process of ...How to invoke mssqlLockStatistics.bat ?
The mssqlLockStatistics.bat file is located under [SDP-Home]\bin folder on the ServiceDesk Plus server. This bat file will catch hold of queries which are blocked in the Database. It will generate 2 files sp_who.html and block.txt. Both these files ...