March 28, 2012 at 12:00 pm
Thank you all very much, we will take several of the suggestions and try them out to see if we can nail this down.
March 28, 2012 at 12:05 pm
eshulman (3/28/2012)
Thank you all very much, we will take several of the suggestions and try them out to see if we can nail this down.
What suggestions?
1. Change @@identity to scope_identity()? Seems irrelevant now that you can confirm sequence, yet gaps in your log table.
2. Monitor for DEADLOCKs and ROLLBACKs? I thought you were already doing that?
Can you give us some insight as to what you are going to try? Again, DDL and DML would really help us to help you.
Jared
CE - Microsoft
March 28, 2012 at 12:07 pm
Thank you all very much, we will try a couple things that were suggested to see if we can figure this out...
March 28, 2012 at 12:09 pm
If there are any check constraints that may have been violated (my testing had that reported to SSMS), the @@IDENTITY will return NULL and the indentity value still gets incremented.
March 28, 2012 at 12:16 pm
Lynn Pettis (3/28/2012)
If there are any check constraints that may have been violated (my testing had that reported to SSMS), the @@IDENTITY will return NULL and the indentity value still gets incremented.
YES! I just verified the same thing, even with the most basic constraint of data type (although not classically defined as a constraint).
USE tempdb
CREATE TABLE test (id int identity(1,1), something int)
SELECT * FROM test
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
INSERT INTO test
SELECT 'Hello'
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('test')
INSERT INTO test
SELECT 'Hello'
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('test')
--DROP TABLE test
Jared
CE - Microsoft
March 28, 2012 at 12:27 pm
Yes, we were going to try SCOPE_IDENTITY but sounds like that will not make any difference since we see gaps.
ALso was going to try the trace again - it is running now with nothing in it yet...
I will collect come code and post it but as I said it is just an INSERT and SELECT @@IDENTITY.
Some of our colleagues suggested that SQL Server is batching statements together and a totally unrelated compilation error or update/insert etc. could roll back the entire batch. Is that true?
We tried to put Begin Transaction, Commit around our inserts but that did not make any difference at all.
March 28, 2012 at 12:33 pm
eshulman (3/28/2012)
Yes, we were going to try SCOPE_IDENTITY but sounds like that will not make any difference since we see gaps.ALso was going to try the trace again - it is running now with nothing in it yet...
I will collect come code and post it but as I said it is just an INSERT and SELECT @@IDENTITY.
Some of our colleagues suggested that SQL Server is batching statements together and a totally unrelated compilation error or update/insert etc. could roll back the entire batch. Is that true?
We tried to put Begin Transaction, Commit around our inserts but that did not make any difference at all.
SQL Server isn't batch requests together.
This is in a SQL Server 2008 forum, just to be sure you are running SQL Server 2008?
March 28, 2012 at 12:34 pm
how about adding another server side trace that captures all errors and warnings , and limit it to just the database in question.
http://support.microsoft.com/kb/199037
then you might see that some basic error, like a not null constraint or other constraint like Lynn mentioned is rolling back a transaction, but the application is not prepared to pass on/raise the error, so it goes by unnoticed.
that might get you exactly what the issue really is.
Lowell
March 28, 2012 at 12:36 pm
Lowell (3/28/2012)
how about adding another server side trace that captures all errors and warnings , and limit it to just the database in question.http://support.microsoft.com/kb/199037
then you might see that some basic error, like a not null constraint or other constraint like Lynn mentioned is rolling back a transaction, but the application is not prepared to pass on/raise the error, so it goes by unnoticed.
that might get you exactly what the issue really is.
I second this... The example I posted fails without rolling back a transaction. So, if your insert is failing before the transaction is created, you will not see rollbacks. However, it apparently increments the identity anyway. I would throw a TRY CATCH block around you INSERT statement to see what is the culprit. I would guess that your inserts are failing before the transaction actually starts.
Jared
CE - Microsoft
March 28, 2012 at 12:42 pm
Yes, 2008 R2...
March 28, 2012 at 12:43 pm
we will try that, thank you much...
March 28, 2012 at 1:19 pm
Below is our VB.NET code that we use to insert reservations into the XDispTrips table. Let me know if you would rather see an actual INSERT statement after it goes through this code.
sqlTrip += "INSERT INTO XDispTrips (" 'Start building our SQL INSERT statement.
If dsTrip.Tables.Count > 0 Then 'Make sure we have data to build from.
dtTrip = dsTrip.Tables(0) 'Get the first table from the dataset. It is the XDispTrips table.
If IsNothing(dtTrip) = False Then 'If we have a record then..
'--------------------------------------------------------------------------------------------------
'Loop through the first XDISPTRIPS_MAX_FIELD_INDEX columns of the XDispTrips table and add the
'column names to the INSERT statement. Notice that we skip the first column. The first column is
'trp_rec_id and it will be set automatically by the database.
'--------------------------------------------------------------------------------------------------
For iCount = 1 To XDISPTRIPS_MAX_FIELD_INDEX
'---------------------------------------------------------------------------------------------------------
'EXCLUDE FIELDS
'There are some fields that we don't want TDS to set. These fields need to be set by the dispatch system.
'Only the dispatch system is smart enough to know what these fields should be set to, plus with the
'TDS_Dispatch system you will screw up dispatching by setting the status here (because you will overwrite
'a status change made by TDS_Dispatch while the user had the trip open).
'---------------------------------------------------------------------------------------------------------
If bExcludeStatusUpdate = False OrElse _
(dtTrip.Columns(iCount).ColumnName <> "trp_status" AndAlso _
dtTrip.Columns(iCount).ColumnName <> "trp_priority_status") Then
If iCount > 1 Then
sqlTrip = sqlTrip & ", "
End If
sqlTrip = sqlTrip & dtTrip.Columns(iCount).ColumnName
End If
Next
sqlTrip = sqlTrip & ") VALUES ("
'------------------------------------------------------------------------------------------------------------------------------------------
'Loop through the first XDISPTRIPS_MAX_FIELD_INDEX columns of the XDispTrips table and add the column values to the INSERT statement.
'Notice that we skip the first column. The first column is trp_rec_id and it will be set automatically by the database.
'------------------------------------------------------------------------------------------------------------------------------------------
For iCount = 1 To XDISPTRIPS_MAX_FIELD_INDEX
'----------------------------------------------------------------------------------------------------------------------------------------
'EXCLUDE FIELDS
'There are some fields that we don't want TDS to set. These fields need to be set by the dispatch system. Only the dispatch system is
'smart enough to know what these fields should be set to, plus with the TDS_Dispatch system you will screw up dispatching by setting the
'status here (because you will overwrite a status change made by TDS_Dispatch while the user had the trip open).
'----------------------------------------------------------------------------------------------------------------------------------------
If bExcludeStatusUpdate = False OrElse _
(dtTrip.Columns(iCount).ColumnName <> "trp_status" AndAlso _
dtTrip.Columns(iCount).ColumnName <> "trp_priority_status") Then
If iCount > 1 Then
sqlTrip = sqlTrip & ", "
End If
If (dtTrip.Columns(iCount).DataType Is GetType(System.String)) _
Or dtTrip.Columns(iCount).DataType Is GetType(System.Char) Then
sqlTrip = sqlTrip & sParm(Convert.ToString(dtTrip.Rows(0).Item(iCount)))
ElseIf dtTrip.Columns(iCount).DataType Is GetType(System.DateTime) Then
If IsDBNull(dtTrip.Rows(0).Item(iCount)) Then
sqlTrip = sqlTrip & dParm(SysFnc.DefPastDate.ToString("MM/dd/yyyy HH:mm:ss"))
Else
sqlTrip = sqlTrip & dParm(Convert.ToDateTime(dtTrip.Rows(0).Item(iCount)).ToString("MM/dd/yyyy HH:mm:ss"))
End If
Else
If IsDBNull(dtTrip.Rows(0).Item(iCount)) Then
sqlTrip = sqlTrip & "0"
Else
sqlTrip = sqlTrip & Convert.ToString(dtTrip.Rows(0).Item(iCount))
End If
End If
End If
Next iCount
sqlTrip = sqlTrip & ") SELECT @@IDENTITY" 'We need to have the trp_rec_id returned back to us. This line does that.
''--------------------------------------------------------------------------------------------------------------------------
''03/23/2012 - We tried wrapping this SQL statement in a BEGIN/COMMIT block, but it did not seem to help so we reversed it.
''--------------------------------------------------------------------------------------------------------------------------
'sqlTrip += vbCrLf & "COMMIT TRANSACTION " & strTranName
OpenDataSet(dsResults, sqlTrip) 'Execute our SQL INSERT statement.
dsRow = GetFirstRow(dsResults) 'Find the new trp_rec_id.
If IsNothing(dsRow) = False Then 'Find the new trp_rec_id.
Identity = Convert.ToInt32(dsRow(0)) 'Find the new trp_rec_id.
End If
rcReturn = ResultCodes.rcOperationSuccessful 'We did it. Return SUCCESS.
End If
End If
Return rcReturn 'Return the results of this function.
Catch ex As Exception
ExceptionHandler(ex, "Common::TDS_XDispTripsWrite")
Return rcReturn
End Try
End Function
Public Function OpenDataSet(ByRef RefDataSet As DataSet, ByVal CommandString As String) As Integer
Dim iResult As Integer
Dim dsTempSet As DataSet
iResult = 0
dsTempSet = Nothing
Try
gwsDbCommon.OpenSqlDataset(dsTempSet, CommandString)
If dsTempSet.Tables.Count > 0 Then
RefDataSet = dsTempSet
iResult = ResultCodes.rcOperationSuccessful
Exit Try
End If
iResult = ResultCodes.rcErrorsOccured
Catch e As Exception
iResult = ResultCodes.rcErrorsOccured
ErrorHandler(Err, "modCO_Wrapper.OpenSQLDataSet", , True)
Finally
dsTempSet.Dispose()
End Try
Return iResult
End Function
<WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _
Public Function OpenSqlDataset(ByRef dsReturn As System.Data.DataSet, ByVal SqlText As String) As Integer
Dim iResult As Integer
Dim coConn As SqlConnection
Try
coConn = New SqlConnection(ApplicationConfiguration.ConnectionString)
Dim sdaOpenRecordset As SqlDataAdapter
dsReturn = New System.Data.DataSet
sdaOpenRecordset = New SqlDataAdapter(SqlText, coConn)
sdaOpenRecordset.Fill(dsReturn)
coConn.Close()
iResult = ResultCodes.rcOperationSuccessful
Catch ex As System.Exception
ErrorHandler(ex, "DbCommon::OpenSqlDataset", SqlText, False)
iResult = ResultCodes.rcErrorsOccured
Throw ex
Finally
Try
coConn.Close()
Catch
End Try
End Try
Return iResult
End Function
March 28, 2012 at 1:26 pm
So, your code dynamically builds the insert statements? Not being a VB programmer, are the insert statments the same everytime they are built?
March 28, 2012 at 1:38 pm
That is correct, the functions I sent you are executed about 12,000-18,000 times per day and work fine 99.9999% of the time. As I described before, the above code runs, then we do a sleep for 750ms and do a dirty (NOLOCK) read to see if the identity we got back is a record in the table. If it is not, we tell the user to save the reservation again (we have a few of these). Then we have another app that looks for these trips some several seconds later and if it finds a missing trip it goes to our logs and tries to recover them from log data (we have a few of these). The interesting thing is that the second case either the dirty read returned us a record that was rolled back right after the dirty read or there is some weird SQL Server stuff going on.
March 28, 2012 at 1:54 pm
Well... My best educated guess is that an invalid insert statement is being built and passed in the ones that are failing. I would write something into the VB.NET code (since it is what generates the insert statement) that logs each statement it creates. Then search for the statements with the IDs that are missing in the log table. That will find you your situation(s) that create an invalid insert.
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply