Displaying posts categorized under

SQL Server

Microsoft Database Technology.

Replication Issue – Duplication of Primary Key

Replication Issue – Duplication of Primary Key. Replication synchronizes data and article on subscriber same as it is on publisher. Changing and inserting data directly at subscriber cause to break replication. Please look the following error. Error Message Command attempted:   if @@trancount > 0 rollback tran (Transaction sequence number: 0x00000031000001B1000500000000, Command ID: 1) Error messages: [...]

Add article in Replication without initializing

Add article in Replication without initializing By this way you are only creating a snapshot only for newly added article and no need to reinitialize  all subscription. Step1 : Create New Table on Publisher. Step2 : Add this table in Article. First check Article exist or not in a replication. Run this to add “ReplicationTest” [...]

Script to Monitor Mirroring

Script to Monitor Mirroring This script will provide all database which is mirrored with mirroring mode, mirroring status and partner information. Regards, Nirav Gajjar

Script to get Replication Latency

Script to get Replication Latency. I have created monitoring script to get latency information for replication and status of replication health. I have used “SP_REPLMONITORHELPSUBSCRIPTION ” under Distribution database. It gives you same information which you can get from Replication Monitor –> Subscription Wath List. In SQL Server 2012 you just need to pass value only [...]

Script to get replication Undistributed Commands

Script to get replication Undistributed Commands Script to Monitor Replication by checking last refresh time and pending commands Regards, Nirav Gajjar

Query to get Database size and table size

Query to get Database size and table size. Query to get Database size in GB. Query to get Table size in GB with no of rows. Query to get Service Broker Queue size in GB with no of rows. Regards, Nirav Gajjar

Query to get Open Transaction

Query to get Open Transaction. Query to monitor open transactions in your sql server instance. If you are using SQL Server 2012 use open_transaction_count column instead of enlist_count. Query to get open transactions by host. remove the comment if you want open transactions just from ssms window. List Open transactions in details. Regards, Nirav Gajjar

SQL Server long running and blocking queries

SQL Server long running and blocking queries This query helps you to get all running queries including user queries and SQL Server engine queries. with duration, so you can examine which query running long. You can see BlockingWith field which shows you query is blocking with another process. I have ordered query by blocking first [...]

Deploy SSIS Package in SQL Server 2012

Deploy SSIS Package in SQL Server 2012. SQL Server 2012 added new feature of Integration Service Catalog, which makes SSIS package deployment task very easy. To get advantage of this feature you must have sql server integration service installed on your server. Follow the steps to deploy package. Step1 : Create Integration Service catalog. Step2 [...]

XML Data in where clause

XML Data in where clause XML Data in Document Types. Step1 : Create Table XMLData. Step2 : Insert XML Document Types Data. Step3 : Where clause for XML Data Type. XML Data in Element Types. Step1 : Truncate XML Data table and insert some element type XML data in the same table. Step2 : Where [...]