Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
--
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
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> > I have an application with SQL Server 2000 SP4 as my RDBMS.
> > There is a table with around 1200000 records with all the necessary
> > indexes defined when i execute the query through query analyzer the no
> > of rows returned are around 300000, execution plan shows a Bookmark
> > Lookup. But the bookmark lookup taks lot of time?
> > Also if i remove the particular index it shows me a Table Scan in
> > Execution Plan.
> > Can anyone help me explain why Bookmark lookup takes the same time as
> > Table Scan?
> > Thanks & Regards
> > Vishal.
> >
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.
I am not sure if I can but perhaps a covering index would be helpful here.
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
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegr oups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Can u tell me more about covering Indexes?
As in :
What do they mean?
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/art...&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
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
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table an
d
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/ar...5&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

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?

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 access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hours running a application developt with vb.net 2003, after a time this blocking is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a part of a factory and is also used for controlling machines. If there is a blocking then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden -> integer
'Aantal records is resultaat(0).aantalrecords -> integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) -> string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalve lden) -> object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boolean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar, begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150 select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzerman
Martin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalve lden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>
|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidable.', i hope it is not true.
In my application blocking is not acceptable. before i have written a application with MySQL as database-server and after 2.5 years 24 hours 7 days a week, there are never blocking problems. The policy by the factory i work for is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman
|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with MySQL as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>
|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and in the logging file there is no blocking detected, and also in the profiler trace there are no special things to see. There is after 10 to 14 hours some blocking with is not detec
ted.
Martin IJzerman.

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hours running a application developt with vb.net 2003, after a time this blocking is gone and there is a normal acces to this database
The application is used for logging parameters, counters and other for a part of a factory and is also used for controlling machines. If there is a blocking then there is no possibility control this machines adequate
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;
The vb module i use
Imports Syste
Imports System.Dat
Imports System.Data.SqlClien
Module SQ
Public SQL_CONNECTION_STRING As Strin
'Structuur van de resultaat arra
'Aantal velden is resultaat(0).aantalvelden -> intege
'Aantal records is resultaat(0).aantalrecords -> intege
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) -> strin
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> objec
Structure Resultaat
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordse
Dim VeldNamen() As String ' namen van de velde
Dim VeldInhoud() As Object ' inhoud van de velde
End Structur
' Resultaat array is de inhoud van de query uitvoerin
Public Resultaat() As Resultaat
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boolean = False) As Boolea
If upd_Q = True The
GeenResultaat(strSQL
End I
If upd_Q = False The
WelResultaat(strSQL
End I
End Functio
Private Sub GeenResultaat(ByVal queryStr As String
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING
myConnection1.Open(
Dim myCommand As SqlCommand = myConnection1.CreateCommand(
Dim myTrans As SqlTransactio
' Start a local transactio
myTrans = myConnection1.BeginTransaction(
' Must assign both transaction object and connectio
' to Command object for a pending local transactio
myCommand.Connection = myConnection
myCommand.Transaction = myTran
myCommand.CommandTimeout = Tr
myCommand.CommandText = querySt
myCommand.ExecuteNonQuery(
myTrans.Commit(
Catch e As Exceptio
Tr
myTrans.Rollback(
Catch ex As SqlExceptio
If Not myTrans.Connection Is Nothing The
Debug.WriteLine("An exception of type " & ex.GetType().ToString() &
" was encountered while attempting to roll back the transaction."
End I
End Tr
Debug.WriteLine("An exception of type " & e.GetType().ToString() &
"was encountered while inserting the data. " & Now()
Debug.WriteLine("Neither record was written to database."
Finall
y = y +
myCommand.Dispose(
myConnection1.Close(
myConnection1 = Nothin
End Tr
End Su
Private Sub WelResultaat(ByVal queryStr As String
Dim lRow As Integer = 0 ' recordnumme
Dim lFIELD As Integer = 0 ' kolomnumme
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velde
Dim myDataR As SqlDataReade
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING
myConnection2.Open(
Dim myCommand As SqlCommand = myConnection2.CreateCommand(
myCommand.Connection = myConnection
Tr
myCommand.CommandText = querySt
myCommand.CommandTimeout = myDataR = myCommand.ExecuteReader(
'haal het aantal velden op van de recordse
LFieldsAffected = myDataR.FieldCoun
'redimensioneer de dynamische arra
ReDim Resultaat(1
ReDim Resultaat(0).VeldNamen(LFieldsAffected
ReDim Resultaat(0).VeldInhoud(LFieldsAffected
'zet het aantal velden in resultaat(0).aantalvelde
Resultaat(0).AantalVelden = LFieldsAffecte
'Vul de veldnamen i
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar, begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150 select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Ur
What do you mean with, 'a certain amount of blocking is normal and unavoidable.', i hope it is not true
In my application blocking is not acceptable. before i have written a application with MySQL as database-server and after 2.5 years 24 hours 7 days a week, there are never blocking problems. The policy by the factory i work for is using MSSQLserver 2000, and there is no possibility to change to a other db-server
I shall try to cancel the query in state of rolling them back
Thanks, Martin IJzerma|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with MySQL as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and in the logging file there is no blocking detected, and also in the profiler trace there are no special things to see. There is after 10 to 14 hours some blocking with is not detected
Martin IJzerman

Blocking access to a database after hours running a application

I have a problem: Access to a database on SQL2000 is blocking after many hou
rs running a application developt with vb.net 2003, after a time this blocki
ng is gone and there is a normal acces to this database.
The application is used for logging parameters, counters and other for a par
t of a factory and is also used for controlling machines. If there is a bloc
king then there is no possibility control this machines adequate.
The connection-string: SQL_CONNECTION_STRING = "Initial Catalog=PTSB
A02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
The vb module i use:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module SQL
Public SQL_CONNECTION_STRING As String
'Structuur van de resultaat array
'Aantal velden is resultaat(0).aantalvelden ->
integer
'Aantal records is resultaat(0).aantalrecords ->
integer
'De veldnamen zijn resultaat(0).veldnamen(0..aantalvelden) ->
string
'De veldinhoud is resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) ->
object
Structure Resultaat_
Dim AantalVelden As Integer ' aantal velden in de recordset
Dim AantalRecords As Integer ' aantal records in de recordset
Dim VeldNamen() As String ' namen van de velden
Dim VeldInhoud() As Object ' inhoud van de velden
End Structure
' Resultaat array is de inhoud van de query uitvoering
Public Resultaat() As Resultaat_
Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As Boo
lean = False) As Boolean
If upd_Q = True Then
GeenResultaat(strSQL)
End If
If upd_Q = False Then
WelResultaat(strSQL)
End If
End Function
Private Sub GeenResultaat(ByVal queryStr As String)
Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection1.Open()
Dim myCommand As SqlCommand = myConnection1.CreateCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection1.BeginTransaction()
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection1
myCommand.Transaction = myTrans
myCommand.CommandTimeout = 1
Try
myCommand.CommandText = queryStr
myCommand.ExecuteNonQuery()
myTrans.Commit()
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Debug.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data. " & Now())
Debug.WriteLine("Neither record was written to database.")
Finally
y = y + 1
myCommand.Dispose()
myConnection1.Close()
myConnection1 = Nothing
End Try
End Sub
Private Sub WelResultaat(ByVal queryStr As String)
Dim lRow As Integer = 0 ' recordnummer
Dim lFIELD As Integer = 0 ' kolomnummer
Dim lRecordsAffected As Integer = 0 ' aantal records
Dim LFieldsAffected As Integer = 0 ' aantal velden
Dim myDataR As SqlDataReader
Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
myConnection2.Open()
Dim myCommand As SqlCommand = myConnection2.CreateCommand()
myCommand.Connection = myConnection2
Try
myCommand.CommandText = queryStr
myCommand.CommandTimeout = 2
myDataR = myCommand.ExecuteReader()
'haal het aantal velden op van de recordset
LFieldsAffected = myDataR.FieldCount
'redimensioneer de dynamische array
ReDim Resultaat(1)
ReDim Resultaat(0).VeldNamen(LFieldsAffected)
ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
'zet het aantal velden in resultaat(0).aantalvelden
Resultaat(0).AantalVelden = LFieldsAffected
'Vul de veldnamen in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
Next
'Vul de inhoud van de query uitkomst in
If myDataR.HasRows() Then
While myDataR.Read
lRow = lRow + 1
ReDim Preserve Resultaat(lRow + 1)
ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
'Vul de velden in
For lFIELD = 0 To LFieldsAffected - 1
Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
Next
End While
End If
'Vul het aantal records in
Resultaat(0).AantalRecords = lRow
Catch e As Exception
Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while reading the data. " & Now())
Debug.WriteLine("Neither record was read from the database.")
Finally
z = z + 1
myCommand.Dispose()
myDataR.Close()
myConnection2.Close()
myConnection2 = Nothing
End Try
End Sub
End Module
a insert query i use:
SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing, commentaar,
begin_, einde_, monteur)" & _
" VALUES (" & MM02_Inv_Stor & ", 'MM02', 'JAM op baan', 'einde storing', '--
--', '" & Format(Now(), "dd-MM-yyyy HH:mm:ss") & "', '--')", True)
a update query i use:
SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" & Mm00_Error_Bi
ts & " where NomItem='" & "lezen02" , True)
and finally a select query:
SQL.ExeQuery("SELECT * FROM mm_telling", False)
every 5 seconds i write abouth 120 update and insert queries and abouth 150
select queries.
the most tables in this database are a few rows (abouth 10 to 20)
The server is MSSQL 2000 with SP 3a
I hope there is a solution for this problem
Martin IJzermanMartin
A certain amount of blocking is normal and unavoidable.
Do you have long-running queries?
Do you cancel the query but not rolling them back?
"MartinY" <m_ijzerman@.hotmail.com> wrote in message
news:F4A1B423-A225-42C3-A02D-858791A503AD@.microsoft.com...
> I have a problem: Access to a database on SQL2000 is blocking after many
hours running a application developt with vb.net 2003, after a time this
blocking is gone and there is a normal acces to this database.
> The application is used for logging parameters, counters and other for a
part of a factory and is also used for controlling machines. If there is a
blocking then there is no possibility control this machines adequate.
> The connection-string: SQL_CONNECTION_STRING = "Initial
Catalog=PTSBA02;Data Source=NLENSAPP01\PDE;Integrated Security=SSPI;"
> The vb module i use:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Module SQL
> Public SQL_CONNECTION_STRING As String
> 'Structuur van de resultaat array
> 'Aantal velden is
-> integer
> 'Aantal records is
-> integer
> 'De veldnamen zijn
ldnamen(0..aantalvelden) -> string
> 'De veldinhoud is
resultaat(1..aantalrecords).veldinhoud(0..aantalvelden) -> object
> Structure Resultaat_
> Dim AantalVelden As Integer ' aantal velden in de recordset
> Dim AantalRecords As Integer ' aantal records in de recordset
> Dim VeldNamen() As String ' namen van de velden
> Dim VeldInhoud() As Object ' inhoud van de velden
> End Structure
> ' Resultaat array is de inhoud van de query uitvoering
> Public Resultaat() As Resultaat_
>
> Public Function ExeQuery(ByVal strSQL As String, Optional ByVal upd_Q As
Boolean = False) As Boolean
> If upd_Q = True Then
> GeenResultaat(strSQL)
> End If
> If upd_Q = False Then
> WelResultaat(strSQL)
> End If
> End Function
> Private Sub GeenResultaat(ByVal queryStr As String)
> Dim myConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection1.Open()
> Dim myCommand As SqlCommand = myConnection1.CreateCommand()
> Dim myTrans As SqlTransaction
> ' Start a local transaction
> myTrans = myConnection1.BeginTransaction()
> ' Must assign both transaction object and connection
> ' to Command object for a pending local transaction
> myCommand.Connection = myConnection1
> myCommand.Transaction = myTrans
> myCommand.CommandTimeout = 1
> Try
> myCommand.CommandText = queryStr
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> Catch e As Exception
> Try
> myTrans.Rollback()
> Catch ex As SqlException
> If Not myTrans.Connection Is Nothing Then
> Debug.WriteLine("An exception of type " &
ex.GetType().ToString() & _
> " was encountered while attempting to roll
back the transaction.")
> End If
> End Try
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while inserting the data. " &
Now())
> Debug.WriteLine("Neither record was written to database.")
> Finally
> y = y + 1
> myCommand.Dispose()
> myConnection1.Close()
> myConnection1 = Nothing
> End Try
> End Sub
> Private Sub WelResultaat(ByVal queryStr As String)
> Dim lRow As Integer = 0 ' recordnummer
> Dim lFIELD As Integer = 0 ' kolomnummer
> Dim lRecordsAffected As Integer = 0 ' aantal records
> Dim LFieldsAffected As Integer = 0 ' aantal velden
> Dim myDataR As SqlDataReader
> Dim myConnection2 As New SqlConnection(SQL_CONNECTION_STRING)
> myConnection2.Open()
> Dim myCommand As SqlCommand = myConnection2.CreateCommand()
> myCommand.Connection = myConnection2
> Try
> myCommand.CommandText = queryStr
> myCommand.CommandTimeout = 2
> myDataR = myCommand.ExecuteReader()
> 'haal het aantal velden op van de recordset
> LFieldsAffected = myDataR.FieldCount
> 'redimensioneer de dynamische array
> ReDim Resultaat(1)
> ReDim Resultaat(0).VeldNamen(LFieldsAffected)
> ReDim Resultaat(0).VeldInhoud(LFieldsAffected)
> 'zet het aantal velden in resultaat(0).aantalvelden
> Resultaat(0).AantalVelden = LFieldsAffected
> 'Vul de veldnamen in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(0).VeldNamen(lFIELD) = myDataR.GetName(lFIELD)
> Next
> 'Vul de inhoud van de query uitkomst in
> If myDataR.HasRows() Then
> While myDataR.Read
> lRow = lRow + 1
> ReDim Preserve Resultaat(lRow + 1)
> ReDim Preserve Resultaat(lRow).VeldInhoud(LFieldsAffected + 1)
> 'Vul de velden in
> For lFIELD = 0 To LFieldsAffected - 1
> Resultaat(lRow).VeldInhoud(lFIELD) = myDataR.GetValue(lFIELD)
> Next
> End While
> End If
> 'Vul het aantal records in
> Resultaat(0).AantalRecords = lRow
> Catch e As Exception
> Debug.WriteLine("An exception of type " & e.GetType().ToString() & _
> "was encountered while reading the data. " & Now())
> Debug.WriteLine("Neither record was read from the database.")
> Finally
> z = z + 1
> myCommand.Dispose()
> myDataR.Close()
> myConnection2.Close()
> myConnection2 = Nothing
> End Try
> End Sub
> End Module
> a insert query i use:
> SQL.ExeQuery("INSERT INTO mm_storing (index_nr, nummer, storing,
commentaar, begin_, einde_, monteur)" & _
> " VALUES (" & MM02_Inv_Stor & ",
'MM02', 'JAM op baan', 'einde storing', '--', '" & Format(Now(),
"dd-MM-yyyy HH:mm:ss") & "', '--')", True)
> a update query i use:
> SQL.ExeQuery("UPDATE MM00 SET waarde" & BlokKeuze_MM00 & "=" &
Mm00_Error_Bits & " where NomItem='" & "lezen02" , True)
> and finally a select query:
> SQL.ExeQuery("SELECT * FROM mm_telling", False)
> every 5 seconds i write abouth 120 update and insert queries and abouth
150 select queries.
> the most tables in this database are a few rows (abouth 10 to 20)
> The server is MSSQL 2000 with SP 3a
> I hope there is a solution for this problem
> Martin IJzerman
>
>|||Uri
What do you mean with, 'a certain amount of blocking is normal and unavoidab
le.', i hope it is not true.
In my application blocking is not acceptable. before i have written a applic
ation with mysql as database-server and after 2.5 years 24 hours 7 days a we
ek, there are never blocking problems. The policy by the factory i work for
is using MSSQLserver 2000,
and there is no possibility to change to a other db-server.
I shall try to cancel the query in state of rolling them back.
Thanks, Martin IJzerman|||Martin
Unfortunatly it is true.
Blocking occurs when one connection to SQL Server locks one or more records,
and a second connection to SQL Server requires a conflicting lock type on
the record or records locked by the first connection. This causes the second
connection to wait until the first connection releases its locks. By
default, a connection will wait an unlimited amount of time for the blocking
lock to go away.
To help identify blocking in your databases, Microsoft has two separate
stored procedures listed on their website (one each for SQL Server 7.0 and
2000) you can use to help identify blocking problems on your SQL Servers. On
these pages are scripts you can use to create stored procedures that you can
run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
"MartinY" <anonymous@.discussions.microsoft.com> wrote in message
news:D24D7BA8-48DC-4FFC-828F-7231E753EC08@.microsoft.com...
> Uri
> What do you mean with, 'a certain amount of blocking is normal and
unavoidable.', i hope it is not true.
> In my application blocking is not acceptable. before i have written a
application with mysql as database-server and after 2.5 years 24 hours 7
days a week, there are never blocking problems. The policy by the factory i
work for is using MSSQLserver 2000, and there is no possibility to change to
a other db-server.
> I shall try to cancel the query in state of rolling them back.
> Thanks, Martin IJzerman
>|||Ury,
I have tried 'How to Monitor SQL Server 2000 Blocking (Q271509)' script and
in the logging file there is no blocking detected, and also in the profiler
trace there are no special things to see. There is after 10 to 14 hours some
blocking with is not detec
ted.
Martin IJzerman.

Blocked tables

From time to time I get blocked tables in my database and application stope
working.
So I try next example:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
While this selects are working I try
in other query analyzer window to create an update on table2:
UPDATE table2 set column1='test'
and I get blocked tables.
I guess something similar is happening in my application.
How can I prevent this blocking?
regards,SIf you are happy with dirty reads you can do this
SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
http://sqlservercode.blogspot.com/
"simon" wrote:

> From time to time I get blocked tables in my database and application sto
pe
> working.
> So I try next example:
> declare @.n int
> set @.n=50
> while @.n>0
> begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
> end
> While this selects are working I try
> in other query analyzer window to create an update on table2:
> UPDATE table2 set column1='test'
> and I get blocked tables.
> I guess something similar is happening in my application.
> How can I prevent this blocking?
> regards,S
>
>|||Why the tables are blocked until I restart the sql server ?
I can't write nolock in each query. Is there some other way?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:E17B8CBD-CF39-4857-8FEF-7AEBEE25375C@.microsoft.com...
> If you are happy with dirty reads you can do this
> SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
>
> http://sqlservercode.blogspot.com/
> "simon" wrote:
>|||On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:

>Why the tables are blocked until I restart the sql server ?
>I can't write nolock in each query. Is there some other way?
Hi Simon,
It appears to me that there are two things wrong:
1. You have somehow set your transactions to an isolation leven that is
higher than the standard "READ COMMITTED" level. With read committed,
locks for data being read are released when the statement finishes. With
REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
transaction.
2. You are starting transactions that you don't finish. That might be
because not each BEGIN TRANSACTION in your app is matched by either a
COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
explicit transactions, but have the autocommit transaction mode switched
off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
automatically started by SQL Server, but they still have to be ended by
an explicit COMMIT or ROLLBACK statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
I don't set any transaction.
I just open sqlQueryAnalyzer and put this code:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
And open other window and put this code:
UPDATE table2 set column1='test'
If I execute each statement separately, than both works. The first statement
takes about minute, the update one less than second.
If I execute the update statement while select is working than I get blocked
tables for infinite time.
Any idea?
regards,S
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5cbtl1d40ntshsauftgqtjf2m4ucp3el1r@.
4ax.com...
> On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:
>
> Hi Simon,
> It appears to me that there are two things wrong:
> 1. You have somehow set your transactions to an isolation leven that is
> higher than the standard "READ COMMITTED" level. With read committed,
> locks for data being read are released when the statement finishes. With
> REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
> transaction.
> 2. You are starting transactions that you don't finish. That might be
> because not each BEGIN TRANSACTION in your app is matched by either a
> COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
> explicit transactions, but have the autocommit transaction mode switched
> off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
> automatically started by SQL Server, but they still have to be ended by
> an explicit COMMIT or ROLLBACK statement).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 26 Oct 2005 09:21:56 +0200, simon wrote:

>Hugo,
>I don't set any transaction.
>I just open sqlQueryAnalyzer and put this code:
>declare @.n int
>set @.n=50
>while @.n>0
>begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
>end
>And open other window and put this code:
>UPDATE table2 set column1='test'
>
>If I execute each statement separately, than both works. The first statemen
t
>takes about minute, the update one less than second.
>If I execute the update statement while select is working than I get blocke
d
>tables for infinite time.
>Any idea?
Hi Simon,
Not now - I'll need more information.
Please post details about your tables: CREATE TABLE statements
(including all properties, constraints, indexes, etc) for the tables,
INSERT statements for the data in your tables. Also, post the complete
code that you are using, as the code above will only return a syntax
error.
Another thing to try: when both statements are running, open a third
window and execute
sp_lock
Post the results in a reply to this message.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Blocked process mystery

This morning, we discovered that our application was timing out on several
pages. We tracked it down to a table that couldn't be read without timing
out. In Enterprise Manager, we found a table (TAB) lock on the table that
was of mode IS that was blocking other spids. The text property of the lock
showed this lock to be on a simple reporting stored procedure, which just
did a SELECT on a couple of tables that should have only taken a second or
two. We tried to debug the problem for several minutes, but to no avail.
Finally, we killed the lock and the database problem was immediately solved.
A look at SQL Profiler (which we run continuously) showed that a query that
matched the text property of the lock and had the same pid as the lock had
been started last Tuesday and ended at roughly the same time as we killed
the lock. The index name listed with the lock in Enterprise Manager also
was strange, since the index listed was not used by the stored procedure
listed in the properties.
We have had several similar problems with our database in the past, but this
is the first time we didn't resort to just a reboot. Why would a simple
stored procedure executing a select cause such problems? Why would this
procedure be allowed to run for a week? Why would we experience no problems
until days after (the table being locked was core to almost every page in
the system and was fine until this morning)? Can the index listed in the
lock information be used to debug the problem?
An even bigger question is how to handle such a problem after it occurs?
Killing the spid seems to have caused some problems in the dotNet
application and forced me to restart the app. Is there a more graceful
method of rolling back the offending process?
the spid you found did not actually lock on the tab, it was an intent share
lock . .effectively indicating that it was going to lock the table(possibly
pages within the table). what version of sql do you have running(and what
service pack) if you are running sql2k plus sp3 then ::fn_get_sql can be
useful in the future to indicate what the currently executing sql was for
that particular spid.
I would also switch on traceflag -T1204 on this server to ensure that you
capture the full details of the blocking in the sqlerror log.
I would also look at your current configurations for query govenor cost
limit and possibly reduce this to an acceptable value which is in line with
your 'Longest Running Query'(LRQ) . . .if you have profiler running
regularly u should be able to determine what your longest running query is
and set you query govenor cost limit accordingly, preventing such problems.
re: stopping the spid . .the only option is to kill the spid(or find out the
application that had started the process and closing the application)
it will be useful for the next time it happens to perform a select * from
master.dbo.sysprocesse(nolock) to identify what the current waittype / wait
resource /waittine were for the query in question . . as a means to
establish if there are other hidden issues within your system.
HTH
Olu Adedeji
"Stephen Brown" <nospam@.telusplanet.net> wrote in message
news:cin2ht$rbf$1@.utornnr1pp.grouptelecom.net...
> This morning, we discovered that our application was timing out on several
> pages. We tracked it down to a table that couldn't be read without timing
> out. In Enterprise Manager, we found a table (TAB) lock on the table that
> was of mode IS that was blocking other spids. The text property of the
lock
> showed this lock to be on a simple reporting stored procedure, which just
> did a SELECT on a couple of tables that should have only taken a second or
> two. We tried to debug the problem for several minutes, but to no avail.
> Finally, we killed the lock and the database problem was immediately
solved.
> A look at SQL Profiler (which we run continuously) showed that a query
that
> matched the text property of the lock and had the same pid as the lock
had
> been started last Tuesday and ended at roughly the same time as we killed
> the lock. The index name listed with the lock in Enterprise Manager also
> was strange, since the index listed was not used by the stored procedure
> listed in the properties.
> We have had several similar problems with our database in the past, but
this
> is the first time we didn't resort to just a reboot. Why would a simple
> stored procedure executing a select cause such problems? Why would this
> procedure be allowed to run for a week? Why would we experience no
problems
> until days after (the table being locked was core to almost every page in
> the system and was fine until this morning)? Can the index listed in the
> lock information be used to debug the problem?
> An even bigger question is how to handle such a problem after it occurs?
> Killing the spid seems to have caused some problems in the dotNet
> application and forced me to restart the app. Is there a more graceful
> method of rolling back the offending process?
>

Blocked process mystery

This morning, we discovered that our application was timing out on several
pages. We tracked it down to a table that couldn't be read without timing
out. In Enterprise Manager, we found a table (TAB) lock on the table that
was of mode IS that was blocking other spids. The text property of the lock
showed this lock to be on a simple reporting stored procedure, which just
did a SELECT on a couple of tables that should have only taken a second or
two. We tried to debug the problem for several minutes, but to no avail.
Finally, we killed the lock and the database problem was immediately solved.
A look at SQL Profiler (which we run continuously) showed that a query that
matched the text property of the lock and had the same pid as the lock had
been started last Tuesday and ended at roughly the same time as we killed
the lock. The index name listed with the lock in Enterprise Manager also
was strange, since the index listed was not used by the stored procedure
listed in the properties.
We have had several similar problems with our database in the past, but this
is the first time we didn't resort to just a reboot. Why would a simple
stored procedure executing a select cause such problems? Why would this
procedure be allowed to run for a week? Why would we experience no problems
until days after (the table being locked was core to almost every page in
the system and was fine until this morning)? Can the index listed in the
lock information be used to debug the problem?
An even bigger question is how to handle such a problem after it occurs?
Killing the spid seems to have caused some problems in the dotNet
application and forced me to restart the app. Is there a more graceful
method of rolling back the offending process?the spid you found did not actually lock on the tab, it was an intent share
lock . .effectively indicating that it was going to lock the table(possibly
pages within the table). what version of sql do you have running(and what
service pack) if you are running sql2k plus sp3 then ::fn_get_sql can be
useful in the future to indicate what the currently executing sql was for
that particular spid.
I would also switch on traceflag -T1204 on this server to ensure that you
capture the full details of the blocking in the sqlerror log.
I would also look at your current configurations for query govenor cost
limit and possibly reduce this to an acceptable value which is in line with
your 'Longest Running Query'(LRQ) . . .if you have profiler running
regularly u should be able to determine what your longest running query is
and set you query govenor cost limit accordingly, preventing such problems.
re: stopping the spid . .the only option is to kill the spid(or find out the
application that had started the process and closing the application)
it will be useful for the next time it happens to perform a select * from
master.dbo.sysprocesse(nolock) to identify what the current waittype / wait
resource /waittine were for the query in question . . as a means to
establish if there are other hidden issues within your system.
HTH
Olu Adedeji
"Stephen Brown" <nospam@.telusplanet.net> wrote in message
news:cin2ht$rbf$1@.utornnr1pp.grouptelecom.net...
> This morning, we discovered that our application was timing out on several
> pages. We tracked it down to a table that couldn't be read without timing
> out. In Enterprise Manager, we found a table (TAB) lock on the table that
> was of mode IS that was blocking other spids. The text property of the
lock
> showed this lock to be on a simple reporting stored procedure, which just
> did a SELECT on a couple of tables that should have only taken a second or
> two. We tried to debug the problem for several minutes, but to no avail.
> Finally, we killed the lock and the database problem was immediately
solved.
> A look at SQL Profiler (which we run continuously) showed that a query
that
> matched the text property of the lock and had the same pid as the lock
had
> been started last Tuesday and ended at roughly the same time as we killed
> the lock. The index name listed with the lock in Enterprise Manager also
> was strange, since the index listed was not used by the stored procedure
> listed in the properties.
> We have had several similar problems with our database in the past, but
this
> is the first time we didn't resort to just a reboot. Why would a simple
> stored procedure executing a select cause such problems? Why would this
> procedure be allowed to run for a week? Why would we experience no
problems
> until days after (the table being locked was core to almost every page in
> the system and was fine until this morning)? Can the index listed in the
> lock information be used to debug the problem?
> An even bigger question is how to handle such a problem after it occurs?
> Killing the spid seems to have caused some problems in the dotNet
> application and forced me to restart the app. Is there a more graceful
> method of rolling back the offending process?
>

block all apps except for...

Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.
|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)

>
> TIA, ChrisR
>
|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com... [vbcol=seagreen]
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
come[vbcol=seagreen]
boss
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>
|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas

"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR

block all apps except for...

Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisROn Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com...
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
> >Is there a way to make it so that nobody can connect to sql unless they
come
> >in through a certain app... without the use of Application Roles. ( my
boss
> >doesnt want to have to pass in a password each time.)
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR

Blob as text

Hi,

I have a conversion application which convertts an access database to an sql server(different versions). I'm using stored procedures. The thins is that I export the OLe Object form access to SQL varbinary. what I do is to convert the binary data from the OLE Object to string using ToBase64String. The thing is that when I execute the SQL statement I get the following error:

Error:Operand type clash: text is incompatible with varbinary.

Can anyone tell me what I do wrong and how can I fix this? Thanks.

Can you post the SQL Statement that is being sent? At the very least print the text out and post it here. In the statement the value should look something like:

'010101'

and not like:

0x010101

If not, you might want to go to the language specific forum to ask there by posting the code you are trying to use.

|||

the value is something like:

'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='

|||

That is not binary, binary would be something like this 0x53516C20536572766572

what you posted is some (.NET) encrypted value that is stored in a column

The person/program who encrypted that is the person who will be able to decrypt that value also

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||well if you'd read the hole post you 'd notice that I've applied the ToBase64String function to the binary data. |||

That value looks okay enough. What about the query that is doing the entering? Can you capture that with profiler?
If this is a varchar or text column, that value should work just fine. For example:

CREATE TABLE testText
(
textValue text --use varchar(max) if this is SQL Server 2005
)
go
INSERT INTO testText
SELECT 'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='
go
SELECT *
FROM testText

textValue
-
DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==

So there is something else going on...

|||

Well as I've said I'm using SQL statements stored in a file. The statement is something like:

CREATE TABLE testBin
(
textValue varbinary

)
go
INSERT INTO testBin('DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==')
go

|||

Try this

CREATE TABLE testBin
(
textValue varbinary(5000)

)
go
INSERT INTO testBin VALUES(convert(varbinary(5000),'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='))
go

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You are trying to convert a string literal that is not in hexadecimal format to varbinary implicitly and this will not work. You need to use either text/ntext in SQL Server 2000 or varchar(max)/nvarchar(max) in SQL Server 2005 for the destination column. Or you can cast the value from one type to another which hurts performance. See BOL topic below on how to specify values for various data types:

http://msdn2.microsoft.com/en-us/ms179899(SQL.90).aspx

And also the CAST topic that has a table showing various conversions possible (implicit/explicit).

http://msdn2.microsoft.com/en-us/ms187928(SQL.90).aspx

|||well do you have a better solution for importing blob data using stored sql statements? And this should work with sql 2000 and 2005 also.|||Would this help.

create table tmp(i int identity primary key,img image default '0x0')
insert tmp(img) values(default)

go
create proc usp
@.i int,
@.img image
as
declare @.ptr binary(16)
select @.ptr=textptr(img)
from tmp
where i=@.i

writetext tmp.img @.ptr @.img
go
declare @.b varbinary(8000)
set @.b=0x0000007B

exec usp 1,@.b

select *,convert(int,@.b) ,convert(int,convert(binary(4),img)) [img]
from tmp
go
drop proc usp
drop table tmp|||

Thanks for your reply! But it doesn't help; because I want to do everything with SQL statements. Meaning I want to export the Access database to a file which contains SQL statements, which can be run on using an SQL command interpreter like osql.exe or my own SQL command interpreter. So the blob has to be in the SQL statement stored in the SQL file.

Any idea?