sql bit data type: spid

Showing posts with label spid. Show all posts

Monday, March 19, 2012

Bocking Issue

I have a spid that has a wait type of networkio, open
trans = 0, command = select, status is runnable, wait time
of around 70000000. application is IIS.
I'm assuming I can just kill this spid beceause it's
a "select" and not part of a transaction. Is there some
way to tell if it's safe to kill this?Did you do a DBCC INPUTBUFFER on that spid? That'll at least tell you what
exactly that spid is selecting, and you could determine, where that
connection is coming from.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"maryann" <anonymous@.discussions.microsoft.com> wrote in message
news:0bcf01c3d53c$b61fb070$a501280a@.phx.gbl...
I have a spid that has a wait type of networkio, open
trans = 0, command = select, status is runnable, wait time
of around 70000000. application is IIS.
I'm assuming I can just kill this spid beceause it's
a "select" and not part of a transaction. Is there some
way to tell if it's safe to kill this?

Sunday, March 11, 2012

Blocking SPID ... no apparent reason

Hi,
I hava a JAVA application that updates a SQL2000 (SP3a)database.
The application handles different types of "jobs" which effectively update the DB.

One job in particular appears to block all subsequent jobs. It comprises of a large amount of inserts/updates in a single transaction. This is necessary as it is an "all or nothing" scenario - so we cannot break the transaction into smaller ones. The transaction appears to succeed as it reaches the COMMIT TRAN statement without error.
However the records do not get written to the database.
EM indicates a large number of locks held on the tables accessed by the transaction and these do not get released.

Using the SP sp_blocker_pss80, the blocking SPID has a waittime of 0 and a waittype of 0x0000 - the lastwaittype is WRITELOG and its status is AWAITING COMMAND

I am using MS SQLSERVER JDBC Driver SP2 (considering using jTDS)

I have tried
- increasing Transaction Log size
- Moving Transaction Log to a separate Disk
- Reducing Isolation Mode to Read Uncommitted
- Set AutoCOMMIT to true
- set Close Cursor on COMMIT
- set SelectMethod to Direct - (we use Cursor by default)

None of these have succeeded in fixing the issue.

The job will succeed if it is the first/only job to access the database.
But if another job precedes it - then the blocking occurs.
I have verified that the preceding job only holds shared dataabase locks
before the blocking job is run.

Each job will use its own JDBC connections to access the database for reading
purposes, but all of the writing goes through the blocking SPID.

Any ideas?
Thanks, LiamIf you close the JDBC connection (when the transaction is complete), does that fix the problem?

-PatP|||I wonder if it is not a deadlock situation. By default deadlocks are not logged. Try running the following:

dbcc traceon (-1, 1205)

then run the big update process. This should start logging deadlock information to the SQL Errorlog.

Except for the data not being written at the end, you have described exactly what locking is designed to do. While someone is writing data to the database, no one else can read that data until they are done. You can potentially try to reduce table locks by checking that the update process is using an appropriate index. Try running the Index Tuning Wizard, and see if it makes any suggestions. This should, of course, be done on a test box first.|||Hi,
Thanks for replies.
1) Connections cannot be terminated after batch jobs complete due to nature of the application

2) DBCC TRACEON resulted in a number of the following entries in the SQL Log

Starting deadlock search 5306
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:57 ECID:0 Ec:(0x484D9510) Value:0x4b0eb320
Node:2 ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x42ECD510) Value:0x4b103b00
End deadlock search 5306 ... a deadlock was not found.

3) Index tuner - havent tackled yet|||I thought 1205 gave you information about deadlocks in progress. I was apparently wrong. Try this:

dbcc traceoff (-1, 1205)
go
dbcc traceon (-1, 1204)

Blocking process

I just found an interesting thing in a database.
I have a lot of locks and all locked processes are locked by SPID -2. The
information I get in Enterprise manager and Query analyzer is that the
proceses are blocked by -2.
I can't find any information of what SPID -2 is. The only thing I see is
when I run sp_lock, then I get a list with over 3000 rows of locks which are
held by -2.
Does anybody know what a negatvie SPID is and what I can do to resolve this?If you run a sp_who '-2'
What does this bring back?
Dylan kruger
"Jaana" <jaana.lehtonen@.banverket.se> wrote in message
news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> I just found an interesting thing in a database.
> I have a lot of locks and all locked processes are locked by SPID -2. The
> information I get in Enterprise manager and Query analyzer is that the
> proceses are blocked by -2.
> I can't find any information of what SPID -2 is. The only thing I see is
> when I run sp_lock, then I get a list with over 3000 rows of locks which
are
> held by -2.
> Does anybody know what a negatvie SPID is and what I can do to resolve
this?
>|||sp_who -2 gives the result The login '-2' does not exist.
"Dylan Kruger[MS]" <carmellobear1@.hotmail.com> skrev i meddelandet
news:OhcrsG8kDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If you run a sp_who '-2'
> What does this bring back?
> Dylan kruger
> "Jaana" <jaana.lehtonen@.banverket.se> wrote in message
> news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > I just found an interesting thing in a database.
> > I have a lot of locks and all locked processes are locked by SPID -2.
The
> > information I get in Enterprise manager and Query analyzer is that the
> > proceses are blocked by -2.
> > I can't find any information of what SPID -2 is. The only thing I see is
> > when I run sp_lock, then I get a list with over 3000 rows of locks which
> are
> > held by -2.
> > Does anybody know what a negatvie SPID is and what I can do to resolve
> this?
> >
> >
>

Blocking problem

SQL2K, sp3
Active Server Pages front-end

I am having a blocking problem with a spid executing a stored
procedure that does no updates. When I look at the locks the blocking
spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
is shared, but I don't see "IS". Can anyone tell me what lock mode
"IS" is?

And how could a stored procedure that does no updates be guilty of
blocking?
There are no cursors in it.

Thanks![posted and mailed, please reply in news]

Hal (hforman1@.cfl.rr.com) writes:
> I am having a blocking problem with a spid executing a stored
> procedure that does no updates. When I look at the locks the blocking
> spid has, some of them have lock mode "IS". Looking in BOL, I see "S"
> is shared, but I don't see "IS". Can anyone tell me what lock mode
> "IS" is?

Intent Shared. If memory serves, if a process has a lock on row level,
it will also take out an intent lock on table level, at least under
some circumstances.

> And how could a stored procedure that does no updates be guilty of
> blocking?

It could block a writer that wishes to update one of the rows that the
reader locks.

To get a better hold of the locking situation, it's probably better to
lock at the blocking processes, and see what lock it is blocked on. I
have a routine on my web site that is conventient for this kind of thing.
You get all active processes (and if a process holds a lock it's active
by definition), their locks and their current statements. Spot the blocked
processes and lock for locks with status WAIY. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

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

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

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--070605070403080308080508
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:
>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
--070605070403080308080508
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A SELECT statement will hold a shared (S) lock on the specific
resource (generally a KEY or RID) and an intent shared (IS) lock on the
escalated resources (usually an IS(PAG) and IS(TAB)).Â The data
browsing window in SQLEM will hold locks open for big tables while
you're scrolling around through the data (just like MSAccess).Â If
SQLEM happens to have a shared lock on a page (PAG), which is entirely
possibly, and some other process requests an intent exclusive (IX) lock
on that same page (for example it wants to delete a row on the page, so
it will acquire an X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then
the IX(PAG) will be blocked by the S(PAG), because they're incompatible
lock types on the same resource, until the S)PAG) is release. 
 
See BOL for more into on lock compatibility:
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>) ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>) 
 
The important thing to remember is that SQLEM is just another client
app running SELECT queries and issuing shared lock requests, sometimes
on pages of data at a time, and often doesn't release those locks (if
it's a bit enough table and you haven't reached the last page of it
yet) until you close the child window displaying that data (that issued
the SELECT statement). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Mangesh Deshpande wrote:
<blockquote cite="mid5515F106-3E87-4255-9DD6-7CAA798E2526@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
</body>
</html>
--070605070403080308080508--|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have one user who uses SQL EM and run queries and modifies data.
> > We see that when the main blocking SPID is SQLEM and command is
> > "select"
> >
> > My question is
> > 1. How can select command from EM be a blocking spid.
> > 2. How can you cause blocling using EM?
> >
> > Please explain.
> >
> > Mangesh
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||This is a multi-part message in MIME format.
--060809080506050602090205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>>Mangesh Deshpande wrote:
>>
>>Hi
>>I have one user who uses SQL EM and run queries and modifies data.
>>We see that when the main blocking SPID is SQLEM and command is
>>"select"
>>My question is
>>1. How can select command from EM be a blocking spid.
>>2. How can you cause blocling using EM?
>>Please explain.
>>Mangesh
>>
>>I can't stress this enough when I say that you should never use SQL EM
>>for editing data, unless in development or off-hours.
>>SQL EM uses a server-side, firehose cursor which is good, but does not
>>fetch all data immediately (which is bad), like Query Analyzer or any
>>well-written application might. The benefit is that a large table can be
>>queried and viewed quickly, but most of the rows are sitting on the
>>server in the result set waiting for the user to scroll though the
>>results (at which point they are fetched). Until then, shared locks
>>remain on some pages on the table.
>>To avoid this, immediately issue a CTRL+END to move the end of the
>>results. That forces SQL EM to fetch all data, thereby releasing the
>>locks. If it's a large table, however, this process could be time
>>consuming.
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--060809080506050602090205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A firehose cursor is essentially a forward-only, read-only cursor.
From BOL: 
</tt>
<blockquote><a>Rows are sent to the client in the order they are placed
in the result set, and the application must process the rows in this
order. After executing an SQL statement on a connection, the
application cannot do anything on the connection other than retrieve
the rows in the result set until all the rows have been retrieved. The
only other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client. 
</a></blockquote>
<tt>See
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Hassan wrote:
<blockquote cite="midujrJPozZFHA.2420@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">What is a firehose cursor ?
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl">news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Mangesh Deshpande wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is
"select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
<pre wrap="">I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060809080506050602090205--

Thursday, March 8, 2012

Blocking in Distribution Database

I noticed that the distribution agents are blocking themselves in the
distribution database.
It looks like this when i do a sp_who2 spid/blocked By:
80/Not blocked
80/80
80/80
80/80
80/80
Is this normal?
Probably not. Check to see what the process is doing which is doing the
blocking and being blocked. You may need to use DBCC inputbuffer(spid) to do
this. You will get locking when some of the replication agents are running,
and even EM will cause locking. It is also possible that if your server is
really under substaintial load the log reader and distribution agents will
lock themselves.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeff B" <JeffB@.discussions.microsoft.com> wrote in message
news:B25E48D1-6AC8-4D59-892C-AA7E92D7A95A@.microsoft.com...
>I noticed that the distribution agents are blocking themselves in the
> distribution database.
> It looks like this when i do a sp_who2 spid/blocked By:
> 80/Not blocked
> 80/80
> 80/80
> 80/80
> 80/80
> Is this normal?

blocking

Frequently when I run sp_who2, I see a spid blocked by itself. Why/How does
this happen? Is there anything I can do about it?
Thanks, Andre
Hi
New feature in SP4.
http://groups.google.com/group/micro...513ab281?hl=en
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:eODlWin6FHA.2608@.tk2msftngp13.phx.gbl...
> Frequently when I run sp_who2, I see a spid blocked by itself. Why/How
> does
> this happen? Is there anything I can do about it?
> Thanks, Andre
>
|||Interesting - thank you.
Andre

Wednesday, March 7, 2012

blocking

Frequently when I run sp_who2, I see a spid blocked by itself. Why/How does
this happen? Is there anything I can do about it?
Thanks, AndreHi
New feature in SP4.
http://groups.google.com/group/micr...r />
281?hl=en
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:eODlWin6FHA.2608@.tk2msftngp13.phx.gbl...
> Frequently when I run sp_who2, I see a spid blocked by itself. Why/How
> does
> this happen? Is there anything I can do about it?
> Thanks, Andre
>|||Interesting - thank you.
Andre

blocking

Frequently when I run sp_who2, I see a spid blocked by itself. Why/How does
this happen? Is there anything I can do about it?
Thanks, AndreHi
New feature in SP4.
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:eODlWin6FHA.2608@.tk2msftngp13.phx.gbl...
> Frequently when I run sp_who2, I see a spid blocked by itself. Why/How
> does
> this happen? Is there anything I can do about it?
> Thanks, Andre
>|||Interesting - thank you.
Andre

Blocked by SPID -2

Hi,
I am using SQL Server 2000 with SP3.
Why am I being blocked by spid -2? I am not able to kill
it and have to restart SQL Server to get around it.
Warm Regards,
DhanunjayIt's a system process - it depends on what you are doing and
what the process is and what it's doing at the time. Don't
think anyone can tell you why without knowing more
information.
-Sue
On Tue, 16 Sep 2003 00:03:45 -0700, "Dhanunjay"
<vdhanunjay@.hotmail.com> wrote:
>Hi,
>I am using SQL Server 2000 with SP3.
>Why am I being blocked by spid -2? I am not able to kill
>it and have to restart SQL Server to get around it.
>Warm Regards,
>Dhanunjay

Friday, February 24, 2012

Blkby Spid -2

I have an issue with and testing enviornment between weblogics application
and SQL server where we are trying to the transaction threshold above 5000
transaction per hour. We have a test that was ran yesterday for 12 hours
trying 8000 transactions per hour threw weblogics against sql server. When
we came in this morning the connections to sql server showed that all
connection from this server we were doing test on were blocked by the same
spid, when getting down the the specific spid, that spid was blocked by spid
-2. I had a simular issue about 1 year ago but cannot seem to remember what
causes the block spid -2 issue. My system is Sql 2000 running sp4. If you
need to post the specific sp_who2 dump I can but figured this information
would be sufficiant.
ThanksHi
In SQL Server 2000 and later, all orphaned distributed transactions are
assigned the session ID value of '-2'. Orphaned distributed transactions are
distributed transactions that are not associated with any session ID.
Stop and restart MSDTC.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:0083D010-4C3D-4F16-886A-8DED076B43B1@.microsoft.com...
>I have an issue with and testing enviornment between weblogics application
> and SQL server where we are trying to the transaction threshold above 5000
> transaction per hour. We have a test that was ran yesterday for 12 hours
> trying 8000 transactions per hour threw weblogics against sql server.
> When
> we came in this morning the connections to sql server showed that all
> connection from this server we were doing test on were blocked by the same
> spid, when getting down the the specific spid, that spid was blocked by
> spid
> -2. I had a simular issue about 1 year ago but cannot seem to remember
> what
> causes the block spid -2 issue. My system is Sql 2000 running sp4. If
> you
> need to post the specific sp_who2 dump I can but figured this information
> would be sufficiant.
> Thanks
>
>

Monday, March 19, 2012

Bocking Issue

Sunday, March 11, 2012

Blocking SPID ... no apparent reason

Blocking process

Blocking problem

Blocking issue in sql2000

Blocking issue in sql2000

Blocking issue in sql2000

Thursday, March 8, 2012

Blocking in Distribution Database

blocking

Wednesday, March 7, 2012

blocking

blocking

Blocked by SPID -2

Friday, February 24, 2012

Blkby Spid -2

Blkby Spid -2

sql bit data type

Blog Archive

About Me