Validate and Create Windows Cluster

Validate and Create Windows Cluster

In this blog i am going to create windows cluster for my AlwaysOn High Availability Group configuration.

I have already enabled and installed failover cluster on all recommended  servers. FailOver Cluster

Step1 : Open Server Manger — Select “Failover Cluster Manager” under Features. Click on “Create Cluster” highlighted in red under Management panel. CreateCluster1

Step2 : This will open “Create Cluster Wizard”.

Read “Before you Begin” and click on “Next”. CreateCluster2

Step3 : Type your server name and click on add. Here i have added Server1, Server2 and Server3. CreateCluster3

Step4 : On the “Validation Warning” click on “Yes. When I Click Next, run configuration validation tests, and then return to the process of creating the cluster” this option will validates the clustering processes available on all server. CreateCluster4

Step5 : This will open “Validation Configuration Wizard” click on Select “Run all tests (recommended)” to check all type of test. CreateCluster6

Step6 : Confirm testing and click on “Next”.

CreateCluster7

Step7 : See the list of test validating.

CreateCluster8

Step8: Confirm testing and click on “Finish”.

CreateCluster9

Step9 : After completion of validating it will come back to “Create Cluster Wizard”.

Now give a name to your cluster. Here i am configuring cluster for availability group so i have created “AGTesting”.

Give any unassigned IP Address from your domain and click on “Next”.

CreateCluster10

Step10 : This window show you the details of your clustering like ClusterName, List of Failover servers and Cluster IP Address.

CreateCluster11

Step11 : See message “You have successfully completed create cluster wizard”.

If you want to see a creation report in detail then click on “View Report…” else click on finish to exit the wizard.

CreateCluster12

Step12 : Now check your cluster environment under ServerManager.

Here you can add or remove cluster node, configure service, configure applications etc…

CreateCluster13

Thanks & Regards,

Nirav Gajjar

Create tables schema to another database using SMO

Create tables schema to another database using SMO.

I have created a package which will create table script to your physical path and also executes same thing on another database to create table schema.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Collector;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Collections.Generic;
#endregion

namespace ST_ab378c7f30204c94a2f49a2b0f7ac500
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            try
            {
                string TableName;
                TableName = (Dts.Variables["User::TableName"].Value.ToString());
                Server server = new Server();
                Database source_database = new Database();
                Database destination_database = new Database();
                server.ConnectionContext.LoginSecure = false;
                server.ConnectionContext.Login = "sa";
                server.ConnectionContext.Password = "reliancesp";
                server.ConnectionContext.ServerInstance = "User8\\SQL";
                source_database = server.Databases["Adventureworks2012"];
                destination_database = server.Databases["ReceiverDB"];

                Table table = source_database.Tables[TableName];
                string tables = table.Name.ToString();

                //Define properties in scriptingOptions
                ScriptingOptions scriptingOptions = new ScriptingOptions();
                scriptingOptions.ClusteredIndexes = true;
                scriptingOptions.NonClusteredIndexes = true;
                scriptingOptions.Default = true;
                scriptingOptions.DriAll = true;
                scriptingOptions.Indexes = true;
                scriptingOptions.IncludeHeaders = true;
                scriptingOptions.ScriptSchema = true;
                scriptingOptions.ScriptDrops = false;
                scriptingOptions.Triggers = true;

                StringCollection result = table.Script(scriptingOptions);

                var script = "";
                foreach (var line in result)
                {
                    script += line + "\r\n";
                }

                System.IO.StreamWriter fs = System.IO.File.CreateText(@"D:\data\" + TableName + ".sql");
                fs.Write(script);
                fs.Close();
                destination_database.ExecuteNonQuery(script);
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
		}

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Script to Shrink Database

Script to Shrink Database.

You need to use Database Console Commands of SQL Server to get back the free space.

DBCC ShrinkDatabase : Shrink all files from database.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, 10)
Go

It will shrink 90% free space and remains 10% free for database files.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, TruncateOnly)
Go

It will shrink data and log files to the last allocated extent, while you use TruncateOnly the percentage parameter will be ignored.

DBCC ShrinkFile : Shrink only single file.

You have a choice to which data file or log file you want to shrink.

–> “AdventureWorks_MDF” and “AdventureWorks_LDF” is a logical name of data files and log files.


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_MDF,10)
Go


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_LDF,10)
Go

Regards,
Nirav Gajjar

SQL Server Mirroring with Certificate

SQL Server Mirroring with Certificate.

Step1 : Take a backup of Database and then backup of log as well, of the database which you want mirrored.
On Principal Server


USE [master]
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.bak'
BACKUP LOG [AdventureWorks2012] TO DISK = N'D:\SQL\AdventureWorks2012.TRN'

Step2 : Copy this both database and log backup from principal server to mirroring server and restore it in specific drive.

On Mirroring Server


USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.bak' WITH  FILE = 1,  NORECOVERY

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'D:\SQL\AdventureWorks2012.TRN' WITH  FILE = 1,  NORECOVERY

Step3 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the principal server. Copy certificate backup to mirroring server.

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO

Step4 : Create Master Key, Certificate, Endpoint for Mirroring and backup of certificate on the mirroring server. Copy certificate backup to principal server.

On Mirrored Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

USE master;
CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO

On Principal Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Mirroring_Server_Certificate
   WITH SUBJECT = 'Mirroring Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Mirroring_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Mirroring_Server_Certificate TO FILE = 'D:\Mirroring_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

CREATE CERTIFICATE Principal_Server_Certificate
   WITH SUBJECT = 'Principal Server Mirroring';
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE Principal_Server_Certificate
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO

BACKUP CERTIFICATE Principal_Server_Certificate TO FILE = 'D:\Principal_Server_Certificate.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mir];
GO

On Mirroring Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://PrincipalServerName.DomainName:7024';
GO

On Principal Server

ALTER DATABASE AdventureWorks2012
    SET PARTNER = 'TCP://MirringServer.DomainName:7024';
GO

To Failover Partner

ALTER DATABASE AdventureWorks2012
    SET PARTNER FAILOVER;

To Stop the mirroring.

--Run below script on Principal server
ALTER DATABASE AdventureWorks2012
    SET PARTNER OFF;
--Run below script on Mirroring server
Restore Database AdventureWorks2012 With NoRecovery

Notes:
1) Windows user must have an access to both server sharing folder.
2) Port number 7024 must me exceptional port number in firewall.

Enable Windows Server Failover Clustering (WSFC)

Enable Windows Server Failover Clustering (WSFC).

Step1 :  On the server manager window click on “Add Features”.

WSFC1

Step2 : On the Add Feature Wizard select “Failover Clusterintg” and click on Next.WSFC2

Step3 : Confirm and click on Install.

WSFC3

Step4 : Check installed feature and click “Close” button to exit.

WSFC4

Thanks & Regards,

Nirav Gajjar

Identity reseed in SQL Server 2012

Identity reseed in SQL Server 2012

In SQL Server 2012, there is one issue regarding Identity column.

I have faced identity value is jumping by 1000 when i have restarted my SQL Server Service.

You can find gaps in identity while you restart your sql server service, restart of server or failover.

Please review below code to try this.

USE ADVENTUREWORKS2012
GO
CREATE TABLE TestIdentJump
    (
         Id   INT IDENTITY (1, 1),
         Name VARCHAR(20)
    )
INSERT INTO TestIdentJump VALUES     ('testing1')
GO 5

SELECT *
FROM   TestIdentJump

IdentJump

Now restart your sql server service and run below code again and see the result.

INSERT INTO TestIdentJump VALUES     ('AfterRestart')
SELECT *
FROM   TestIdentJump

IdentJump1

Have you seen SQL server has started Identity column from 1002 instead of 6.

To resolve the problem there is two options

1) Run the checkpoint before you restart the sql server and check again.

This technique will solve the problem of jump in identity.

This technique has limitations, this will helps you out only in manual failover.

2) We need to open one trace flag to avoid this issue when server crisis and we don’t have a chance to run checkpoint.

Need to Open trace flag -t272

Right click on sql server service and click on property.

Select “Startup Parameters” tab.

Specify -t272 in textbox an click on add button and restart the service.

IdentJump3

Note : You need to this activity on principle server as well as mirror server.

Thanks & Regards,

Nirav Gajjar

Process Element XML and Attribute XML in single node

Process Element XML and Attribute XML in single node.


DECLARE @T VARCHAR(400)
SET @T = '<Log DeviceId="MainDevice001">
  <Info UId="100000" Date="05/05/2014">
    <Err>Error1</Err>
  </Info>
  <Info UId="100001" Date="05/05/2014">
    <Err>Error2</Err>
  </Info>
</Log>'
DECLARE @Errorlogsxml AS XML
SET @Errorlogsxml = Cast(@T AS XML)
SELECT TAB1.COL1.value('@UId', 'VARCHAR(100)') AS Uid,
       X.Deviceid AS Deviceid,
       TAB1.COL1.value('@Date', 'DATETIME') AS Date,
       TAB1.COL1.value('(Err)[1]', 'VARCHAR(100)') AS Error
FROM   (SELECT TAB.COL.value('@DeviceId', 'VARCHAR(100)') AS Deviceid
        FROM   @Errorlogsxml.nodes('/Log') AS Tab(COL)) X
       CROSS APPLY @Errorlogsxml.nodes('/Log/Info') AS Tab1(COL1)

Modify database name with single user mode

Modify database name with single user mode

USE MASTER;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012 MODIFY NAME = AdventureWorks
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

Configure Service Broker in SQL Server 2012

Configure Service Broker in SQL Server 2012.
Initiator Script

Step1 : Create Database, Master Key and Initiator Certificate

USE MASTER
GO
CREATE DATABASE InitiatorDB
GO
ALTER AUTHORIZATION ON DATABASE::InitiatorDB TO SA
Go
ALTER DATABASE InitiatorDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
Go
ALTER DATABASE InitiatorDB SET TRUSTWORTHY ON
GO

--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE InitiatorBrokerCertificate
	WITH SUBJECT = 'broker.InitiatorCertificate',
	START_DATE = '2012-12-01',
	EXPIRY_DATE = '2099-12-31'
	ACTIVE FOR BEGIN_DIALOG = ON;
GO
--Take Backup of Certificate
BACKUP CERTIFICATE InitiatorBrokerCertificate
	TO FILE = 'D:\Certificates\InitiatorBrokerCertificate.cer';
GO

Step2 : Create Target Certificate, Login and Endpoint

--Create Target Certificate from backup
CREATE CERTIFICATE TargetBrokerCertificate
	FROM FILE = 'D:\Certificates\TargetBrokerCertificate.cer';
GO

CREATE LOGIN [TargetBrokerLogin]
	FROM CERTIFICATE TargetBrokerCertificate
GO

CREATE ENDPOINT ServiceBrokerEndPoint
	STATE = STARTED AS TCP (LISTENER_PORT = 4022)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InitiatorBrokerCertificate,
	ENCRYPTION = SUPPORTED);
GO

GRANT CONNECT ON ENDPOINT :: ServiceBrokerEndPoint TO [TargetBrokerLogin]
GO

Step3 : Create User, Master Key, Initiator and Target Certificate

USE [InitiatorDB]
GO
--Create Broker User to Access Target Service
CREATE USER [TargetBrokerUser] WITHOUT LOGIN
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE Certificate InitiatorUserCertificate
 WITH Subject = 'user.InitiatorCertificate',
    START_DATE = '2012-12-01',
    EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON
GO

BACKUP CERTIFICATE InitiatorUserCertificate
TO FILE = 'D:\Certificates\InitiatorUserCertificate.cer'
GO
--Create Target Certificate From Backup
CREATE Certificate TargetUserCertificate
	AUTHORIZATION TargetBrokerUser
 FROM FILE = 'D:\Certificates\TargetUserCertificate.cer';
GO

GRANT CONNECT TO [TargetBrokerUser]

Step4 : Create Message Type, Contract, Queue, Service, Route and Binding

CREATE MESSAGE TYPE [MSG://Sample/Request] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [MSG://Sample/Response] VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [CON://Sample/Contract] ([MSG://Sample/Request] SENT BY INITIATOR,
[MSG://Sample/Response] SENT BY TARGET)
GO

CREATE QUEUE [dbo].[InitiatorQueue] WITH STATUS = ON , RETENTION = OFF ,
POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]
GO

CREATE SERVICE [Service://Sample/Initiator]
	ON QUEUE [dbo].[InitiatorQueue] ([CON://Sample/Contract])
GO
GRANT SEND ON SERVICE::[Service://Sample/Initiator] To [TargetBrokerUser]

CREATE ROUTE [TargetRequestRoute]
	WITH  SERVICE_NAME  = N'Service://Sample/Target' ,
	BROKER_INSTANCE  = N'106775EA-80AC-48ED-AEFB-92AE1F9A0736' ,  --Broker Instance of Target Databases
	ADDRESS  = N'TCP://User9.excellence.local:4022'
GO
CREATE REMOTE SERVICE BINDING [InitiatorRemoteServiceBinding]
	TO SERVICE N'Service://Sample/Target'
	WITH USER = [TargetBrokerUser] ,  ANONYMOUS = OFF
GO

Target Script

Step1 : Create Database, Master Key and Target Certificate

USE MASTER
GO
CREATE DATABASE TargetDB
GO
ALTER DATABASE TargetDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TargetDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TargetDB SET TRUSTWORTHY ON
GO

--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE TargetBrokerCertificate
	WITH SUBJECT = 'broker.TargetCertificate',
	START_DATE = '2012-12-01',
	EXPIRY_DATE = '2099-12-31'
	ACTIVE FOR BEGIN_DIALOG = ON;
GO
--Take Backup of Certificate
BACKUP CERTIFICATE TargetBrokerCertificate
	TO FILE = 'D:\Certificates\TargetBrokerCertificate.cer';
GO

Step2 : Create Initiator Certificate, Login and Endpoint

--Create Target Certificate from backup
CREATE CERTIFICATE InitiatorBrokerCertificate
	FROM FILE = 'D:\Certificates\InitiatorBrokerCertificate.cer';
GO

CREATE LOGIN [InitiatorBrokerLogin]
	FROM CERTIFICATE InitiatorBrokerCertificate
GO

CREATE ENDPOINT ServiceBrokerEndPoint
	STATE = STARTED AS TCP (LISTENER_PORT = 4022)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TargetBrokerCertificate,
	ENCRYPTION = SUPPORTED);
GO

GRANT CONNECT ON ENDPOINT :: ServiceBrokerEndPoint TO [InitiatorBrokerLogin]
GO

Step3 : Create User, Master Key, Initiator and Target Certificate


USE [TargetDB]
GO
--Create Broker User to Access Target Service
CREATE USER [InitiatorBrokerUser] WITHOUT LOGIN
GO
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@1234'
GO
--Create Certificate
CREATE CERTIFICATE TargetUserCertificate
 WITH SUBJECT = 'user.TargetCertificate',
    START_DATE = '2012-12-01',
    EXPIRY_DATE = '2099-12-31'
ACTIVE FOR BEGIN_DIALOG = ON
GO

Backup Certificate TargetUserCertificate
TO File = 'd:\certificates\TargetUserCertificate.cer'
GO
--Create Target Certificate From Backup
CREATE Certificate InitiatorUserCertificate
	AUTHORIZATION InitiatorBrokerUser
 FROM FILE = 'd:\certificates\InitiatorUserCertificate.cer';
GO

GRANT CONNECT TO [InitiatorBrokerUser]

Step4 : Create Message Type, Contract, Queue, Service, Route and Binding

CREATE MESSAGE TYPE [MSG://Sample/Request] VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [MSG://Sample/Response] VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [CON://Sample/Contract] ([MSG://Sample/Request] SENT BY INITIATOR,
[MSG://Sample/Response] SENT BY TARGET)
GO

CREATE QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF ,
POISON_MESSAGE_HANDLING (STATUS = ON)  ON [PRIMARY]
GO

CREATE SERVICE [Service://Sample/Target]
	ON QUEUE [dbo].[TargetQueue] ([CON://Sample/Contract])
GO

GRANT SEND ON SERVICE::[Service://Sample/Target] To [InitiatorBrokerUser]

CREATE ROUTE [InitiatorResponseRoute]
	WITH  SERVICE_NAME  = N'Service://Sample/Initiator' ,
	BROKER_INSTANCE  = N'533AE34F-FD12-4C27-9545-1EEAD080404C' ,  --Broker Instance of Initiator Databases
	ADDRESS  = N'TCP://User8.excellence.local:4022'
GO
CREATE REMOTE SERVICE BINDING [TargetRemoteServiceBinding]
	TO SERVICE N'Service://Sample/Initiator'
	WITH USER = [InitiatorBrokerUser] ,  ANONYMOUS = OFF
GO

GRANT RECEIVE ON [TargetQueue] TO [InitiatorBrokerUser]

Send Message From Initiator Instance

Use InitiatorDB
Go
--Send Message to TargetQueue.
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg XML;

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [Service://Sample/Initiator]
     TO SERVICE N'Service://Sample/Target'
     ON CONTRACT [CON://Sample/Contract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg = N'Message for initiator.';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [MSG://Sample/Request]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION

Receive Message at Target Instance

Use TargetDB
Go

--Get Message From Initiator
SELECT CAST(MESSAGE_BODY AS XML),* FROM [TARGETQUEUE]

Regards,
Nirav Gajjar

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server.

Today i got one issue on service broker for sys.transmission_queue.
Service broker configured well and working fine, suddenly service broker tranmission_queue goes high and high.
I have checked Transfer_Status to check the error if there is any, but i can’t see any error in Transfer_Status.

So, the question is what to do if SYS.Transmission_Queue stucks.
I had searched on web and read many bolg’s and forums but can’t find proper way to subsidize the transmission queue.

Finally, i have started to send the message manually from sys.transmission_queue and clean the conversation.

The steps that i followed is as below.

Step1 : Insert all Sys.Transmission_Queue data into temp table.

SELECT *
INTO MY_TRANSMISSION_QUEUE
FROM SYS.TRANSMISSION_QUEUE

Step2 : Now purge records of Sys.Transmission_Queue.

BEGIN
BEGIN TRY
DECLARE @Convhandle UNIQUEIDENTIFIER

DECLARE CONV CURSOR FOR

SELECT S2TRANSQUEUE.CONVERSATION_HANDLE
FROM SYS.CONVERSATION_ENDPOINTS AS S2CONVEND WITH(NOLOCK)
 INNER JOIN SYS.TRANSMISSION_QUEUE S2transqueue WITH(NOLOCK)
 ON S2TRANSQUEUE.CONVERSATION_HANDLE = S2CONVEND.CONVERSATION_HANDLE

OPEN CONV
FETCH NEXT FROM CONV INTO @Convhandle

WHILE @@FETCH_STATUS = 0
 BEGIN
 END CONVERSATION @Convhandle WITH CLEANUP
 FETCH NEXT FROM CONV INTO @Convhandle
 PRINT @Convhandle
 END

CLOSE CONV
DEALLOCATE CONV
END TRY

BEGIN CATCH
PRINT Error_message()
END CATCH

Step3 : Alter table and add one new column TransferStatus to check manually transfer status.


ALTER TABLE MY_TRANSMISSION_QUEUE
ADD TRANSFERSTATUS BIT

UPDATE MY_TRANSMISSION_QUEUE
SET TRANSFERSTATUS = 0

Step4 : Now send data manually from temp table and change the TransferStatus column.

DECLARE @Cnt INT
SELECT @Cnt = Count(*)
FROM My_transmission_queue(NOLOCK)
WHERE TRANSFERSTATUS = 0

WHILE( @Cnt > 0 )
 BEGIN
 DECLARE @Con_hand UNIQUEIDENTIFIER
 DECLARE @Xml XML
 DECLARE @Seq INT
 SELECT TOP 1 @Con_hand = CONVERSATION_HANDLE,
 @Xml = Cast(MESSAGE_BODY AS XML),
 @Seq = MESSAGE_SEQUENCE_NUMBER
 FROM My_transmission_queue(NOLOCK)
 WHERE TRANSFERSTATUS = 0
 ---Process Queue
 DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
 DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION

BEGIN DIALOG @InitDlgHandle
 FROM SERVICE [SB://Service/Audit/Initiator]
 TO SERVICE N'SB://Service/Audit/Target'
 ON CONTRACT [SB://Contract/Audit]
 WITH ENCRYPTION = ON;

 SEND ON CONVERSATION @InitDlgHandle
 MESSAGE TYPE [SB://Message/Audit/Request]
 (@Xml);

COMMIT TRANSACTION
 --End of Process Queue.

UPDATE MY_TRANSMISSION_QUEUE
 SET TRANSFERSTATUS = 1
 WHERE TRANSFERSTATUS = 0
 AND CONVERSATION_HANDLE = @Con_hand
 AND MESSAGE_SEQUENCE_NUMBER = @Seq
 SET @Cnt = @Cnt - 1
 END

Note: This script is useful if you have only one type of service. If you have more than one service in sys.transmission_queue you have to change the code as your requirement.

 
Thanks & Regards,
Nirav Gajjar