System. Data. sqlclient. sqlexception: the lock request timeout period has been exceeded.

Source: Internet
Author: User
When you open the sqlserver2008 manager, When you right-click a table in a database, the above error occurs, and the entire opening process is very slow, generally a deadlock.

The solutions found are as follows:

 
/* -- Process deadlocks to view the current process, or deadlocked process, and automatically kill the dead process. Because it is targeted to death, if there is a deadlock process, you can only view the deadlock process, you can use parameter control, whether there is a deadlock or not, only check the deadlock process -- login build 2004.4 -- * // * -- call the example exec p_lockinfo -- */create proc p_lockinfo @ kill_lock_spid bit = 1, -- whether to kill the deadlock process, 1 kill, 0 only displays @ show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 shows, 0 does not show asdeclare @ count int, @ s nvarchar (1000), @ I intselect id = identity (INT,), flag, process id = spid, thread id = kpid, block process id = blocked, database ID = dbid, database name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = CPU, login time = login_time, number of opened transactions = open_tran, process status = status, site name = hostname, application name = program_name, workstation process id = hostprocess, domain name = nt_domain, NIC address = net_addressinto # T from (select flag = 'deadlocked process', spid, kpid,. blocked, dbid, uid, loginame, CPU, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address, S1 =. spid, S2 = 0 from master .. sysprocesses a join (select blocked from master .. sysprocesses group by blocked) B on. spid = B. blocked where. blocked = 0 Union all select '| _ victim _>', spid, kpid, blocked, dbid, uid, loginame, CPU, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address, S1 = blocked, S2 = 1 from master .. sysprocesses A where blocked <> 0) A order by S1, s2select @ count =rowcount, @ I = 1if @ COUNT = 0 and @ show_spid_if_nolock = 1 begin insert # T select Sign = 'normal process', spid, kpid, blocked, dbid, db_name (dbid ), UID, loginame, CPU, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address from master .. sysprocesses set @ count =rowcountendif @ count> 0 begin create table # T1 (ID int identity (255), a nvarchar (30), B INT, eventinfo nvarchar )) if @ kill_lock_spid = 1 begin declare @ spid varchar (10), @ sign varchar (10) While @ I <= @ count begin select @ spid = process ID, @ flag = flag from # t where id = @ I insert # T1 exec ('dbcc inputbuffer ('+ @ spid + ')') if @ sign = 'deadlocked process' exec ('Kill '+ @ spid) set @ I = @ I + 1 end else while @ I <= @ count begin select @ s = 'dbcc inputbuffer ('+ Cast (process ID as varchar) + ') 'From # t where id = @ I insert # T1 exec (@ s) set @ I = @ I + 1 end select. *, process SQL statement = B. eventinfo from # t a join # t1 B on. id = B. idendgo

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.