September 18, 2010 at 3:55 am
On one of my databases DBCC OPENTRAN returs following:
Replicated Transaction Information:
Oldest distributed LSN : (1042:63:1)
Oldest non-distributed LSN : (0:0:0)
I have remote proc trans ON.
Distrubuted transaction coordinator Transaction list is empty
I have no other problems (log growth, locks..)
Also I have SSIS jobs but they aren't soposed to use Distributed transactions
I have lot of cross databases store procedures calls, and as I unerstand thoose transactiones are promoted to distributed (because of remote proc trans is set to ON) but they are soposed to look as local to sql users.
On sp_who and sp_who2 all looks clear to me.
How can I find out what this transaction is and how to stop it?
please help...
If you don't like how things are, change it! You're not a tree.
September 22, 2010 at 11:27 pm
This happens when the database was participating in transactional replication but it was removed properly or restored from a replicated database.
To stop this, add the database to replication and remove it from replication.
Hope this helps you.
September 22, 2010 at 11:57 pm
Database was not part of transactional replication. When I try to setup local publication I got a message:
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.
------------------------------
ADDITIONAL INFORMATION:
Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)
But when I tried that on my test environment it worked. So I am thinking about restoring backup from production environment to test, and then setup publication and removing it to get rid of distributed transaction, and then restoring backup from test environment to production...
If you don't like how things are, change it! You're not a tree.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply