SQL Server2005 transaction log

We are using WCF services for a windows application. For inserting multiple objects contained in a single object into database, we are using Transactions using Transaction Scope class.

We are encountering a strange scenario where even after closing the database connections after insertions and calling TransactionScope.Complete, the connection remains inactive for hours on the Oracle database. Could anyone suggest if this is due to the usage of .NET TransactionScope class?

We are facing this problem only in production environment and not in development environment.

The context is a SQL Native client that inserts data into two tables "TableA" and "TableC" in database "DB1". The operation must be atomic : This is done by using a transaction. So far so good.

On "TableA" in "DB1", I have a CLR INSERT trigger that should also update data in "TableB" stored in another database, "DB2" on the same server. I would like to use the same transaction as the DML that fired the trigger, so that if insert on TableC fails, modifications on TableB are rolled back.

If database DB2 is located on another server, MSDTC automatically promotes the ambiant transaction to a distributed one, and everything works fine.

Is it possible to use a loopback connection string that specify "enlist=false" and write code that manually promotes the transaction ?

Any help appreciated.

TIA.

Hi

I have been reading on this topic including various blogs by some SQL Server heavy weights, I would like to confirm if my understanding is true.

Here is my understanding, when a transaction happens, the TSQL statement, along with the data page's before any after image is copied to the transaction log ldf file. Is this true?

If so, the second part of the question would be why do we need to have a before image of the data page. Is this used during crash recovery to facilitate fast recovery? Is the after image used for transaction log restores?

I also have some confusion when reading Kalen's book when she talked about the bulk-logged transaction log backup will be larger. If the BCM marks the extent only and not actually write the data pages to the ldf file, then we should have a smaller ldf file in the bulklog mode. During my testing, this had not been the case. I simply created a table and bulk inserted 100,000 rows from a server side trace(a flat file I bcp’ed from a tracetable), and for all three recovery models, I recorded almost same duration and for the transaction log backup of bulk-loggged an full recovery being the same size. Maybe my example is too simple here. Can anyone give me a working example to demonstrate bulk operation is actually faster in the bulk-log mode?

Best regards

Hi,

Can anyone please guide me of how to implement Transactions in SSIS when multiple Dataflow task runs in parallel.? Any help will be highly appreciated.

Regards

Sanjeev

I have observed that after committing data, excel does not start a new transaction, resulting in an error when excel tries to perform a rollback afterwards.

I have created a Microsoft Connect bug for this problem: https://connect.microsoft.com/SQLServer/feedback/details/631402/excel-2010-olap-pivottable-does-not-start-new-transaction-after-commit

The following steps reproduce the error. I also listed some queries observed using the profiler:

1) Create a pivottable connected to an SSAS OLAP Cube that has writeback enabled.

2) Add an editable measure to the pivot table.

3) Enable what-if analysis on the pivot table.

      Query: BEGIN TRANSACTION

4) Switch-off "automatically calculate changes"

5) Change a value

6) Publish Changes

      Query: UPDATE CUBE ....

      Query: COMMIT TRANSACTION

      (Note that no new transaction was started)

7) Disable What-If analysis

      Query: ROLLBACK TRANSACTION

      Error: XML for Analysis parser: The commit or rollback request cannot be completed because there is no active transaction in this session.

Variations:

- If after step 6, we change another value, but do not publish this value, and then disable What-If Analysis, excel does not fire a ROLLBACK TRANSACTION and thus there is no error.

- If after step 6, we change another value and choose to calculate, but not publish the changes, excel does an update cube. When then diabling What-If excel fires a ROLLBACK TRANSACTION which succeeds.

After much searching, reading, re-reading and further searching and reading - I find myself at a dead end.
With so little detail (at least that I can find/understand) from msdn articles and BOL, I throw myself humbly at the mercy of those better experienced to help solve the problem I face. And to any of those better equipped persons that donate their time to help me, useful or not, I sincerely thank you in advance.

 I trust you will forgive my likely naive attempt to explain the problem...
 If I could talk to my SQL server directly, which I often do, and have it actually understand me, which it often doesnt, I would say...

 "Please can you stop anything from changing the data i'm about to work with and dont let anything add a record related to those that i'm about to work with... but if anyone wants to read the data while i'm working thats ok."

Essentially then, I think i'm looking for a way to make a table readonly for the duration of my stored procedure transaction.

The basic flow of my sp is:

BEGIN TRAN

-- Take a copy of the data I want to work with (ok for people to read the actual data)
CREATE TABLE #TempTable (RowId int identity(1,1), Table1_PrimaryKey int, KeepRow bit)
INSERT INTO #TempTable (Table1_PrimaryKey, KeepRow)
SELECT Table1_PrimaryKey, 0 
FROM Table1
WHERE (stuff that decides which rows I want)

Start loop on records In #TempTable 

IF (i want this row) BEGIN
    UPDATE Table1 SET KeepRow = 1
END

Carry on looping til done... then...

DELETE FROM Table1
WHERE Table1_PrimaryKey IN (
    SELECT Table1_PrimaryKey FROM #TempTable WHERE KeepRow = 0
)

COMMIT TRAN

END TRAN

The bit thats making my head spin is that all relevant rows I have selected to start with form a kind of set. They are related by a foreign key to a parent table. I need to make sure that not only are the rows I am working with not modified or deleted - but no new rows can be added to Table1 (related to the same parent object) while im working.

It is however, safe for people to look at the rows while im deciding about them (as I have copied them into a temp table to work with).

So.. kind of make table read-only while i'm working.
It feels like locking of some kind should help here.... but i'm fearing a blanket TABLOCKX as I understand it will prevent prevent others reading the data while im deciding what to keep. Any ideas which locks are appropriate? Have I made any sense at all?

Thank you again in advance - even if only for reading this far.

Hi

I have a custom dead letter queue service to pick up messages from DLQ. This service picks up failed message from DLQ and tries to resend to a different end point. If the new endpoint doesn't work, it rolls back transaction. This works great. Here is the code.

BEGIN TRY

	-- CHECK IF THE DATA FOR THAT SERIAL NO IS OK

	EXEC dbo.sCheckSerialNumberInfo @SerialNo 

                -- do some other things

END TRY

BEGIN CATCH

	IF XACT_STATE() = -1 ROLLBACK

	--PRINT XACT_STATE()

	DECLARE @ErrNo INT, @ErrSeverity INT, @ErrState INT, @ErrLine INT

	DECLARE @ErrProcedure NVARCHAR(126)

	DECLARE @ErrMessage NVARCHAR(2048)

	DECLARE @CustomErrMessage NVARCHAR(512)

	SELECT @ErrNo = ERROR_NUMBER(), @ErrSeverity = ERROR_SEVERITY(), @ErrState = ERROR_STATE(), @ErrLine = ERROR_LINE(), @ErrProcedure = ERROR_PROCEDURE(), @ErrMessage = ERROR_MESSAGE()

	SET @CustomErrMessage = 'Error in tblorders_units Update Trigger' 

	EXEC dbo.sLogError @ErrNo, @ErrSeverity, @ErrState, @ErrLine, @ErrProcedure, @ErrMessage, @CustomErrMessage

END CATCH

-- Declare the return variable here

DECLARE @RetVal INT

-- find RetVal

-- IF THERE IS AN ERROR<br/>	IF @RetVal = 1

	BEGIN

	-- RAISE ERROR SO CATCH IN TRIGGER CAN PROCESS THE ERROR

	RAISERROR ('Gtin, lot number, manufacture date or expiry date for Serial No %s is invalid',

					16,

					1,

					@SerialNo)

         END	

-- Return the result of the function

RETURN @RetVal

INSERT INTO dbo.ERROR_LOG

           ([ErrorNumber]

           ,[ErrorSeverity]

           ,[ErrorState]

           ,[ErrorLine]

           ,[ErrorProcedure]

           ,[ErrorMessage]

           ,[CustomErrorMessage]

           ,[ErrorDate])

     VALUES

           (@ErrNo,

            @ErrSeverity,

            @ErrState,

            @ErrLine,

            @ErrProcedure,

            @ErrMessage,

            @CustomErrMessage,

            GETDATE())