|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 11:39 AM
Points: 1,483,
Visits: 2,044
|
|
If you see nothing in the output from the following query then you can disable replication for that database.
select * from syspublications -- will check if there are any publications on that database
If none then you can use sp_removedbreplication - details on the use of this procedure can be found HERE.
Please don't do this unless you are sure that there are no other publications on that database. :)
David
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,005,
Visits: 12,131
|
|
Kwisatz78 (11/19/2009)
If so how do I resolve this?
Providing there is no existing transactional replication publication....
Create a new transactional replication publication.
Run sp_repldone in the published database
Drop the newly created publication.
Run DBCC OPENTRAN and see if the replicated and non-replicated lines remain.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 12, 2010 3:12 AM
Points: 100,
Visits: 274
|
|
There is still a used publication in place so dropping replication isn't an option, I will look at using your method Gila and let you know how I get on.
Many thanks.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 11:39 AM
Points: 1,483,
Visits: 2,044
|
|
What kind of publication is that? You had stated no logreader agent and you were trying to create a snapshot (which should be easily recreatable). Merge?
Regardless read the documentation for sp_repldone before you go and execute it. http://msdn.microsoft.com/en-us/library/ms173775.aspx
Especially considering you have other replication for that database....
David
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 12, 2010 3:12 AM
Points: 100,
Visits: 274
|
|
Hi this has worked a treat. I actually didn't need to create a new publication but by running:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
and then waiting for the next transaction log backup I was able to shrink the file.
Thanks for all your help.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,005,
Visits: 12,131
|
|
That's a temporary fix. The create and drop publications is needed for a permanent fix if this is what I think it is (an imprperly cleaned up transactional replication publication).
If you check DBCC OPENTRAN, is there still a reference to replicated and unreplicated transactions? If so, all you've done is fix the symptoms for now, the problem will be back in a few days.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
