SQL Server – Lock request time out period exceeded – Error:1222

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.

 

sql server time out error
Lock request time out period exceeded. (.net sqlclient data provider) Error: 1222

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.

DBCC opentran()

Transaction information for database ‘salesdb’.

Oldest active transaction:

SPID (server process ID): 55

UID (user ID) : -1

Name: user_transaction

LSN: (18:279:2)

Start time : Mar 22 2012 4:07:40:717PM

SID: 0x0105000000000005150000007154b16e38cc0bf710baa55dacfc0000

DBCC execution completed. If DBCC printed error messages, contact your system

administrator.

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.

sp_who2 55

Command

SELECT INTO

CPUTime

297579

DiskIO

94778

 

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.

sp_lock 55

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)

Use MASTER

KILL 55

OR

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 runing on sql server.

After killing the process you can easily check the properties of the database.

 

Leave a Reply

  

  

  


− 1 = 1