Friday, 27 September 2013

Current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

Current transaction cannot be committed and cannot support operations that
write to the log file. Roll back the transaction

I know that there are other questions with the exact title as the one I
posted but each of them are very specific to the query or procedure they
are referencing.
I manage a Blackboard Learn system here for a college and have direct
database access. In short there is a stored procedure that is causing
system headaches. Sometimes when changes to the system get committed
errors are thrown into logs in the back end, identifying a stored
procedure known as bbgs_cc_setStmtStatus and erroring out with The current
transaction cannot be committed and cannot support operations that write
to the log file. Roll back the transaction.
Here is the code for the SP, however, I did not write it, as it is a stock
piece of "equipment" installed by Blackboard when it populates and creates
the tables for the application.
USE [BBLEARN]
GO
/****** Object: StoredProcedure [dbo].[bbgs_cc_setStmtStatus] Script
Date: 09/27/2013 09:19:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bbgs_cc_setStmtStatus](
@registryKey nvarchar(255),
@registryVal nvarchar(255),
@registryDesc varchar(255),
@overwrite BIT
)
AS
BEGIN
DECLARE @message varchar(200);
IF (0 < (SELECT count(*) FROM bbgs_cc_stmt_status WHERE registry_key =
@registryKey) ) BEGIN
IF( @overwrite=1 ) BEGIN
UPDATE bbgs_cc_stmt_status SET
registry_value = @registryVal,
description = @registryDesc,
dtmodified = getDate()
WHERE registry_key = @registryKey;
END
END
ELSE BEGIN
INSERT INTO bbgs_cc_stmt_status
(registry_key, registry_value, description) VALUES
(@registryKey, @registryVal, @registryDesc);
END
SET @message = 'bbgs_cc_setStmtStatus: Saved registry key [' +
@registryKey + '] as status [' + @registryVal + '].';
EXEC dbo.bbgs_cc_log @message, 'INFORMATIONAL';
END
I'm not expecting Blackboard specific support, but I want to know if there
is anything I can check as far as SQL Server 2008 is concerned to see if
there is a system setting causing this. I do have a ticket open with
Blackboard but have not heard anything yet.
Here are some things I have checked:
tempdb system database:
I made the templog have an initial size of 100MB and have it auto grow by
100MB, unrestricted to see if this was causing the issue. It didn't seem
to help. Our actual tempdb starts at 4GB and auto grows by a gig each time
it needs it. Is it normal for the space available in the tempdb to be
95-985 of the actual size of the tempdb? For example, right now tempdb has
a size of 12388.00 MB and the space available is 12286.37MB.
Also, the log file for the main BBLEARN table had stopped growing because
it reached its maximum auto grwoth. I set its initial size to 3GB to
increase its size, but for some reason every time I check off
"unrestricted growth", the radio button goes back to restricting the
growth to 2GB.
Thanks for any advice.

No comments:

Post a Comment