Lock request time out period exceeded - Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Lock request time out period exceeded - Help

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2009-03-12 : 04:37:35

Hi I'm having massive problems with a sp i created. I made a full-text text index on two tables and created the below sp.

I had tested bot the full-text index and sp on a tast server and everything ran fine. Now when I try to alrer, delete or run the sp I get a lock request time out error.

Can some please help me!?

ALTER procedure [dbo].[sp_leap29_jobdetails_QuickSearch2]

(@Keyword nvarchar(500),
@Location nvarchar(500))

as

select distinct j.jobid,
j.jobtitle,
convert(varchar(11),j.createdon, 106) as date,
aa.description as 'location',
left(cast(j.publishedjobdescription as Nvarchar(500)), 300)+ ' ....' as 'Description',
j.createdon,
cast(round((j.maxbasic),2,1) as decimal(10))as 'salary'

From jobattributes a
join Jobs j on j.jobid = a.jobid
join attributes aa on aa.attributeid = a.attributeid

where CONTAINS(aa.description,@location) and contains((j.jobtitle,j.publishedjobdescription),@keyword)
and aa.attributemasterid in ('419')
and j.updatedon > dateadd(month,-6,getdate())
and DATALENGTH(publishedjobdescription) > 100

Error -

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for StoredProcedure 'dbo.sp_xxxxxx'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+StoredProcedure&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 01:17:05

Checks to see if you have any hanging connections that are using that objects in the sp ...

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

DaveC11
Starting Member

43 Posts

Posted - 2009-03-13 : 05:11:39

How do I check that??

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 15:29:57

You can check activity monitor to see if you have long lasting connections that have are stuck in a transaction that never completed.

You can also turn trace on your server as you execute that script and see which locks are timing out.

Ref: http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources