.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Dropped Distribution database

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server


Got a problem. I have a replication that was created and then deleted. Not properly!

I believe subscriptions were deleted, then Agent jobs, then most importantly *Distribution db was dropped*. Replication was set up on a test copy of db. There is nothing going on in the db. So growing log is not an issue.

Problem is that when I right click on Replication option in SQL Server, "Configure Distribution" option doesn't even come up. So, I can't set up a new replication on this server.


What are my options? Is there a way to delete this replication manually?





View Complete Post

More Related Resource Links

How to attach distribution database files

Hi  i am new to sql server. when i am exploring replication concepts, i did a small mistake i.e. i have deleted distribution database.after that i realised and try to create a new one but it still points to the old database named as "distribution" which is not available. i didnt take the backup of the database before dropping. But i have mdf and ldf files of the distribution database. so can i use the sp_attach method to add the  mdf and ldf files? or any other way to fix this issue? please help me on resolving this issue Thanks, Rock  

Question on Distribution Database ** HELP ! **

Hi, I am new to SQL2K8 replication.  I have been reading lots of articles/tutorials on this subject.  I was able to setup a Transactional Replication.  Everything is working as expected.   However, I wanted to further investigate as to what events are taking place behind the scene just to educate myself a bit more.  I found that in the process of creating my transactional replication, it created the "Distribution" database under the "System Database" node on my SQL 2008 box.  There is only one table called "UIProperties" with a column called ID.  My question is where is the Log Reader keeping the changes in my publications.  I can see when I made a change to a row in my publication, it got replicated to the subscription database.  But where is SQL Server storing these changes??  I guess when the distribution agents runs, where does it get the changes from?  From my reading, the Distribution database is supposed to keep tracking of these changes.  But I am finding any changes I made to my publication.  Can someone educate me on this? Thank you.  

Commands Stacking Up in Distribution Database According to Monitor



SQL Server 2005 SP2.  Push Replication.  One publication.  One subscription.  Distribution on publisher.

The Replication Monitor shows 800k commands waiting to be delivered in the distribution database.  The subscriber database is up to date.  This has been ticking up since I reinitialized the subscription two days ago.

The following query:

undelivered = sum(UndelivCmdsInDistDB)
	MSDistribution_status s with (nolock)
join [db].dbo.sysarticles a 
on s.article_id = a.artid
group by s.agent_id

agent_id	undelivered
1	1218876
2	1218876
3	91

Agent_id 1 and 2 are virtual.

Are these two pieces of info related?  I am concerned that the distribution database will grow.  I am trying to figure out replication, but it has been tough.  Any help is appreciated.


Missing column in MSlogreader_history table in the distribution database

We recently built a new two node cluster on a Windows 2008 R2 64bit OS. We installed SQL 2005 64bit Enterprise with SP3 and CU7. We want to run replication from node 1 (1 instance - publisher) to node 2 ( 2 instances - distributor instance and a subscriber instance). We use scripts to build transactional replication (push). These scripts have been used before in an identical setup except it is CU3 instead of CU7. On our new setup, with CU7, the MSlogreader_history table in the distribution databse is missing the @updateable_row column. We only have 15 columns in this system table on our new CU7 setup. The older CU3 setup has 16 columns in the MSlogreader_history table including the updateable_row column.

Replication monitor shows an error stating that the sp_MSadd_logreader has too many arguments specified (Code 1007).

Why is our MSlogreader_history table missing the updateable_row column, and how can this be fixed?

Selecting the correct database drive for distribution DB and picking out master DB drive


I would like to know if its good practice to put the distribution DB files under same drive as that of the master, msdb, tempdb files or have it in the user database drive, both configs are RAID 10 but on seperate physical LUNS.

If it should be on same drive as the master database, my replication is setup dynamically and I was wondering how I can get the folder where the master DB is located in, I know I can query sysaltfiles for this, but it needs to be the folder where the master DB file is located.

Any ideas ?

The database principal owns a service in the database, and cannot be dropped.


I try to drop a user from the database, but the system said the user own some service and cannot drop it. I have deleted all schema of the user(but it seems not the case ____ the error message said it own some 'services' not 'schema'). And I have try to search what service it own by the command and it shows nothing.

select s.name as ServiceName
from    sys.database_principals p
        inner join sys.services s on p.principal_id = s.principal_id
where p.name = 'user'

How can I solve the problem?

Backup distribution database fails


 We recently did a windows update on our 2003 server. After the restart, one of our database backup jobs keep hanging. After a little troubleshooting i found that the job gets stuck on distribution database. i am able to backup all the user/system databases, but every time i try to backup distribution database, it just creates a 2kb file and keeps running. I have to manually kill the process. While the backup runs, there is no blocking as well.

The distribution databse is 300 MB and the backups worked fine before the restart.
I have peer to peer replication setup on the machine.

Any clues why i can't backup the database?
Any help would be appreciated.


Database Dropped - But no log entry


Environment: SQL Server 2008 SP1 Enterprise 64 bit

Server: Windows 2008 R2 64 bit

A database was dropped. There is no entry in SQL Server logs. I have looked at other related posts on this form regarding entries in sql log when a database is dropped.

Apart from creating user triggers to monitor the databases, I was wondering if there is any place where database being dropped can be caught.

Thank you.


Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

MS SQL Server: Disconnect Users From Database - Kill User Session

If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

Publishing SQL Server Database using Publishing Wizard : Tips & Tricks

We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.

Adding Rows in GridView without using a Database

This article describes on how to add rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in the TextBox on Button Click and retain the GridView data on post back.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

ASP.NET Database Tutorial

This tutorial will show you how to display data using the .NET GridView Control, ASP.NET 2.0 and VB.NET

The GridView control is a powerful tool and is simple to implement.

First, you will need to import the System.Data.SqlClient namespace.

Connestion string create any database server

This links important to how to create connection string to any data base sever.

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend