Monthly Archives: August 2013

Replication – Script – Dropping a table from an existing publication in SQL Server

Scripts, SQL Server Replication August 30, 2013 Leave a comment

In this article I will share the script that I use while dropping a table from an existing publication.
Below is the script that helps us achieve the same.

use [databasename]--Name of the DB in replication
go
exec sp_dropsubscription @publication=N'NameOfPublication',
@article=N'TestTable',--name of the table to be dropped
@subscriber=N'all'
go
use [databasename]
go
exec sp_droparticle @publication = N'NameOfPublication',
@article = N'TestTable',--name of the table to be dropped
@force_invalidate_snapshot = 1
go

Technical reference:
The details for the stored procedure sp_dropsubscription can be found in the below link
http://technet.microsoft.com/en-us/library/ms184385.aspx

Advertisement

Replication – Script – How to add a table to an existing publication in SQL Server

Scripts, SQL Server Replication August 30, 2013 Leave a comment

In this article i will share the script that I use when I add a table to an existing publication in SQL Server.
The below script would help us to add any table to an existing publication. The most important thing about adding a table to replication is that the table must have a primary key.

Below is the TSQL script to achieve the same.

use [databasename]--Name of the table involved in Replication
Go
exec sp_addarticle @publication = N'NameOfPublication',
@article = N'TestTable', @source_owner = N'dbo',
@source_object = N'TestTable', 
@type = N'logbased',@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDB,
@force_invalidate_snapshot = 1,
@identityrangemanagementoption = N'manual',
@destination_table = N'TestTable',
@destination_owner = N'dbo', @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboTestTable',
@del_cmd = N'CALL sp_MSdel_dboTestTable',
@upd_cmd = N'SCALL sp_MSupd_dboTestTable'
Go

More details on the used stored procedure [sp_addarticle] can be found on the below mentioned technet article

http://technet.microsoft.com/en-us/library/ms173857.aspx

Mathematics behind Query Recompilation

Plan cache August 17, 2013 Leave a comment

We DBA’s have all experienced query recompilation in our SQL Server environment. The entire journey of a query, right from inception till the formation of the Query Plan followed by execution is very interesting and involves a lot of internal processes. Recompilation is nothing but the re-initiation of compilation of a query when a certain compilation check fails. So what is the underlying algorithm or mathematics that the SQL server uses to decide whether to recompile or not. Here is how it works:

ABS( colmodctr(current) – colmodctr(snapshot)) ) >= RT

1) RT = Each column has a recompilation threshold (RT) associated with it. RT is a function of the number of rows in a table. We will discuss how RT is calculated later in this article.

2) colmodctr = This counter tracks the number of modifications that a table has undergone. The counter is not transactional in nature, which means if 1000 rows were inserted into the table and then rolled back then the counter value would not be rolled back.

3) colmodctr(current) is the current value of the counter and colmodctr(snapshot) is the value of the counter when the query plan was last compiled.

So when this absolute value crosses the RT, it is then that the query is recompiled. The next quetion is how does the engine decides or calculates the RT. Here is how it is done:

1) For permanent table:If n<=500 then RT = 500.If n>500 then RT = 500 + 0.20 * n.

2) For temporary table: If n<6 then RT = 6.If n between 6 and 500 then RT = 500. If n>500 then RT = 500 + 0.20*n

3) For Table variable: RT does not exist. They are not recompiled.

Here n stands for numbers of rows in the table.

I hope this concise article was helpful in understanding the decision making process for Recompilation.

SQL Server Replication – Troubleshooting – LogReader Timeout executing sp_replcmds

SQL Server Replication August 15, 2013 Leave a comment

I came across this excellent article from Chris Skorlinski which talks about logreader timeout errors. All DBA’s who have managed a replicated environment must have encountered this error. This article talks about the Root-Cause of this issue and advises a resolution. Worth a read.

http://blogs.msdn.com/b/repltalk/archive/2010/10/25/troubleshooting-logreader-timeout-executing-sp-replcmds.aspx

New DMV’s in SQL Server 2008 R2 onwards

Dynamic Management Views August 15, 2013 Leave a comment

I came across these 2 new DMV’s in SQL server 2012 and found them available in R2 also. The DMV’s are very helpful and insightful. Below is a brief about the 3 new DMV’s.

1) SELECT * FROM sys.dm_server_registry:- Use this dynamic management view in SQL Server 2008 R2 SP1 and later versions to return information such as the SQL Server services that are available on the host machine or network configuration values for the instance of SQL Server.
MSDN Link: http://msdn.microsoft.com/en-us/library/hh204561.aspx

Registry

2) SELECT * FROM sys.dm_server_services:-Use this dynamic management view to report status information about SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server.
MSDN Link: http://technet.microsoft.com/en-us/library/hh204542.aspx

Services

%d bloggers like this: