deadlock on tempdb..sysindexes

Greetings,
I have stored procedure dumping resultset into created temporary table.
Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
Problem is I that can not change stored procedure and not sure how to stop
locking sysindexes since stored proc will take between 5-15 seconds to run
depending on date range supplied.
I would appreciate any suggestions how to resolve this issue
PS: Code goes like this
create table #temp(...)
insert into #temp
execute sp_StoredProc
--
SaxonThe problem appears to be that the stored procedure is executing within a
transaction. This is unavoidable, since the INSERT...EXEC statement starts
a transaction prior to executing the procedure. To avoid the deadlocks, you
MUST alter the stored procedure. If you can't alter it, then make a copy
and alter that. Change the procedure so that it executes an INSERT
statement into the temp table. (If a temp table is created before executing
the stored procedure, it is available within the body of the stored
procedure.) This will eliminate the transaction
You should avoid creating, altering or deleting temporary objects within a
transaction. This includes both tables, indexes and constraints. You
should avoid executing procedures within a transaction. For this reason, I
generally avoid INSERT...EXEC.
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
> Greetings,
> I have stored procedure dumping resultset into created temporary table.
> Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
> Problem is I that can not change stored procedure and not sure how to stop
> locking sysindexes since stored proc will take between 5-15 seconds to run
> depending on date range supplied.
> I would appreciate any suggestions how to resolve this issue
> PS: Code goes like this
> create table #temp(...)
> insert into #temp
> execute sp_StoredProc
> --
> Saxon|||Thanks Brian,
so basically if I create temp table and call stored proc to insert into
table instead of using INSERT... EXEC it would not cause deadlock since no
transactions would be started.
PS: Why inserting into temp table would hold lock on sysindexes anyway? I
tried to find some info on that but no luck.
Regards
Saxon
"Brian Selzer" wrote:

> The problem appears to be that the stored procedure is executing within a
> transaction. This is unavoidable, since the INSERT...EXEC statement start
s
> a transaction prior to executing the procedure. To avoid the deadlocks, y
ou
> MUST alter the stored procedure. If you can't alter it, then make a copy
> and alter that. Change the procedure so that it executes an INSERT
> statement into the temp table. (If a temp table is created before executi
ng
> the stored procedure, it is available within the body of the stored
> procedure.) This will eliminate the transaction
> You should avoid creating, altering or deleting temporary objects within a
> transaction. This includes both tables, indexes and constraints. You
> should avoid executing procedures within a transaction. For this reason,
I
> generally avoid INSERT...EXEC.
>
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
>
>|||The lock isn't caused by inserting, it's caused by creating, altering, or
deleting a temporary object within the procedure! The problem is that
normally, when a procedure runs, any transactions must be explicitly started
within the body of the proc. INSERT...EXEC wraps the procedure call in a
transaction. There are several articles on MSDN about lock contention and
blocking--some cite concurrency issues with tempdb. (There are fixes for
that in SP4.)
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
> Thanks Brian,
> so basically if I create temp table and call stored proc to insert into
> table instead of using INSERT... EXEC it would not cause deadlock since no
> transactions would be started.
> PS: Why inserting into temp table would hold lock on sysindexes anyway? I
> tried to find some info on that but no luck.
> Regards
> --
> Saxon
>
> "Brian Selzer" wrote:
>|||Thank you kindly Brian.
Much appreciated.
Regards
Saxon
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||Thanks for this useful description of the problem.
If we creates at temporary table in the procedure and fill data into it with
a function, will that cause a transaction too?
create table #temp(...)
insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||On Wed, 23 Nov 2005 03:36:11 -0800, winther wrote:

>Thanks for this useful description of the problem.
>If we creates at temporary table in the procedure and fill data into it wit
h
>a function, will that cause a transaction too?
>create table #temp(...)
>insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
Hi winther,
Yes. Every modification is automatically part of a transaction. If you
didn't start one explicitly, it will be started implicitly.
If SET IMPLICIT_TRANSACTION is OFF, the implicitly started transaction
will also be implicitly committed after each statement. With this
setting to ON, the server waits for an explicit COMMIT or ROLLBACK to
end the transaction.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There will be a transaction, but it won't put a lock on sysindexes because
the create table occurs apart from the transaction started for the insert.
"winther" <winther@.discussions.microsoft.com> wrote in message
news:0A8FC1D4-1571-45B8-91A8-29656995C285@.microsoft.com...
> Thanks for this useful description of the problem.
> If we creates at temporary table in the procedure and fill data into it
> with
> a function, will that cause a transaction too?
> create table #temp(...)
> insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
>
> "Brian Selzer" wrote:
>