Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a year.
ALTER PROCEDURE [dbo].[bsi_transfer_reallocate] @transfer_no int
AS
BEGIN
BEGIN TRY
[a few lines of code]
BEGIN TRANSACTION REALLOCATE;
(over 700 lines of code)
COMMIT TRANSACTION REALLOCATE;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION REALLOCATE;
SET @dbg_msg = 'Transfer ' + CAST(@transfer_no AS varchar(8)) + ' aborted due to error condition with error number ' +
CAST(ERROR_NUMBER() AS varchar(200)) + ' and message ' + ERROR_MESSAGE()
INSERT INTO BATTERY_debug VALUES ('REALLOCATION Failure', GETDATE(), @dbg_msg)
select @dbg_msg
RETURN (1)
END CATCH
END
return(0)
BEGIN TRANSACTION
being run, the culprit lies in "a few liones of code" most likely. How should we start debugging these lions? – LowlyDBA - John McCall Nov 6 '19 at 18:35