BUG: Rollback fails with errors 3314 and 9001 if you enlist multiple connections in the same transaction (811476)



The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)

SQL Server 7.0:56707
SQL Server 8.0:215381

SYMPTOMS

If you enlist multiple connections to different databases in the same transaction, and there is no guest account, the rollback of the transaction may fail with errors 3314 and 9001. You may see the following messages in the SQL Server error log:

1999-10-21 15:35:11.25 spid8 Error: 3314, Severity: 21, State: 4 
1999-10-21 15:35:11.25 spid8 Error while undoing logged operation in database 'Northwind'. Error at log record ID (24:368:2).. 
1999-10-21 15:35:11.32 spid8 Error: 9001, Severity: 21, State: 1 
1999-10-21 15:35:11.32 spid8 The log for database 'Northwind' is not available.. 
1999-10-21 15:35:11.34 spid8 Error: 3314, Severity: 21, State: 2 
1999-10-21 15:35:11.34 spid8 Error while undoing logged operation in database 'Northwind'. Error at log record ID (24:368:1).. 
1999-10-21 15:35:21.48 spid6 Closing file d:\mssql7\DATA\northwnd.mdf. 
1999-10-21 15:35:21.50 spid6 Closing file d:\mssql7\DATA\northwnd.ldf. 
1999-10-21 15:35:21.51 spid6 Starting up database 'Northwind'. 
1999-10-21 15:35:21.51 spid6 Opening file d:\mssql7\DATA\northwnd.mdf. 
1999-10-21 15:35:21.56 spid6 Opening file d:\mssql7\DATA\northwnd.ldf. 
1999-10-21 15:35:21.78 spid6 4 transactions rolled forward in database 'Northwind' (6). 
1999-10-21 15:35:21.78 spid6 0 transactions rolled back in database 'Northwind' (6). 

If you are using trace flag -y916, and you run the SQL Profiler trace while the error is encountered, SQL Profiler shows a short stack dump that is similar to the following:

Short Stack Dump
0x77f1d4cb Module(KERNEL32+1d4cb) (RaiseException+6a)
0x005b5953 Module(sqlservr+1b5953) (ex_raise2+ec)
0x00506417 Module(sqlservr+106417) (ex_raise+5f)
0x00596ce2 Module(sqlservr+196ce2) (usedb+c0)
0x00432d83 Module(sqlservr+32d83) (CAutoDb::FUse+31)
0x00402cd5 Module(sqlservr+2cd5) (opentabid+77)
0x0043134e Module(sqlservr+3134e) (IndexDelete+46)
0x00431212 Module(sqlservr+31212) (XDES::UndoPageOperation+168)
0x00431ceb Module(sqlservr+31ceb) (XDES::RollbackToLsn+22a)
0x00430560 Module(sqlservr+30560) (XDES::Rollback+14e)
0x00431abf Module(sqlservr+31abf) (XCB::Rollback+2f9)
0x006e8603 Module(sqlservr+2e8603) (CDTCTransaction::abortTran+d0)
0x005aea0c Module(sqlservr+1aea0c) (rm_do_tm_work+102)
0x006e6a07 Module(sqlservr+2e6a07) (rm_ods_handler_body+773)
0x006e60cf Module(sqlservr+2e60cf) (rm_ods_handler+9e)
0x4106135c Module(opends60+135c) (execute_event+659)
0x41066fc3 Module(opends60+6fc3) (process_commands+11b)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04f3e Module(KERNEL32+4f3e) (BaseThreadStart+51)

CAUSE

This problem occurs because the logins that are used for the database connections do not have permissions to all the databases in the transaction.

WORKAROUND

To work around the problem, perform one of the following steps:
  • Make sure that each database login has appropriate permissions to all the databases in the same transaction.
  • Use the same user for each connection enlisted in the transaction, and make sure that the user is granted permissions to all the databases in the transaction.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

Modification Type:MajorLast Reviewed:4/25/2005
Keywords:kbSecurity kbAuthentication kblogin kbServer kbprb kbBug KB811476 kbAudDeveloper