Lock on system table

Hi all,
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>