process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.

|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue