Posted by Erland Sommarskog on 06/21/05 11:31

(arijitchatterjee123@yahoo.co.in) writes:
> Continuation of the Problem...But I am trying to highlit is different
> manner..Using Sqlserver 2000 with Service pack 3
> After login When I am going to Current Activities under Management and
> doing refresh it is fine.
> But from my Database end under Query Analyzer I am typing these lines
>
> begin tran t1
> create table #t1 (a int)
> insert into #t1 values(1)
> ---------But not commiting still now..
> And again I am going to Current Activities under Management and doing
> refresh but now it is telling
> Error 1222: Lock request time out period exceeded.

Don't use Current Activity. It tries to look up the name of the temp
table, but uses READ COMMITTED isolation level to do this, so it gets
blocked. If you use my aba_lockinfo you will not get blocked, because
I read the system tables with READ UNCOMMITTED. (Dirty reads, which in
this special case is the right level.)

> Now plese guide me how to solve this..without commiting the
> transaction..I can say this transaction process can take 1 hour also..
> That is why it has not commited still now.. That means on that time no
> one can interact with the tempDB?

That's a foregone conclusion. Special operations like Current Activity
will block. Normal usage will not.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]