I have a site that calls a stored procedure to populate data on an ASP page.
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>
Showing posts with label threshold. Show all posts
Showing posts with label threshold. Show all posts
Sunday, March 11, 2012
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
>
>
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
>
>
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
>
>
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
>
>
Subscribe to:
Posts (Atom)