October 14, 2008 at 9:19 am
Hello, am cate.
I accidentally deleted the distribution database after i had created a publisher, which also acted as the distributor.
My problem now is that i cant even delete the existing publications, and neither can i create a new publication
October 14, 2008 at 9:37 am
Have you tried right clicking on the replication folder in SSMS then selecting "disable publishing and distribution" to see if that will reset things back to a pristine state. Once you get that done you can re-enable publishing and hopefully get that working again.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 14, 2008 at 9:44 am
I have tried from the replication folder but it gives an error message that database distribution cannot be opened due to inaccessible files or insufficient memory space.
October 14, 2008 at 10:42 am
I don't suppose you have a backup of distrbution?
Did you drop the database, or did you delete the files of distribution? If you query sys.databases, is distribution listed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 4:00 am
When i query sys.databases, the database is listed there
October 15, 2008 at 5:05 am
you'll need to dig yourself out of this using scripting rather than GUI tools.
as an emergency to get the log size down you might want to look at running the command xp_repldone to mark transaction as completed (and flush from log)
then you'll need to script out the sp_dropdistributor, drop publication etc
if these don't work you're going to have to manually delete rows in the database from syspublications and sysarticles
you'll also have to alter master..sysdatabases - one of the columns in here - Status if i remmember correctly determines if the database is marked for replication- you'll need to find the correct value to AND the column with.
then go back through the GUI and recreate your distributor
rough guide only i'm afraid.
MVDBA
October 15, 2008 at 8:20 am
michael vessey (10/15/2008)
you'll also have to alter master..sysdatabases - one of the columns in here - Status if i remmember correctly determines if the database is marked for replication- you'll need to find the correct value to AND the column with.
Not possible on SQL 2005. The system tables can't be changed and sysdatabases doesn't even resolve down to a system table.
I don't suppose there's a backup of distribution anywhere? How did you manage to delete the files of the distribution database anyway?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 8:59 am
This is what i did.
i was curious about the distribution database and i wanted to know what it contains.
so i attached it to management studio, as a normal database, not a system db, and it attached. so when ai was done, i deleted it from management studio, rather than detaching it.
August 20, 2010 at 12:22 pm
For anyone who has this issue in the future.... I had a brainfart and ran into this issue myself.
Just go to a clean SQL Server installation (same version), create the distribution database and then back it up. Then go to your troubled server, create a clean database named distribution and restore the backup. Don't worry that it's not under system databases when you first create it. Once you run the restore, SQL Server will recognize it's a system db and place it in there.
At this point it appears the distribution management tools in SSMS will work. I hope this helps someone.
May 3, 2012 at 9:17 am
Please make note that when you are restoring a distribution database use the "WITH KEEP_REPLICATION" option for it to appear under the system database folder. If you dont use this option it will be restored as a regular user database and becomes useless as SQL Server replicatin GUI just fails to recognize that database as a user database.
December 23, 2014 at 11:53 am
We often run into this in our QA environments. The below script is a blunt hammer approach to remove replication, no matter how fubared your server is:
USE <yourreplicateddb>
EXEC sp_removedbreplication '<yourreplicateddb>';
go
USE master
EXEC sp_dropdistpublisher @@servername, 1, 1;
EXEC sp_dropdistributiondb 'distribution';
EXEC sp_dropdistributor;
Note that the third parameter for sp_dropdistpubisher is an undocumented parm, but if you look at the code, it allows things to be forced.
February 11, 2016 at 2:45 pm
wayne.fairless (12/23/2014)
We often run into this in our QA environments. The below script is a blunt hammer approach to remove replication, no matter how fubared your server is:USE <yourreplicateddb>
EXEC sp_removedbreplication '<yourreplicateddb>';
go
USE master
EXEC sp_dropdistpublisher @@servername, 1, 1;
EXEC sp_dropdistributiondb 'distribution';
EXEC sp_dropdistributor;
Note that the third parameter for sp_dropdistpubisher is an undocumented parm, but if you look at the code, it allows things to be forced.
Good Answer.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply