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)

BlackMail by software manufactureron sql 6.5

we are using a software that runs on sql 6.5 and the manufacturer is causing all types of problems

I tried to trace what he is doing on one few steps

i could not, how has he disabled the trace, can i enable it

how and will it have some repurcussion

i want to throw this vendor out

Plain BlackmailHow is the vendor connecting to your environment? VPN? Disable the VPN account. Also, analyze your logins and see which ones are used by your app. If possible, change the password on the application account (I hope that account is not SA), and by all means change the SA password. But before you do all that make sure you're not breaching your Software License Agreement by monkeying with all this stuff.|||Refer to your other http://www.dbforums.com/t990068.html post about enabling SQL Trace.

Ensure to tighten the security on SQL by using secure password for SA account.|||I mean that when i set up the trace,each operation that we do on the vendors software

I need to track where it effecting on sql tables

Can a vendor disable that tracking , or i have it set wrong or something

I dont have a vpn connection for the vendor open

so he can't enter without our approval

For trace is not user friendly on 6.5|||Ensure you're using correct password to connect SQL database.
There is no setting where you can bypass the Trace on SQL Server.

Bits of Indexes RFC

I suppose this is more an RFC than a true question, but I've come to realize "bit" data types do not optimize a query when they are used in a WHERE clause. Since they cannot be indexed, it forces the analyzer to query an available index for rows based upon the non-bit constraints. The result is then scanned to match the values of the bit constraints of the query. So you're potentially doing a full table scan, or an index scan.

My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.

Am I wrong in my conclusion?Nope...

USE Northwind
GO

CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO

--[CTRL]+k

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

DROP TABLE myTable99
GO|||WOW! Learn something new every day! Now I've got to go and start changing all my bit fields to something else. The execution plan in QA showed the bit field about three times slower for a simple select!

Thanks Brett... Keep the info coming..|||...It also produced table scan. I noticed though that it does not behave the same way if bit field is part of a composit index, even if it is the first field in the index field list.|||I get Index Scans...I have no idea why it would decide to use the new indexes and do the scan...could be because there's no data..

Hell the last 2 indexes are the same (if not larger) in...

But why would a 3 selects use each new index as I add them?

USE Northwind
GO

CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

CREATE INDEX myTable99_IX4 ON myTable99(Col2,Col1,Col3)
GO

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

CREATE INDEX myTable99_IX5 ON myTable99(Col1,Col3,Col2)
GO

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

DROP TABLE myTable99
GO|||Right.. I suppose then that bits should be relegated to read/write only.. not query constraints...

So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)|||So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

It's probably due to the cardinality...bit is 0 or 1...anything with a low number of different values...will not make a good index...it will always cause a scan..

In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)

In a table..no...in an index, it's imperative...at least that's what I've always believed...

For example...an index with Col3, Col2, Col1

And a predeicate of Col1 = something

Is a table scan...ever read up on index intersection?

Still don't know why my example (besides probably be a bad one) shows the index scan using IX4 and IX5...got a very nice seek using IX1 thru IX3

Wonder why the optimizer chose them...

I'm (as usual, so it's not disconcerting) perplexed...