SQL server time out error, while performing Health check task on production database server. we got a “lock request time out period exceeded error”. When we right clicked on database to see the properties of that database, got that error.
1. First thing we had done that executed below query to find out active transactions on the
database. After executing the query we found the SPID which was running from 4 hours.
Transaction information for database ‘salesdb’.
Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Start time : Mar 22 2012 4:07:40:717PM
DBCC execution completed. If DBCC printed error messages, contact your system
2. To find out more information regarding this SPID and transaction query. We executed
below query sp_who2 with SPID. We can see the numbers in the CPUTIME and DISKIO
column, that to for select query on sql server.
3. Check the locks information for the SPID by using sp_lock command, this will give to
which types of locks are placed on the database or table/page. We had seen that exclusive
locks were placed on the database.
4. We informed user that this query is taking too much time to execute on production so please
execute the same query after business hours. Also suggested user to use hints available in sql
server 2005 like NOLOCK and MAXDOP while executing select query on production.
5. Now kill the process, we can kill the runing process either using Kill command in sql server
or activity monitor with SSMS (SQL Server Management Studio)
Connect to SQL Server using SSMS(SQL Server Management Studio)/Click on the plus sign of management option, Here you can see the Activity monitor which will give you complete information regarding process running on sql server.
After killing the process you can easily check the properties of the database.