Blocking Problem

Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked serve
r
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:

> Hi there,
> maby someone know something about this kind of strange behaviour. One of m
y
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the sa
me
> SPID. The locks are on key-level. I've tested it and the problem seems to
be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fin
e
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...[vbcol=seagreen]
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f 234 spam-mails.
Betalende brugere fr ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after prope
r
evaluation and testing!)
John
"Bobby Henningsen" wrote:

> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Serve
r
> 2000 (And still are. They had to move the database back). I can'tfigure ou
t
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thin
g
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f? 234 spam-mails.
> Betalende brugere f?r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>