Drop database that will not drop because of replication 2

[jcaulder]

I have a transaction log file that has grown to 15GB. Nothing I do will shrink or truncate the log file because it says there are uncommitted transactions related to replication.

Unfortunately, this database is not currently replicating although it was at one time. So any commands I issue that refer to replication fail with a message that the database is not replicating. Anything I do to shrink the log file fails with a message that it is replicating.

I tried re-publishing the database with the plan of then deleting the publication hoping it would clear everything but I can't create a publication because, you guessed it, the transaction log is full.


So my real goal is to somehow shrink the log file or delete it or as a last resort, drop the database entirely.

Anyone know any tricks to accomplish this. All the MS recommendations fail because of the funky state the database is in. I think I need a brute force method at this point.

TIA!

J

 

[ptheriault]

As a last resort you could try to stop the SQL Services and delete the log file?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLWilts]

that would bring the database up as suspect. Try googling SP_Repldone and also in Enterprise manager double check that this DB is not marked for replication - is there a sharing hand under the icon for the DB by the way?

 

[jcaulder]

I had earlier tried sp_repldone based on MS documentation to no avail. Error message returned says the 'database is not published'.

Also, not sure but MS documentation says this is used in transactional replication whereas this database was once merge replication. In any case, it will not execute as mentioned above.

Yes, there is a hand underneath the database showing that it is marked for replication.

Any sp I try to run such as sp_replicationdboption fails with error message that the transaction log is full.

Thx,

J

 

[SQLWilts]

Ahhh.

Try this in Enterprise Manager (although I prefer scripts, sometimes this is easier).
Make sure that you have highlighted the "databases" node.
Go to tools/replication/configure publishing, subscriber and distribution. click the "Publication databases" tab - is there a tick in the Merge column next to your DB? If so, un-tick it.
HTH

 

[jcaulder]

If the database comes up as suspect, can I back it up at that point and use this to restore from? Nothing in the transaction log is of benefit at this point. I would just like to preserve the actual data, get rid of the huge transaction log, re-create the transaction log and bring it back up functional.

J

 

[jcaulder]

SQLWilts,

I tried your recommendation but once again, operation failed because the tran log is full.

Thx for the suggestion. Anything else you can think of to get around this??

J

 

[jcaulder]

I managed to free up enough space on the drive that the drop replication sp worked.

Now I have an even larger transaction log file that still does not appear to respond to dbcc shrinkfile. I can only assume that it is because of uncommitted replication transactions which is apparently what caused the issue initially.

I have the database in simple mode and have issued checkpoints but it still will not shrink. sp_repldone still errors out saying the database is not published.

 

[ptheriault]

detach the database, re-name the log and attach it with out the log. It will create a new log.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jcaulder]

That worked!!

I am free from the monster log file.

Thanks for the info guys!! It was most appreciated!

J