Sunday, February 12, 2012

BEGIN TRAN . . . WITH MARK . . .

Hallo All,

Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORT\Administrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORT\Administrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.

On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.
See the Books Online Topic "Ensuring Recoverability of Related Databases". I believe that there is a change in SQL Server 2005.|||Hello ALL,
I have the solution now.
It have nothing to do with some changes in SQL Server 2005.
It seems just to be the following. I habe never made a backup
of the ACCT database in the past. After I executed the full
online backup of the ACCT database for the first time I get
exactly the same results as with MS SQL 2000.

No comments:

Post a Comment