January 30, 2009 at 9:22 am
This is my test server for SQL 2005. When it was created, the system databases were restored to this server. Unbeknownst to me, one of the databases on the production server was set up for replication. What I have now is a distribution database, that appears under 'System Databases', but it doesn't really exist on this server.
I can't view its properties. When I try I receive an error:
Database 'distribution' cannot be opened due to inaccessible files or insufficient meoroy or disk space. SE the SQL SErver errorlog for details. (Microsoft SQL Server, Error: 945).
No suprise since the files doen't exist.
Its not the end of the world if I need to remove SQL 2005 and start over, but I don't want to if I don't have to. How can I get rid of, or fix my distribution database?
January 30, 2009 at 9:25 am
That really depends on how critical your replication configuration was as this is the only thing that is going to be impacted by the missing distribution database. If it wasn't important and you can recreate your replication setup then just disable publishing on that instance and most likely the reference to the distribution database will go away (hopefully).
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 30, 2009 at 9:30 am
I've disabled replication and publishing. That allowed me to remove the database being replicated.
I'm now interested in fixing this because its generating errors when my backups run each night.
Incidentally, I have separate maintenance plans for backing up the system and user databases. The mutant distribution database affects the user database backup rather than the system database backup.
February 2, 2009 at 6:59 am
Are the errors you're getting related to publications or subscriptions failing? Can you post the errors here?
If you dont need replication working on your test machine, you could drop the distributor rather than leave it disabled. you'd end up getting rid of replication all together on the machine. (dropping subscriptions and publications as well).
see the commands sp_dropdistributor and sp_droppublication, (or sp_dropmergepublication depending on what merge model you're using)
Another option for you would be to script out all of the replication objects from your production environment (if you dont already have them), and re-build replication in your test environment. This is probably your best option, since it will allow you to have a test environment that emulates production.
Regards, Jim C
February 2, 2009 at 8:22 am
jtrask (1/30/2009)
I've disabled replication and publishing. That allowed me to remove the database being replicated.I'm now interested in fixing this because its generating errors when my backups run each night.
Incidentally, I have separate maintenance plans for backing up the system and user databases. The mutant distribution database affects the user database backup rather than the system database backup.
just detach it !
* Noel
February 3, 2009 at 8:34 pm
I'll check out those stored procedures. For now that might be my best option.
In the future, I would like my test server to be a duplicate of the production box. I'll look into that before I rebuild this box the next time.
Thanks.
February 3, 2009 at 8:47 pm
Well, my publication and subscriptions are gone. I had been able to delete them previously. I ran :
USE master
GO
EXEC sp_dropdistributor @no_checks = 1
(cut and pasted from Microsoft Article ID: 324401)
but I get the following:
Database 'distribution' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Incidentally, the is the same error I get if I attempt to look at the properties of the Distribution database in Management Studio
February 3, 2009 at 9:51 pm
I'm pretty sure that executing the following will take care of your situation. However, having not been in it I can't say for sure. 😀
The following are steps 6 and 7 from this MS BOL Website.
At the Distributor, execute sp_dropdistributiondb (Transact-SQL) to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
At the Distributor, execute sp_dropdistributor (Transact-SQL) to remove the Distributor designation from the server.
Note:
If all replication publishing and distribution objects are not dropped before executing sp_dropdistpublisher (Transact-SQL) and sp_dropdistributor (Transact-SQL), these procedures will return an error. To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. If a Publisher or Distributor is offline or unreachable, the @ignore_distributor parameter can be set to 1 so that they can be dropped; however, any publishing and distributing objects left behind must be removed manually.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 4, 2009 at 7:14 am
I think that because of my own ineptitude, my test server is now sufficiently scrambled enough that I should just start over.
In running the sp_dropdistributiondb and sp_dropdistributor procedures, I discovered that the publisher was not as completely removed as I originally thought.
I had to backup and perform steps 3 & 5 from the MS BOL Website (sp_removedbreplication and sp_dropdistpublisher respectively).
I had to throw in a little help from sp_helpdistributor, sp_helpdistributiondb, and sp_helpdistpublisher.
sp_dropdistpublisher doesn't work. I get the same error message I get when I try to do anything else with the distribution database: Database 'distribution' cannot be opened due to inaccessible files or insufficient memory or disk space.
Since this is a test server, and (as far as I know) no one is actively using it right now, I am going to uninstall SQL 2005 and use the opportunity to modify my disaster recovery process to include database replication.
February 4, 2009 at 8:12 am
Ok. Thanks for the update. I do know that you can typically clean up the old replication stuff but it can be a bit persnickety if it gets in an odd state. Either way, have fun!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 4, 2009 at 9:31 am
When I switched SQL Server Service accounts from Local System to a domain account I had the same problem. Switched it back to Local System and everything carried on as per usual.
June 4, 2009 at 9:52 am
This was because the domain account did not have access rights to the physical location of the database files - distribution happened to be on a different disk. Once this was sorted everything worked again.
June 4, 2009 at 10:26 am
Presumably you still have a the Distribution database available on Production and a backup of that database?
If so, just RESTORE the database using WITH MOVE to locate the .mdf and .ldf files correctly.
You will then be able to open the database or delete it if you don't need it.
If your Test server is basically a copy of your Production server it will still have the original servername, i.e the Production server name. You can check this by running SELECT @@SERVERNAME.
You'll have to remove replication from the Test server by following the steps that other posters have mentioned - remove replication objects from the Subscription database, disable the Publisher and the Distributor (if they are one and the same). Then drop any remote logins that reference the old server name: sp_helpremotelogin -- list remote logins and sp_dropremotelogin.
Now you'll be able to drop the original server: sp_dropserver [old servername] and finally add the new (Test) servername: sp_addserver [Test servername].
You'll likely have to restart the Test server instance before SELECT @@SERVERNAME will return the correct server name.
Regards
Lempster
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply