MSSQLWIKI

Karthick P.K on SQL Server

Archive for September, 2010

How to rebuild index and update statistics for all the tables in database.

Posted by Karthick P.K on September 26, 2010

 
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'   --  {can be run anytime}

Exec sp_MSforeachtable "dbcc dbreindex('?')"      --- {Always run this on a off-peak hour on any SQL Server instance}
 



Thanks


Karthick

Posted in Optimizer, Performance, SQL General, SQL Query | Tagged: , , , , | 3 Comments »

I get error while I update a row in immediate updating subscription what is the cause?

Posted by Karthick P.K on September 15, 2010

Error:

{

Another user has modified the contents of this table or view; the database row you
are modifying no longer exists in the database.
Database error: ‘[Microsoft][ODBC SQL Server Driver][SQL Server]Updatable
Subscriptions: Rows do not match between Publisher and Subscriber. Run the
Distribution Agent to refresh rows at the Subscriber.
[Microsoft][ODBC SQL Server Driver][SQL Server]Updatable Subscriptions: Rolling
back transaction.’
}

 

In Transactional Replication with immediate updating subscription

Update to a row in subscriber which is already updated in publisher is not
possible.
When we update the row in subscriber it compares the value of msrepl_tran_version
in Publisher and subscriber. If it matches then update is allowed.
If msrepl_tran_version in Publisher and subscriber doesn’t match Update is not
possible for that row in subscriber.

Posted in Replication | Tagged: , , , | 4 Comments »

 
%d bloggers like this: