How can I debug a SQL Server data problem that I cannot reproduce in any test environment?

Question

I have a stored procedure in production that does 2 things. It updates one table and then inserts a record into another. The first step (update) seems to be happening, but we found instances, examining the data where the second step was not performed. I looked at the data and confirmed that this is not a data problem. I have confirmed that requests return relevant data to ensure that requests are completed in normal circumstances. I do not know if there might be some kind of performance problem ... or a blocking problem that occurs in the second step that prevents this step.

Error handling for a stored procedure is as follows.

BEGIN TRY BEGIN TRANSACTION; -- perform update to data -- insert record into second table. IF ( @@ERROR = 0 AND @@TRANCOUNT > 0 ) COMMIT TRANSACTION; END TRY BEGIN CATCH IF ( @@TRANCOUNT > 0 ) BEGIN ROLLBACK TRANSACTION; END DECLARE @WebSafeErrorId INT; EXEC dbo.spErrorInsert @WebSafeErrorId OUTPUT, 'Proc'; -- Reraise the error back to the client. IF ( @WebSafeErrorId != 0 ) BEGIN DECLARE @Error VARCHAR(20); SET @Error = CAST( @WebSafeErrorId AS VARCHAR(20) ); RAISERROR( @Error, 11, 1 ); END ELSE BEGIN RAISERROR( 'An error has occurred but there is no error to log.', 11, 1 ); END END CATCH; 

Of course, if an error occurred in this procedure due to which the insert did not occur, it would be logged and then raised. Code for spErrorInsert below ...

 CREATE PROCEDURE [dbo].[spErrorInsert] @ReturnErrorId INT OUTPUT , @ErrorSourceType VARCHAR(4) = NULL , @ParentErrorId INT = NULL , @StackTrace VARCHAR(MAX) = NULL AS SET NOCOUNT ON; --SET XACT_ABORT ON; -- Will indicate an error was not logged. SET @ReturnErrorID = 0; DECLARE @ErrorSource VARCHAR(200) , @ErrorMessage VARCHAR(MAX) , @ComposedErrorMessage VARCHAR(MAX) , @ErrorLine INT , @ErrorSeverity INT , @ErrorState INT , @ErrorNumber INT; SET @ErrorSource = ERROR_PROCEDURE(); SET @ErrorMessage = ERROR_MESSAGE(); SET @ErrorLine = ERROR_LINE(); SET @ErrorSeverity = ERROR_SEVERITY(); SET @ErrorState = ERROR_STATE(); SET @ErrorNumber = ERROR_NUMBER(); SET @ComposedErrorMessage = 'Message: Error occurred in procedure ' + CAST( @ErrorSource AS VARCHAR(MAX) ) + ' on line ' + CAST( @ErrorLine AS VARCHAR(MAX) ) + '. Error: ' + @ErrorMessage; BEGIN TRY INSERT INTO Errors( ParentId , ErrorSourceType , ErrorSource , [Message] , [LineNo] , Severity , Stacktrace , ts) VALUES (@ParentErrorId , @ErrorSourceType --@ErrorSourceType --- NOTE: move this into a parameter ... , @ErrorSource , @ComposedErrorMessage , @ErrorLine , @ErrorState , @Stacktrace , GETDATE() ); SET @ReturnErrorId = SCOPE_IDENTITY(); END TRY BEGIN CATCH RAISERROR( 'An error has occurred but there is no error to log.', 11, 1 ); END CATCH; 

I don’t know, maybe there is a way to get a snapshot of what is happening in the database at a certain time, when a certain procedure is called ... I'm not sure how to determine if something is wrong when it is needed? Are there any tools that I can use or sql functions that I don't know about?

Answer 1

If you want to control the database, SQL Profiler is a good place to start, but it will become obsolete.

Extended events are much more capable, and I would suggest reading about them if you are really interested in monitoring what is happening.

As a thought, if your procedure code uses the same data to update a row, since it must be inserted into another table, consider using OUTPUT.

 Update table set col1 = 'value' OUTPUT inserted.col INTO othertable Where col3 = stuff 

OUTPUT and OUTPUT INTO

Or, if this is for any audit table or log, you can use DELETED.col1 This will be the original value until it is updated. Note that INSERTED will return the value you are updating or pasting, it is simply called INSERTED for both.

Answer 2

If you have a copy of Visual Studio, try it. It allows you to execute stored procedures.

Answer 3

The approach I would like to try is to first take a copy of this procedure and comment on try / catch. I found that tsql does not cause errors if the error generation code is in the try / catch block - I think this is the equivalent of the sql exception handled by the catch clause.

I use a table in my database to keep a constant record of errors as they occur (a trick that I learned by doing inline programming) Error table creation code:

 CREATE TABLE dbo.errors ( id smallint NOT NULL IDENTITY(1, 1) PRIMARY KEY, errordesc nvarchar (max) NOT NULL, dateandtime smalldatetime NOT NULL, -- Date and time of last occurance errorcount int NOT NULL) ; 

My stored procedure for adding an entry to the error table:

 CREATE PROCEDURE jc_diagnostics.jpwsp0005_adderrordescription( @Errordescription nvarchar( max )) AS BEGIN DECLARE @Id smallint = 0 , @Currentcount int = 0; IF((@Errordescription IS NULL) OR ( @Errordescription = '')) BEGIN SET @Errordescription = 'Error description missing'; END; SELECT @Id = ( SELECT TOP ( 1 ) id FROM jc_diagnostics.errors WHERE errordesc = @Errordescription ); IF(@Id IS NOT NULL) BEGIN SELECT @Currentcount = (SELECT errorcount FROM jc_diagnostics.errors WHERE id = @Id ); SET @Currentcount = @Currentcount + 1; UPDATE jc_diagnostics.errors SET errorcount = @Currentcount WHERE id = @Id; UPDATE jc_diagnostics.errors SET dateandtime = CONVERT(smalldatetime , GETDATE()) WHERE id = @Id; END; ELSE BEGIN --new entry INSERT INTO jc_diagnostics.errors( errordesc , dateandtime , errorcount ) VALUES( @Errordescription , CONVERT(smalldatetime , GETDATE()) , 1 ); END; IF(@Id IS NULL) BEGIN SET @Id = SCOPE_IDENTITY( ); END; RETURN @Id; END; 

The calling code when an error occurs:

 Declare @Failuredesc nvarchar(max) = 'Description of error'; EXEC @Retval = jc_diagnostics.jpwsp0005_adderrordescription @Failuredesc; 

The return value of @Retval contains the identifier of the entry in the error table so you can view it

Finally, I would create code to continuously call your procedure until an error is declared. Then you can check the error table and see if it sheds light on your problem.

Hope this helps. Jude

Answer 4

Logical thinking - since you are declaring a transaction before these two steps, any error will result in the rollback of both transactions. So, most likely, there is no mistake. I would suggest checking your queries again, as it seems that the problem may be in them, and not elsewhere. Please post all code if you like additional suggestions.

Hi

Roman