The current transaction cannot be committed and cannot support operations that write to the log file.

   Msg 3930, Level 16, State 1, Line 25 The current transaction cannot be committed and cannot support operations that write to the log file. This issue comes when we are using TRY-CATCH block and whithin that we have started a TRNASACTION. Below is an example of SP, on execution of which generates error 3930.

CREATE PROC [dbo].[usp_StoredProcedureName]

@USERID           VARCHAR(50)

AS

BEGIN

SET NOCOUNT ON




      IF(@USERID
= 'ABC')

      BEGIN TRY

      BEGIN TRAN




            INSERT INTO TABLE1

            SELECT * FROM TABLE2

            WHERE USERID = @USERID

            

            COMMIT TRAN

      END TRY

      BEGIN CATCH

            DECLARE @ERROR    VARCHAR(200)

            SET @ERROR =
ERROR_MESSAGE()

            ROLLBACK TRAN

      END CATCH



END




EXEC [usp_StoredProcedureName] 'ABC'

You can resolve it by checking XACT_STATE() and then rolling back the transaction, see below:

CREATE PROC [dbo].[usp_StoredProcedureName]

@USERID           VARCHAR(50)

AS

BEGIN

SET NOCOUNT ON




      IF(@USERID
= 'ABC')

      BEGIN TRY

      BEGIN TRAN




            INSERT INTO TABLE1

            SELECT * FROM TABLE2

            WHERE USERID = @USERID




            IF (XACT_STATE() = 0)

            COMMIT TRAN

      END TRY

      BEGIN CATCH

            DECLARE @ERROR    VARCHAR(200)

            SET @ERROR =
ERROR_MESSAGE()

            IF (XACT_STATE() <> 0)

            ROLLBACK TRAN

      END CATCH




END


EXEC [usp_StoredProcedureName] 'ABC'

Another way to get rid of it is to see the error or exception and resolve it first. To do so, notice the output, you will get error "The current transaction cannot be committed and cannot support operations that write to the log file" under the Message tab. But under Result tab you will get the error like "String or binary data would be truncated." or any other similar exception. Go to resolve that exception and you are done.

Message Tab is like :



And Result Tab is like :

 






Reference: Govind Badkur(http://sqlserver20.blogspot.com)