Tag Archives: SQL Server

Deal with message queue conversation with state CD in Service Broker

Deal with message queue conversation with state CD in Service Broker

Each side of a Service Broker conversation is represented by a conversation endpoint and sys.conversation_endpoints catalog view contains a row per conversation endpoint in the database. Generally when service broker accept record in message queue, it used to process message and remove it after closing by its own.

Recently at one of client site I have seen that messages are having “CD” state in sys.conversation_endpoints catalog view but it is not being removed from the message queue. Message queue was having approx half a million message with “CD” closed state so in order to remove those messages, I have created one small script to clean it up which might be helpful for you as well sometime.

CREATE PROCEDURE [CleanConversations]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY

DECLARE @handle UNIQUEIDENTIFIER

DECLARE conv CURSOR FOR SELECT a.conversation_handle FROM sys.conversation_endpoints a with(nolock)
WHERE state = 'CD'

OPEN conv

FETCH NEXT FROM conv INTO @handle

WHILE @@FETCH_STATUS = 0 BEGIN

END CONVERSATION @handle WITH CLEANUP
FETCH NEXT FROM conv INTO @handle
PRINT @handle

END

CLOSE conv

DEALLOCATE conv

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
RAISERROR('Error in %s: %s', 16, 1, 'CleanConversations', @msg)

END CATCH
END
GO

After having the above stored procedure, you can run it and clear all conversation which are already closed. Generally message should be closed and removed by its own but once in a blue moon if you come across the situation which I have observed at my client site, you can use this SP.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Milestone of 500+ article

Milestone of 500+ article

This post is 504th post in my blog. I have started sharing my knowledge in form of blog article three years back. Initially I, just, wanted to keep my script collection online so that I can find it easily as and when I need it but later on my blog has gained overwhelming response from the community.

I have started my blogging journey with “SQLHub.com” which was focusing on relational database only but later one I think to go beyond relational database and hence I have booked another domain, named “Extreme-Advice.com”.

I have started writing articles in “Extreme-Advice.com” 5 months back only. As soon as I have started “Extreme-Advice.com”, I found I had Alexa rank 1.8+ million world wide and 100K + in India but due to overwhelming response from the reader, I have reached to 19K+ rank in India and 160K+ world wide.

alexa

I don’t like to measure my blog performance by rank or by the hit I used to get. I would always like to know how many people I have helped so far and each email from the reader motivate me to work even harder as a blogger.

Whatever script I develop for my need, I used to share it with my blog reader. I share, mostly, those script which I use myself but out of all scripts some of the scripts are very useful and I would like to keep it handy so that I can easily find it out. Herewith, I am proving bookmark of those script and I hope you too find it useful and keep it handy, you never know, when shall you need it!!!

DBA Related Articles:

  • Query to Find missing job in SQL Server (Click Here)
  • Capture Schema Change in SQL Server to maintain history (Click Here)
  • Capture long running query in SQL Server (Click Here)
  • Analyze IO disk pressure in SQL Server (Click Here)
  • Transactions/Sec and Batch Requests/Sec performance counter in SQL Server (Click Here)
  • Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions (Click here)
  • use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance (Click Here)
  • Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find locked table name with duration and count in SQL Server (Click Here)
  • Find Blocking in SQL Server and use it to send an alert (Click Here)
  • Calculate Table & Schema size in SQL Server (Click Here)
  • Get row count for tables quickly in SQL Server (Click Here)
  • Find database restore date in SQL Server (Click Here)
  • Find Database backup history in SQL Server (Click Here)
  • Generate script to create and drop Primary Key and Foreign Key in SQL Server (Click Here)
  • “Max degree of parallelism” or “MAXDOP”, good guy or bad guy? (Click Here)
  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Keep watch on your disk space in SQL Server 2008/2005 (Click Here)
  • Keep watch on your database file size in SQL Server 2008/2005 (Click Here)
  • Performance Counter in SQL Server (Click Here)
  • Repl. Pending Xacts performance counter and high value in SQL Server (Click Here)
  • Performance Counter in Microsoft Windows Server 2008 (Click Here)
  • Implementing physical Database Structure in SQL Server (Click Here)
  • Create windows login and user in all databases with dataReader and dataWriter role in SQL Server 2008 (Click Here)
  • Shrink log file for all database in SQL Server 2008/2005 (Click Here)
  • Find user in all database with Sp_MSforEachDB in SQL Server 2005 (Click Here)
  • NOLOCK hint in SQL Server, Good guy or bad guy? (Click Here)
  • LOG Parser – Wonderful yet under-appreciated tool to read log of windows, IIS (Click Here)
  • Read IIS log with LOG Parser in SQL-Like language (Click Here)
  • Add/Remove column in IIS log file (Click Here)
  • Auto IIS log reading with Log Parser (Click Here)
  • Find TCP/IP port of SQL Server 2012 (Click Here)
  • Monitor user login connected to SQL Server (Click Here)
  • Start and stop server side trace profiler automatically in SQL Server 2012 (Click Here)
  • Read SQL Server Profiler Trace files with SELECT statement (Click Here)
  • Search SQL Server Object (Click Here)
  • Mirror backup to take full backup at multiple location in SQL Server (Click Here)
  • Split SQL Server Full backup in multiple files for faster backup (Click Here)
  • Backup and Restore Filegroup in SQL Server (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • CXPACKET wait stats in SQL Server (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • List of Table in Filegroup with table size in SQL Server (Click Here)
  • Automatic script backup of email operator in SQL Server by SSIS and SMO (Click Here)
  • Automatic script backup of mail profile in SQL Server by SSIS and SMO (Click Here)
  • GRANT VIEW DEFINATION permission to all Stored Procedures in SQL Server (Click Here)
  • Backup Linked Server script in SQL Server by SSIS (Click Here)
  • Keep watch on heavy software usage directly in production server (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)
  • Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)

Service Broker Articles:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)

INDEX Related Articles:

  • Some basics about Index in SQL Server (Click Here)
  • Clustered Index in SQL Server (Click Here)
  • Nonclustered Index in SQL Server (Click Here)
  • Included Column Index with non clustered index in SQL Server 2005/2008/Denali (Click Here)
  • Filtered Index in SQL Server 2008/Denali (Click Here)
  • Cannot create index on view Msg 1940, Level 16, State 1, Line 1 (Click Here)
  • Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005 (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • Do you know Index Statistics in SQL Server (Click Here)
  • Index Rebuild or Reorganize in SQL Server (Click Here)
  • Index Fillfactor in SQL Server (Click Here)
  • Find missing Index with DMVs in SQL Server 2005/2008/Denali (Click Here)
  • Find unused index in SQL Server 2005/2008/Denali (Click Here)
  • sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005 (Click Here)
  • Move clustered index to different filegroup in SQL Server (Click Here)

Replication Related Articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)
  • Keep an eye on replication error in SQL Server (Click Here)

Mirroring Related Articles:

  • Setup Certificate mirroring in SQL Server 2008 R2 (Click Here)
  • Check mirroring status from mirroring monitor in SQL Server (Click Here)
  • Find mirroring status by querying sys.database_mirroring in SQL Server (Click Here)
  • Database maintenance in mirroring principal database in SQL Server (Click Here)
  • Mirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type (Click Here)

Error Handling & Transaction Related Articles:

  • Introduction to Filestream in SQL Server 2012 (Click Here)
  • Error handling with “THROW” command in SQL Server 2012 (Click Here)
  • List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
  • Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)
  • Importance of transaction in SQL Server (Click Here)
  • Capture and log error of TSQL code in SQL Server 2012 (Click Here)
  • SET option XACT_ABORT and SQL Server Transaction (Click Here)

 Filestream Related Articles:

  • Introduction to Filestream in SQL Server 2012 (Click Here)
  • Create table with Filestream column in SQL Server 2012 (Click Here)

Microsoft Windows / SQL Azure:

  • What is Cloud Computing? (Click Here)
  • Understanding Microsoft Cloud and its essential part (Click Here)
  • Setup development environment for Windows Azure (Click Here)
  • Create Database and Server in SQL Azure (Click Here)
  • SQL Server 2008 SSMS and lower can’t be connected to SQL Azure (Click Here)
  • DMV to monitor SQL Azure database/Server (Click Here)

Encryption – Decryption:

  • Encryption – Decryption in SQL Server 2008 Part 1 (Click Here)
  • Service Master Key (Encryption – Decryption in SQL Server 2008 Part 2) (Click Here)
  • Database Master Key (Encryption – Decryption in SQL Server 2008 Part 3) (Click Here)
  • Certificate (Encryption – Decryption in SQL Server 2008 Part 4) (Click Here)
  • Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5) (Click Here)
  • Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6) (Click Here)
  • Backup Database with Encrypted Data and Restore it (Encryption – Decryption in SQL Server 2008 Part 7) (Click Here)

Full Text Search:

  • Introduction of iFTS (Integrated Full Text Search – iFTS in SQL Server 2008 Part 1) (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • FREETEXT Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 3) (Click Here)
  • FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4) (Click Here)
  • CONTAINS Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 5) (Click Here)
  • CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6) (Click Here)

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles .

Update XML attribute value based on condition with XQUERY

Update XML attribute value based on condition with XQUERY

Recently I had one article which shows how to update XML attribute value which doesn’t had any checking/condition while update. One of the reader asked me how can he update value based on condition. Here is the solution for it.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Dept=”VOC” Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Dept=”VOC” Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Dept=”VOC” Name=”PCB” /></TestDetails>’

GO

 

Now we will change attribute “Dept”’s value from “VOC” to appropriate value.

-- updating new attribute
UPDATE Orders
SET TestDetails.modify('
replace value of (/TestDetails/Test/@Dept)[1]
with (
if (/TestDetails/Test/@Name="SVOC Full") then
"SVOC"
else
"VOC"
)
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new valie in attribute “Dept” added inside “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • Update XML attribute value with XQUERY in SQL Server (Click Here)
  • XQuery in SQL Server to insert XML attribute in existing XML element (Click Here)
  • XQuery in SQL Server to delete XML element (Click Here)
  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

XQuery in SQL Server to delete XML element

XQuery in SQL Server to delete XML element

Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to delete element from XML data based on the condition you have.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’
GO

We can see that we have “TestDetails” as a root element in above given XML along with child elements “Test”. Suppose we have requirement to delete all elements which has test name “Pesticide Group1”, how can we do it with the help of XQuery?

Here is the answer:

--Update with XQuery to Delete element
UPDATE Orders SET
TestDetails.modify ('
delete
/TestDetails/Test[@Name cast as xs:string ? = "Pesticide Group1" cast as xs:string ?]')
GO

-- confirm result by querying Orders Table
SELECT * FROM Orders

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep an eye on replication error in SQL Server

Keep an eye on replication error in SQL Server

Setting up replication is may be little easy but to keep a constant watch on the performance of the replication is one of the tough task to do. If setup of replication is proper and if you are having a proper hardware and infrastructure, you won’t face much issue. Since we are not facing much issue, we may feel hectic to keep constant watch on replication health and this is the time you may face some trouble in replication and you are not alert to handle that uncertain situation.

DBA can’t afford to be in this situation. Every DBA has to prepare their self ready to handle as much uncertain situation as possible. I have created one small query to run in “Distributor” database of replication which show me error message with publisher, subscriber name and exact date time if anything goes wrong with my replication.

I used to call following SELECT query in my monitoring application so that if any error comes, I get immediate update from the person who is manually monitoring my application in the company. “Prevention is always better then cure” but nobody is sure enough that after having prevention, there won’t be any situation arise which force us to have cure. This is the reason I keep adding things in my monitoring application though I used to take as much prevention as possible while setting up things.

SELECT
ma.publisher_db,
ma.publication,
ma.subscriber_db,
msre.time,
msre.error_text
FROM
MSrepl_errors msre
INNER JOIN
MSdistribution_history msh
ON
(msre.id = msh.error_id)
INNER JOIN
MSdistribution_agents ma
ON
(ma.id = msh.agent_id)
ORDER BY
msre.time DESC

Above SELECT query will return the result only, if we have any error in replication at the moment, otherwise you will get blank result set.

Over the time, I have written quite a few articles on the subject of “Replication”, If you wish to refer any of them, have a look at list given below:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep watch on replication undistributed command in SQL Server via SSIS

Keep watch on replication undistributed command in SQL Server via SSIS

In my earlier blog “Find undistributed replication command in SQL Server” I have exaplained the importance and usage of “sp_replmonitorsubscriptionpendingcmds” system stored procedure to find out undistributed command.

If we execute “sp_replmonitorsubscriptionpendingcmds”, you may get undistributed command one time manually but I always try to capture important information in my monitoring application so that If there is any issue, it comes to notice of our support people and they can inform DBA team immediately.

This is the requirement which triggered this article.

Here is my requirement and logic I want to implement.

I have one of the main production database (let us call this ProdDB) which is replicated to three different instance with same publication so we have three subscriber to ProdDB database. Out of these three databases, one database (let us call ProdDBSub1) is being used to display the report in our application which is being used by approx 12000 concurrent users.

Higher latency or huge undistributed command will provide wrong information to the client which is not at all acceptable and this is why I have captured latency of replication in my monitoring application (Find the script here) and also captured total undistributed command in my monitoring application, if undistributed command number keeps increasing for long time and doesn’t decrease, our support team member immediately call DBA to look at replication.

I have created one table in “DBAdb” database which has row for each publication/subscriber combination along with other important details. Each time I query “sp_replmonitorsubscriptionpendingcmds”, I used to store result in that table (update the current row only). I have this process in one SSIS package which is scheduled to run every 15 seconds .

Let us create some foundation in SQL Server before we start creating SSIS package.

--create new database, if it does not exists already
CREATE Database DBAdb
GO

--create one table which stored information for undistributed command
USE [DBAdb]
GO

CREATE TABLE [UndistributedCommands](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[PublicationName] [varchar](100) NULL,
[SubscriberName] [varchar](100) NULL,
[PendingCommands] [int] NULL,
[PendingDuration] [int] NULL,
[LastRefreshed] [datetime] NULL,
[LastNonEmpty] [datetime] NULL
) ON [PRIMARY]
GO

--insert one record for your publication/subscriber.
--this record will be updated everytime for same publication/subscriber
INSERT INTO UndistributedCommands
SELECT 'ProdDBPub','ProdDBSub',0,0,GETDATE(),GETDATE()
GO

Now create one SSIS package and have three package level variable as shown in following screen capture.

1Variable

After having all three variables, take one “Execute SQL” Task in your package and use following query inside the task.

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'ProdDBPub',
--replication publication name
@publication ='ProdDBSub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='ProdDBSub1',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

Here is the screen capture of “Execute SQL” Task:

2ExecuteSQL

System Stored Procedure “sp_replmonitorsubscriptionpendingcmds” will return result set and that should be mapped with one of the variable we have created above. Follow the screen capture.

Please note that, I have had database connection of distribution database of replication for above given screen capture.

3ExecuteSQL

Now, take one foreach loop container and iterate it with “Obj_Result” variable.

4ForEachLoop

Map two variables with the result set coming from “sp_replmonitorsubscriptionpendingcmds” in the Foreach Loop container itself:

5ForeachLoop

Now take one more execute SQL Task and kept it inside the foreach loop container. That “Execute SQL” task will have following query to update data in table.

DECLARE @PendingCMDCount BIGINT
DECLARE @ExstimatedProcessTime BIGINT

SET @PendingCMDCount = ?
SET @ExstimatedProcessTime =?

UPDATE
UndistributedCommands
SET
PendingDuration = @ExstimatedProcessTime ,
PendingCommands = @PendingCMDCount,
LastRefreshed = getdate()
WHERE
SubscriberName = 'ProdDBSub' AND PublicationName='ProdDBPub'

IF @PendingCMDCount <> 0
BEGIN
UPDATE
UndistributedCommands SET LastNonEmpty = getdate()
WHERE
SubscriberName = 'ProdDBSub' AND PublicationName='ProdDBPub'
END

6ExecuteSQL

Please note that this Execute SQL task is having connection with “DBAdb” database and my “UndistributedCommand” table is there and data from this table will come to my monitoring application.

We have collected two valued in two different variable in Foreach Loop Container which we will pass to “Execute SQL Task” inside the loop.

7ExecuteSQL

Now, you are having package ready to run.

8FinalPackage

Execute this package from SQL Server Job or from Windows Schedule task via .BAT file as per the frequency needed for your business logic, I used to keep it at 15 seconds. Display the rows of “UndistributedCommands” in monitoring application, if you have, if undistributed command is >0.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find undistributed replication command in SQL Server

Find undistributed replication command in SQL Server

I have written one article to find replication latency sometime back. If I find latency of 10 seconds or more for longer time then I always try to find the bottleneck and fix it because in the environment I am working, won’t afford big latency.

In finding the bottleneck, my first step would be to find how many commands are pending to apply in replicated database. To find undistributed command, you can use replication monitor which comes with SQL Server itself or use sp_replmonitorsubscriptionpendingcmds” system stored procedure which you can find in distributor database of replication.

 

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'AdventureWorks2012',
--replication publication name
@publication ='AdventureWorks2012Pub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='AdventureWorks2012ReplDB',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

 

Here is the screen capture of output.

ReplicationPendingCommand

This screen capture shows that total number of 64 commands are waiting to apply from “AdventureWorks2012” database to “AdventureWorks2012ReplDB” and that would take 0 (<1) seconds.

I have written few more replication related articles, have a look if you are interested.

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Copy Database wizard in SQL Server 2012

Copy Database wizard in SQL Server 2012

It is observed so many times that people used to use backup of database to restore in another instance or in the same instance with different database name. Few people used to detach database, copy .MDF / .NDF / .LDF file to another instance and attach it back in old and new instance also. I would prefer to use “Copy Database” wizard if I have good network between both instance. If it is a case of instance in same computer, I would definitely use “Copy Database” wizard.

This is not a new facility but this is one of the useful yet under appreciated facility. I am going to copy “AdventureWorks2012” database in the same instance with name “AdventureWorks2012_New”. Please follow the screen capture given below for more details.

Note: Please keep SQL Server Agent running as it will be needed to run “Copy Database” wizard smoothly.

1OpenCopyDatabaseWizard
Once you get “Copy Database” wizard in SQL Server, click on “Next” button from the following dialog box.

2ClickNext

 

Select the source SQL Server Instance in “Source Server” text box and give proper credential “Windows Authentication” or “SQL Server Authentication”
3SourceServer

 

Select the destination (where you want to copy database) SQL Server Instance in “Destination Server” text box and give proper credential “Windows Authentication” or “SQL Server Authentication”
4DestinationServer

 

Now, you have two choices here. Either go for first radio button which will detach database (database will be offline and won’t be used), copy it to destination server and Attach it in new server. If you wish to keep database online and available to use, go for second radio button which will use SMO method. This method will work slow as compare with first option but your database will remain usable during copy process.
5SMOorAttachDetach

 

You have to select database(s) which you want to copy. You can even use “Move” checkbox if you don’t want database in source server and want to move database to another server completely.
6DatabaseToCopyorMove

 

Here you have to give new name of database for destination server. If you are having different SQL Server Instance then source, you can have even same name of database, no need to give new name.
7FileNameForNewdatabase

SQL Server internally create SSIS package and deploy it to SQL Server and call that SSIS package from SQL Server JOB. This Job run either one time or at the scheduled time you specify. You can see package name and location in following dialog box, you don’t need to do anything.
8PackageName

 

You can run that SSIS package immediately to copy database one time or schedule it if you want copy of your database at some other server periodically, you can mention the schedule also.
9JobForDatabaseMove

 

Have a look at final summary of your selection so far and click on “Finish” button.
10ClickOnFinish

 

If you get “Success” status for all steps, you are done.
11ClickOnClose

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Synonyms in SQL Server to give short name to your fully qualified object name

Synonyms in SQL Server to give short name to your fully qualified object name

Synonyms is not a new enhancement in SQL Server 2012, it is long back introduced in SQL Server 2005 but I have seen many SQL Developer doesn’t know this feature so thought to give some light to this concept.

As per MSDN, A synonym is an alternative name for a schema-scoped object. In SMO, synonyms are represented by the Synonym object. The Synonym object is a child of the Database object. This means that synonyms are valid only within the scope of the database in which they are defined. However, the synonym can refer to objects on another database, or on a remote instance of SQL Server.

The object that is given an alternative name is known as the base object. The name property of the Synonym object is the alternative name given to the base object.

Synonym can be created on following:

Assembly (CLR) Stored Procedure

Assembly (CLR) Table-valued Function

Assembly (CLR) Scalar Function

Assembly Aggregate (CLR) Aggregate Functions

Replication-filter-procedure

Extended Stored Procedure

SQL Scalar Function

SQL Table-valued Function

SQL Inline-table-valued Function

SQL Stored Procedure

View

User Defined Table (Including local and global temporary tables)

You can alter data of object via synonyms but you can’t alter schema of object via synonym. For example I have table named “AdventureWorks2012.Sales.SalesPersonQuotaHistory” in Adventureworks2012 database and I give synonyms “SalesPersonQuota” then I can execute any DML commands (INSERT / UPDATE / DELETE) on “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table but I can’t Add/Remove column or change datatype of column in “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table.

Nowadays, we used to create proper schema for each object and detail oriented name to object which makes object name longer so it is good to give short but meaningful name to object to increase productivity (though we have intelisense now).

By providing synonyms name to developer for use in their script, you are also ensuring that they will not be able to change definition of the object via synonyms (though we can handle this with proper login/user permission). This is one more advantage as per me. Each business process and environment has their own rules and regulation to apply. We may have debate on whether this is useful or not as I am sure few people would appreciate the usage of synonyms whereas few will stand against it. Personally I would like to use synonyms for the benefit I just explained.

Let us see how we can create and use synonyms. I will use “Adventureworks2012” database for this demonstration and table would be “AdventureWorks2012.Sales.SalesPersonQuotaHistory”.

USE AdventureWorks2012
GO

CREATE SYNONYM SalesPersonQuota
FOR AdventureWorks2012.Sales.SalesPersonQuotaHistory
GO

Now if we query original table or query synonyms, we will get same results, let us confirm it.

SELECT TOP 10 * FROM AdventureWorks2012.Sales.SalesPersonQuotaHistory
GO

SELECT TOP 10 * FROM SalesPersonQuota
GO

I have received the same output. Have a look:

SynonymsOutput

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Setup service broker between two databases in SQL Server.

Setup service broker between two databases in SQL Server.

My earlier article “Service Broker implementation between two tables in one database”  explained how to setup service broker between two table in one database. Today I am explaining how to setup service broker between two tables resides in two different databases in one SQL Server instance.

I will be using the same table, queues, contracts, services and message types given in “Service Broker implementation between two tables in one database” article so if you have not read this article, I would recommend to take a look there once.

Creating two different databases in one SQL Server 2012 instance.

USE MASTER;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBMain')
DROP DATABASE ExtremeAdviceSBMain;
GO

CREATE DATABASE ExtremeAdviceSBMain;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBReceiver')
DROP DATABASE ExtremeAdviceSBReceiver;
GO

CREATE DATABASE ExtremeAdviceSBReceiver;
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBMain  SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBMain  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBMain TO [sa];
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBReceiver SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBReceiver  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBReceiver TO [sa];
GO

Let us now create basic architecture of service broker like message type, contract, queue and services in both databases ExtremeAdviceSBMain & ExtremeAdviceSBReceiver (target and initiator). Please note that I have used the same service broker object I have used in earlier article. We may eliminate few things but I will cover it up later on.

USE ExtremeAdviceSBReceiver
GO

--Create Message Type in target database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in target database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

USE ExtremeAdviceSBMain
GO

--Create Message Type in initiator database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in initiator database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO
--Create initiator Queue

CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

Now we will create two tables. One table in initiator database which is OrderDetail. Sales person will generate sales order and data will falls under that table. If sales person inserting duplicate order, it will caught by trigger and will be inserted in OrderHistory table. For more detail about this concept, have a look at my earlier article.

USE ExtremeAdviceSBMain
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

USE ExtremeAdviceSBReceiver
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now we will create main logic which will send and process original message along with trigger on OrderDetail table which will activate service broker as soon as new record inserted in OrderDetail table.

USE ExtremeAdviceSBMain
GO
--Create this SP in Initiator database
--Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;

END TRY

BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

USE ExtremeAdviceSBReceiver
GO

--Create this SP in target database
-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM   ExtremeAdviceSBMain.dbo.OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'

EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'

SELECT @xml = 'OK';

SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()

SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

USE ExtremeAdviceSBMain
GO

--create following SP to handle response of target in initiatordatabase
--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
END CATCH
GO

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Once you are ready with this structure, you are all set to go. Let us insert one record in OrderDetail table.

USE ExtremeAdviceSBMain
GO

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM  ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

Since we have not inserted any duplicate records yet, we won’t get any record in OrderHistory table. Let us inserting duplicate record in OrderDetail table.

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

We, now, suppose to get record in OrderHistory table but unfortunately we won’t get it. There are two possibilities that either our initiator queue doesn’t have sent message to target or target have received the record but it has not processed it. Right???? Let us check it.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see that our “OrderTargetQueue” has record to process. Then question is why it has not processed?

Well, we have had Trigger on OrderDetail table which has activated our initiator queue and hence record reached to target queue but target queue has not been activated and hence it has not processed target message.

We can process message with following SP either manually or by sql server job as we have not activate it automatically.

 USE ExtremeAdviceSBReceiver
 GO

EXEC [OrderTargetQueueReader]
GO

Now let us see whether record is processed or not.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see “OrderTargetQueue”has not record now as our message is processed and you can also see the entry in “OrderHistory” table also as we had one duplicate record.

Still “OrderInitiatorQueue” has record to process. You can see that it is just a blank message for end dialog with “OK” status which has sent back by target queue after processing messages.

We can run following SP to process end dialog in initiator queue.

USE ExtremeAdviceSBMain
GO

EXEC [OrderInitiatorQueueReader]
GO

I will shortly come up with article which will run full service broker cycle by its own without manual interaction.

If you wish to refer my other Service Broker related article, have a look at following list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.