Script to remove a constraint and drop columns


Recently there was a need to get rid of a replication column after replication was disabled on a SQL Server 2000 database. The customer wanted to get rid of a column for all the tables which replication had added. And the flip side was that this column had a constraint defined on it. I looked at the constraint names and found that they had a common sub-string “msrepl_tran”. So by joining the system catalogs syscontraints and syscolumns, we were able to create the necessary T-SQL scripts to drop the constraint and the column from the tables involved.

select 'ALTER TABLE [' + OBJECT_NAME(a.id) + '] drop constraint [' + OBJECT_NAME(a.constid) + ']' + CHAR(13) +

'alter table [' + OBJECT_NAME(a.id) + '] drop column ['+ b.name + ']' + CHAR(13) + 'go'

from sysconstraints a

inner join syscolumns b on a.id = b.id

where OBJECT_NAME(a.id) like '%msrepl_tran%'

NOTE: Use CTRL+T and F5 to generate the script above. Inspect the output to ensure that no unintended table gets affected.

Advertisements

Replication Agent has not logged a message in 10 minutes


I saw multiple posts on MSDN regarding the following message: 

“The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity.” 

More often than not the above message can be ignored safely. The next obvious question is when can this message be ignored. If you find that your replication agent history is not reporting any error messages, then the above message is benign. Typically, this message means the agent is busy doing its work and has no resources to respond to status inquiries. 

Why does this error message get generated? 

This error message gets generated because of the Distribution heartbeat interval property. This property governs how long an agent can run without logging a progress message. If your replication agents are not reporting an error message and you are seeing the above message, then you could change your heartbeat interval to a higher value. One of the option could be that you changed the history logging option for your replication agent so that it doesn’t log any message. 

Query: 

exec sp_changedistributor_property @property = 'heartbeat_interval', @value = <value in minutes>;

 

Reference: sp_changedistributor_property (Transact-SQL) 

I have read lots of opinions as to why this is a bug and if you subscribe to that school of thought, please create a Connect item at http://connect.microsoft.com/sqlserver.

Monitoring Merge Agent performance using T-SQL


I have always loved writing T-SQL scripts and am ever-ready to write a T-SQL script when the opportunity presents itself. I just wrote another one for monitoring Merge Agent performance and spewing out some legible English to track the slow performing Merge Agents in your Replication Topology.

Read about it on:

http://blogs.msdn.com/repltalk/archive/2010/03/16/tracking-merge-agent-performance-using-distribution-database-system-tables.aspx

The script can be downloaded from either the above blog post or from the Script Center Gallery:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/20467f5a-99f1-4313-9f12-6ac24346da14