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

Top 5 Contributors of the Month
Post New Web Links

On rebuilding indexes of MSmerge_Contents and other replication system tables

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :


In my previous question leads me to the index rebuild of MSmerge_Contents system table. However, I am still not sure what the explicit and correct way to rebuild index is. 

Refering to examples in BOL, the syntax should be something like the following:

ALTER INDEX ALL ON msmerge_contents

After checking system.indexes, I found that the fill_factor of 4 indexes of msmerge_contents are all 0. Does this mean I do not need to specify the value of FILLFACTOR? How about another 2 parameters, i.e. SORT_IN_TEMPDB and STATISTICS_NORECOMPUTE?

Do I need to specify ot

View Complete Post

More Related Resource Links

merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

SQL Server 2005 SP4 Install Fails - error log references replication system tables


The OS is Windows Server 2003 R2 SP2 (32 bit).  Trying to install SQL Server 2005 SP4 (current version is 9.00.4035).  All SQL products update without error, except for the Database engine.  The error listed in the summary.txt file in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix is:

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 5000
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log
Error Number              : 29537
Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Update or insert of view or function 'dbo.syspublications' failed because it contains a derived or constant field.. To continue, correct the problem, and then run SQL Serv

transactional replication and sp_MSupd tables

Hi thre, My target server keeps running sp_MSupd sp for different values in profiler and synch status shows "delivering replication commands" I keep waiting but this never ends. and ultimately we run a snapshot and it can't be applied. So recreate the whole replication again. It runs fine for a while but again, the sp_MSupd sp starts running on the target server and the replication goes down again. I don't know how to fix this. any advices?   Regards

Minimum Permissions Needed To See Cdc tables In System Tables Folder

What are the minimum permissions needed for a user to see the cdc tables in the System Tables folder in SQL Management Studio?DJ Baby Anne's Biggest Fan................

Unable to publish two tables for replication. They have a key with a red x.

I am trying to figure out why I can't access the DB that I've replicated.  Two tables have a key with a red x that makes it so I can't publish them for replication.  In addition, how do I replicate all the user roles and permissions with the database that is being replicated?  I am using SQL Server 2000.   More info:  I can't access the replicated DB through the client software.  It is accessible through SQL Management Console.  I had to manually export all users and roles and then run the query to get them into the replicated database.  Is there a more thorough way to replicate a database?  How can I make an exact copy on a different server so all information is intact?

clean up system tables like sysmergepublications , sysmergesubscriptions...

Hi ALL,            How to clean up system tables like sysmergepublications , sysmergesubscriptions... Bcoz  in my subscriber database i still see the previosous publihser details in these system tables Same thing in publisher database....how to remove all these data .....when i reinitialize database at subscriber ..and if i see the local subscriptions in ssms there are subscriptions from publishers to which it is subscribed before..and i cant delete this....

Error While Rebuilding system databases

Hi, I installed my SQL Server with a wrong collation. I tried to rebuild the instance and when i tried doing that I am getting a weird error as given below. ALso the installation is picking the edition as evaluation instead of enterprise, i tried executing the install file and it went through to install a enterprise edition. I am not sure why the installer is picking up the evaluation edition instead of enterprise as the option is set.  Exception summary: The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indented Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException     Message:          The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details. SugeshKumar SQL Server MVP http://sugeshkr.blogspot.com

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?


As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)


Tx Replication INDEXES


The performance on Publisher db is acceptable but the Subscriber db is very poor. Please advise how can I mitigate this issue? As we understand the evolving reporting needs we can later fine tune and tune the performance. For now,

Will setting the following article properties act as a quick solution?

copy clustered index: Default-true

copy non clustered indexes: true

copy unique key constraints: Default-true

copy user-defined statistics: true

copy full text indexes: true ( please provide explanation)

Can't replicate few tables in Snapshot replication SQL 2005 in two different domain


I am not able to replicate few tables of a database using snapshot replication.Both publisher and subscriber are in different network and domain.Although I am able to replicate 950 tables but facing problem in some 30 tables.I am using push replication and I can telnet 1433 port from A server to B server but vice versa not happening.When I am replicating few records of that few tables it is replicating properly but not able to replicate complete table. I tried Verbose history too but I didn't get complete error.

Please help me out.....

Rebuilding indexes




Is it good to do index rebulid with online  option when production is going on. Is there any impact?

Kindly let me know if there is any impact.

We are using Sql server 2005 standard edittion.

Thanks in advance....

avoid accidental delete on Subscriber tables - transactional replication


Hello pundits..

I am looking to see if we have any way to set on the subcsriber to avoid accidental deletes on subscriber tables to avoid the 20598 error(where the publisher is trying to replicate a record not existing(rather deleted in accident) at the subscriber end). This is for transactional replication


Indexes on Partitioned Tables


I have a table that is used for staging.  It has a unique clustered index on it that has the same partitioning as the table.  I want to know if there is a way for me to add a non-clustered, non-partitioned index to the table and still be able to do the partition swapping.  Are there any work arounds?  I have found that because the indexes are 'not-aligned' the swapping fails to insert into the table with the multiple indexes.


Replication and Remedy AR system


Hi All,

I wanted to get some feedback on replicating an AR system database. Does anyone have any advise or a list of DOs and Don'ts. I am looking at doing Transactional replication via a WAN


Getting server non system databases, getting database tables, getting table columns


I use next code to get server databases

string connectionString = "Data Source=DENIS\\SQLEXPRESS;Integrated Security=SSPI;";
DataTable tables = new DataTable("Tables");
using (SqlConnection connection =
    new SqlConnection(connectionString))
  SqlCommand command = connection.CreateCommand();
  command.CommandText = "sp_databases"; 
  command.CommandType = CommandType.StoredProcedure;


this.dataGridView1.DataSource = tables;

I've got many databases :

- model ( i guess it is system )

- master ( i guess it is system )

- tempdb ( i guess it is system )

- db1 ( my table )

and etc.

How to determinate what table is system.



FROM information_schema.Tables

So how to determinate what table is system or not.

Can I get tables of database

slow query, proper db setup, indexes, tables

I have a large set (~10 million) of time series data (date, time, and value) stored in text files.   These dates and times may repeat.   I would like to move these to a database (or several databases) and access them from a program.  I have sort of done this, the problem is that retrieving the data takes too long (say 30s).  I'd like to reduce the time it takes to access the data.

The steps I've done are:

1) Create a new database using SSMS.
2) Read in all of the year 2010 data into a new table
3) Accessed the data with the LINQ query from c#:
var timeSeriesData =
                from point in context.2010s
                where (new DateTime(point.Date.Year, point.Date.Month, point.Date.Day, point.Time.Hour, point.Time.Minute, point.Time.Second, point.Time.Millisecond)).CompareTo(_MinimumDateTime) >= 0
                    && (new DateTime(point.Date.Year, point.Date.Month, point.Date.Day, point.Time.Hour, point.Time.Minute, point.Tim

SSIS : Pulling large number of tables from Source system



we are migrating a datawarehouse application from asp - sql 2000 to dotnet2.0 - sql 2005
currently we are in the process of coming up with an optimum solution for backend design using sql server 2005.
This application pulls data from 3 different source systems (oracle,sql server and mainframe db2).

we have 3 different staging databases corresponding to each of the source application database.
For eg : RetailGarments application is an oltp application with backend oracle database .
we have a staging SQL server database called Retailgarments_stg for our datawarehouse application.
similarly OnlineTravelBooking is an OLTP application with DB2 database .we have corresponding
OnlineTravelBooking_Staging database for staging this data.

Each of these source systems have more than 100 tables and we are currently pulling most of the
tables to our side from the source .

These staging data from different datasources are accumulated in to a cleansed Schema database
which is used for Reporting and other OLAP requirements.

Our question is related to data pull from the source system.
Current SQL Server database pulls these data from sorce system using Stored proc dynamic queries containing
link server openquery.

We would like to improve the performance as part of sql server 2000 to

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