.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

Question on Distribution Database ** HELP ! **

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
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.  

View Complete Post

More Related Resource Links

database interface design question


For handy database interface, is it good to use SqldataSources exclusively intead of EntLib functions?

Thanks for any feedback.

Database CurrentUser Question

I am trying to check whether the user connected to a database is db_ownder so I am using the IsDbOwner property on the database object that was created from the Server object but it is setting the UserName to guest as opposed to the user connecting - any ideas? Thanks Scott

Detach/Attach database question

I recently restored a database from SQL7 to SQL2005.  When restoring the database I needed to create a c:\data\data folder or else it would not restore the mdf and ldf files. I would like to move these files to a new location.  Copied the the two files to a new folder, C:\database.  Using the sql studio manager, preformed a task to detach the database, then attach the database with the new location. This seemed to work, I am able to access my application that uses this database.  However, any changes I make to the program, are not writing changes back to the mdf file.  When I open the program, these changes are still in place, but the mdf file should be updated, correct?  When the database files were stored in the c:\data\data folder, any little change I made to the program changed the modified time for the mdf file. Is there something missing or where could these changes be written to?

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  

Sharepoint Database size question

We have just installed Sharepoint 2010 in our network.  We currently have 2 users who are accessing the system, since we are in alpha stage.  What I have noticed is that  User Profile Service Application_SyncDB database size is at 800mb.  is this normal? In addtion, WSS_Logging database size is at 140mb. 

about date in database question

hi i need to find data between 2 date's and time's. i use one field for date ,  and one field for time. **is it be better to use only one field for date & time ?** i see that it came in  `dd/mm/yyyy hh:mm:ss`  format that  can contain date and time. this question is for acceess and for sql-server thank's in advance

Newbie question about adventureworks database


Hi Guys,

I downloaded the Adventurworks2008 database and the AdventureworksDW2008 database for practice with SSIS.  I was wondering what the difference between the two databases is, because in the exercises im following it is stating that i will need to extract data from  the Adventureworks2008 database to the AdventureworksDW2008 database.

Is the the AdventureworksDW2008 an empty database and we are just extracting OLTP data to it? Please any help in clarifying this will be highly appreciated. Thanks!

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.


database diagram question

in sql server 2010, is it possible to create a database diagram where there are two tables connected showing a many-to-many relationship without a bridge table?

Worker & Location Database design question


Hello to forums :)


I have a simple question on design.

A company has several 'Worklocations'. Each location employs several workers. Every worker can be associated with multiple locations.

So, I have decided to create below tables. please letme know if there is a better way to design it

worker table
wid (primary)

location table
lid (primary)

workerlocations (WID+LID => primary key)

Is this sounds right for you guys? let me know

Question about SQL Server Database sizes


I have SQL Server 2008, when I go into Server Management Studio and I right click on my database and choose properities.  I see two things, I see Size of 200.13 MB and Space Available of .44MB.  Does this mean my database can only grow by .44MB more of data?  If so, is there a way to change the max size?  When I was looking at documents on SQL Server Express 2008, I saw a 10GB limit on each Database instance, so I was assuming SQL Server 2008 would provide me more than 200.5 MB? 

I'd really appreciate any info/clearing up for me.



Can anyone answer of my Question regarding SQL server database connectivity??

I want to connect my sql server database using my desktop application. Now Just tell me can i connect directly to my database using IP Adress in my connection string????? my database

Exist is in other country


Please clear this confusion.


Thank in advance


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 id question




Running this on an SQL 2005 server


This tsql statement returns 6

select DB_ID('erm') 

Also, this tsql statement returns 6

select database_id from sys.databases where name = 'erm'

But this one returns 5 rows all with a&n

Database Design Question


Hi everyone,

This is my scenario: I have 4 tables in my database at the moment. One of the tables contains all my users, each of which have a unique id(integer). One table contains the names and descriptions of certain lists of users so for example a row would be

table name: Lists


12    |     Example List     |    short description of the list


A third table contains the ID's of users that are in a given list:

table name: ListUsers


 12        |         213

 12        |         192

 12        |         84


My final table is filled with certain "events" I am holding that users on a list are invited to. (there are more fields in this table but they are not important).

table name: Events


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