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

Top 5 Contributors of the Month
Post New Web Links

Commands Stacking Up in Distribution Database According to Monitor

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server


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.


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.  

SQL Database monitor : How?


My client software is ASP.net 2.0 is on a SHARED sql server hoster provider. I use ADO to create a connection to MSSQL database to run many TSQL stored procedures. I am using aspnetdb database for members management.

How can I
1) Monitor or count the number of members logged onto the SQL database.
2) Monitor or count the number of ado connections to SQL database.
3) Monitor or count database statistics etc.

My hoster let me use the Plesky software to monitor and use database, but its slow

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?

Dropped Distribution database



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?





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 ?

Database column in Activity Monitor is Blank


When we remotely manage a SQL 2008 instance the Database column in Activity Monitor is Blank, but when connected directly it shows up. We do have one user where the database column dose populate but he cannot remember if he did something to fix/enable it or not. These users are logging into this SQL server to get this info instead of managing remotely which I would like them to stop doing but I need to resolve this before they can since this info is critical.


I cannot imagine it is permission related since the column is blank for me as well and I have sysadmin rights over the instance.

Lossless Audio Addict

generic use of database connections, commands, datareaders


I have an app that is reading and writing from databases that are structurally identical but are from different systems, primarily SQL Server and Access 2010.  When using the respective .Net objects such as connections, commands, datareaders, the code seems to be often the same (except for transactions - Access doesn't do transactions?).  But the objects are different - sqlconnection versus oledbconnection, etc.

Is there a standard way of writing data access code that works regardless of the database type?

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.


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.

ASP.Net Gridview Edit Update Cancel Commands

In ASP.Net 2.0, GridView Control also provides the functionality to edit and update the data retrieved from the database using CommandField template. You can cancel the action using Cancel Command of the CommandField. GridView consists of events that can be used to perform the actions like edit, update and cancel upon the Data items displayed in the ASP.Net GridView Data Control.

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.
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