0

There is a transaction that has been open on this instance since it was restarted and is preventing us from clearing the ldf.

Here is the transaction information:

Transaction information for database 'MYDATABASE'.

Oldest active transaction:
    SPID (server process ID): 7s
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (54883:155655:1)
    Start time    : Jun 30 2014  5:45:35:820PM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Does anyone know what 7s means, I have not yet seen a SPID with a letter in it.

Thanks, Craig

Update 14:03

I ran the resolution suggested here and this is the result after restart:

2014-07-18 14:01:12.83 spid7s      Mise à niveau des paramètres d'abonnement et des objets système dans la base de données [MYDATABASE].
2014-07-18 14:01:13.03 spid7s      Nom d'objet 'MSreplication_subscriptions' non valide.
2014-07-18 14:01:13.03 spid7s      Error executing sp_vupgrade_replication.
2014-07-18 14:01:13.03 spid7s      Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
2014-07-18 14:01:13.04 spid7s      Saved upgrade script status successfully.

I still have the same problem. Any ideas on how to resolve this?

Update 19/07 Problem solved by adding the MSReplications_subscriptions table to the database where sp_vupgrade_replication was failing

3 Answers 3

2

I guess this is a known issue and Microsoft has released information about it in below links

Cause:

This issue occurs because the "tran_sp_MScreate_peer_tables" transaction was left open by a replication upgrade script during the upgrade. This open transaction prevents usual log truncation.

Transaction log for database is growing with system SPID as open transaction

There is also a documented support article which will help you in understanding the issue

Database transaction log continues to grow after upgrade

5
  • Found the error. spid7s Error executing sp_vupgrade_replication. I am following the resolution steps now Jul 18, 2014 at 11:57
  • Updated my answer Jul 18, 2014 at 12:05
  • @Craig: Please see solution in link:blogs.msdn.com/b/sqljourney/archive/2012/08/09/… or enable trace flag -T3601 and see what is failing and causing issue and resolve accordingly. In my case this worked. What is version and edition of database did you applied latest service pack/CU ?
    – Shanky
    Jul 18, 2014 at 13:07
  • Thanks Shanky, I forgot to enable the trace flag before restarting. I'll have to wait for the next restart before attempting again. Jul 18, 2014 at 13:30
  • 1
    Thanks again, problem solved by adding MSReplications_subscriptions table to the database where sp_vupgrade_replication was failing Jul 19, 2014 at 13:03
1

Transaction log for the database is growing and system SPID is holding open transaction.

If you see Name user_transaction then script upgrade for database has failed leaving an open transaction. Verify the SQL Server error log to identify why the script upgrade has failed.

You need to enable Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log while doing script upgrade . Identify the batch which is failing and troubleshoot the batch.

Oldest active transaction:

SPID (server process ID): 7s

UID (user ID) : -1

Name : user_transaction

LSN : (543263:28204:1)

Start time : Dec 8 2011 11:02:19:483PM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (544101:227459:27)

Oldest non-distributed LSN : (543263:28204:1)

Transaction name for 7s is user_transaction and 6S transaction is also there for that transaction name would be tran_sp_MScreate_peer_tables

For 6s you need to do the following

Restart SQL-Server and wait for Script upgrade to complete and then start the SQL Server Agent.

1
-1

SPID followed by s like 7s here means the Open transaction is on the Secondary node and not this node, so you will have to kill the transaction on that node.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.