Excessive blocking - runnable processes

Hi there,

Currently using SQL Server 2000 (SP4). The following condition started occurring last week:

- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking

Any help would be greatly appreciated.

Thanks.Did the blocking happen with the user tables or in tempdb?|||It happens on the user tables... Thanks.|||This is probably a fault of the database application, not the server. Poorly designed schemas and bad code are the primary culprits of blocking, which may not occur until the application use scales beyond that which it was tested.

Try to figure out which sprocs or statements are causing the blocking, and then try to optimize them.|||Since your job actually finishes, and it did not happened at night. My guess would be:
1. Some process running long update transaction, or
2.|||2. Your IO time slows down due to workload, that may increases chance of blocking too.|||If you cannot spot abnormalties in profiler, have a look at IO time.|||Thanks for all your replies. It was pointing to an application issue. Thanks again.

Exception repeately occurring

Hi

I have a strange problem with my SQL server 2k instllation - every 10
mintutes when I have the Profiler trace with the entire "Errors" Event
category selected, the following 5 exceptions show up in the trace and
that too for the same SPID.

Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1

I have no clue why this is occurring - I tried running a trace with the
SP:StmtCompleted event on, but no other stored procedures show up with
the same spid close to the time where this exception is logged.

Does anyone have a clue as to why this error is occurring ?

RahulPondy (fd96121@.yahoo.com) writes:
> I have a strange problem with my SQL server 2k instllation - every 10
> mintutes when I have the Profiler trace with the entire "Errors" Event
> category selected, the following 5 exceptions show up in the trace and
> that too for the same SPID.
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> I have no clue why this is occurring - I tried running a trace with the
> SP:StmtCompleted event on, but no other stored procedures show up with
> the same spid close to the time where this exception is logged.

So what you in a such situation like this is this:

select * from master..sysmessages where error in (16955, 16945)

You could also have looked up the errors in Books Online, by simply
searching for them. This could give you the bonus that there might be
entire topic to troubleshoot the problem. I would not expect that in
this case, though.

These are the messages:

16945 The cursor was not declared.
16955 Could not create an acceptable cursor.

I would guess that 16945 is a consequence of 16955.

Apparently there is some code out there where the cursor declaration
fails, and where there is no error handling, so that execution continues.
Note that this may not have to be a stored procedure. Hypothetically
it could be a server-side cursor initiated by some client API as well.

In any case, it's a problem specific to that process, and it is not that
your server is about to go belly-up.

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

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