Thursday, March 8, 2012

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

No comments:

Post a Comment