Ritesh Shah (Extreme-Advice.Com)

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

Count active Thread for Service Broker Queue in SQL Server

Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to each queue. Suppose we have one billing queue then we suppose to name it as “BillingInitiatorQueue”, “BillingTargetQueue” etc.

Since target queue has responsibility to implement actual business logic, I am always interested to see how many thread currently running for each target queue. Suppose I have set 15 thread for “BillingTargetQueue” but it may happen that I have only 5 to 10 messages in queue and hence all 15 thread will not be active. If I don’t have any message in queue then obviously no thread will be active though I set 15. Sometime I may have 1000 message to process in target queue but even I have 10 thread active only. Remaining 5 thread may not be getting proper system resources or anything else. I can’t even decide whether to increase thread or decrease without knowing actual number of message, time thread is taking to process. I have already written articles about “Find Service Broker Queue Count in SQL Server (Click Here) ” and “Keep watch on Service Broker Queue Delay in SQL Server (Click Here) ”. Today I am going to show the script which will give the count of thread for target queue running at the moment.


SELECT

OBJECT_NAME(queue_id) AS QueueName,

COUNT(*) AS ThreadCount

FROM

sys.dm_broker_activated_tasks WITH (NOLOCK)

--Comment the WHERE clause to see thread count for all queue

--not only target queue

WHERE (OBJECT_NAME(queue_id) LIKE '%Target%')

GROUP BY

OBJECT_NAME(queue_id)

HAVING

(COUNT(*) > 0)

ORDER BY

ThreadCount DESC

GO

I have written few more article about service broker, have a look if you are interested.

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)
Keep watch on Service Broker Queue Delay in SQL Server (Click Here)
Deal with message queue conversation with state CD in Service Broker (Click Here)

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

Reference: Ritesh Shah

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

Error Fix – Replication subscriber does not exist on the server anymore

Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have restored mirrored database backup to primary server.

Now situation is complicated as subscriber was removed, distributor was removed but in primary server, they are seeing publication as well as subscriber. They have already tried the way I have explained in one of my earlier article “Forcefully remove replication publisher, subscriber and distributor in SQL Server” but no luck.

When they tried to remove publication by pressing “Del” key after selecting “Publication” under “Replication” in SSMS, they faced following message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete publication 'SMXPPublisher'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476
------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------

Cannot drop the publication because at least one subscription exists for this publication. Drop all subscriptions to the publication before attempting to drop the publication. If the problem persists, replication metadata might be incorrect; consult Books Online for troubleshooting information.
Changed database context to 'SMXP'. (Microsoft SQL Server, Error: 14005)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=14005&LinkId=20476
------------------------------
BUTTONS:

OK
------------------------------

Here is the screen capture of the error:

This is obvious message that they can’t delete Publisher until and unless they remove subscriber under publication but the twist is they have already deleted subscriber from the secondary server and primary server were down at that time so effect doesn’t came there so while deleting subscriber from the primary server, they were greeted with following message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"MARS:SMXPRepl" does not exist on the server anymore. Please refresh its parent tree node.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=ObjectNoLongerExists&LinkId=20476
------------------------------

BUTTONS:

OK
------------------------------

Here is the capture for this error:

Now, there is a big question, how to remove this replication?

After getting in this clumsy situation, they have called me up to help. They needed very fast solution so I provided undocumented and dirty but yet workable way. Here is what I have asked them to perform and update me with the result.

Here is the small TSQL script I told them to run on primary server in replicated database.

USE master
EXEC sp_removedbreplication @dbname='smxp'
GO

sp_dropsubscription @subscriber='smxprepl'

sp_droppublication 'smxppublisher'

After executing above given commands, they were facing following error:

--Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for replication.

Well, my suggestion now is to unmark “MSPeer_lsns” object from replication with following command and then try to execute above script to remove and drop replication.

sp_MSunmarkreplinfo 'MSpeer_lsns'

Again there wasn’t luck with them so finally I send them one more TSQL which did the magic.

DROP TABLE sysarticleupdates
DROP TABLE sysarticles
sp_removedbreplication 'smxp'
GO
DROP TABLE syspublications
DROP TABLE sysschemaarticles
DROP TABLE systranschemas
DROP TABLE syssubscriptions
DROP TABLE sysarticlecolumns
DROP TABLE MSpub_identity_range
DROP TABLE MSpeer_response
DROP TABLE MSpeer_request
DROP TABLE MSpeer_lsns
DROP VIEW sysextendedarticlesview

After executing above given TSQL script, it has removed everything related to replication. However, I don’t recommend this script for normal situation.

Here is the list of my other replication related articles, if you are interested!!!!

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)

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

Reference: Ritesh Shah

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

Bug Fix – Show Result Pane not working in SQL Server 2012 with CTRL + R

Once you are addicted with some keyboard short keys then you hate to use mouse for those things for sure. SQL Developer/DBA are, mostly, addicted with “CTRL + R” short key which is being used to show/hide result panel in SSMS.

I, many time, received question/complaint that CTRL+R is not working in SQL Server 2012′s SSMS, especially from the user who are using Windnows 7 (Windows 8 doesn’t have this behavior, at least for me).

Well, this is very known issue and there is a workaround for this. Let us see how shall we do this…..!!!

Once you open the SSMS, go to Tools | Customize.

Once you will get “Customize” dialog box, click on “Keyboard” button from the “Customize” dialog box.

Now you will be greeted with “Options” dialog box, where you have to select “Windows.ShowResultPane” option from listbox given under “Show Commands Containing”, select “SQL Query Editor” under “Use new Shortcut in”, also Press the short key you wanted to use (I have selected very known CTRL+R) under “Press shortcut keys” and click on “Assign” button and close the dialog box by clicking on “OK” button.

Now, you are there with your very own shortcut key “CTRL+R”. It will work in SSMS even in Windows 7.

Have you ever faced this issue?

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

Reference: Ritesh Shah

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

Find disabled job in SQL Server

I, generally, don’t tend to keep disabled job in my server. I would always like to remove disabled job after generating the script and keep the backup of script and this is the reason whenever someone intentionally or unintentionally disabled the job, my monitoring application shows the server name and disable job.

There is one small TSQL SELECT script which shows the disabled job along with server name and last modified date.

SELECT
@@servername as ServerName,
name as JobName,
date_modified as LastModifiedDate
FROM
msdb.dbo.sysjobs WITH (NOLOCK)
WHERE
enabled = 0

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)
Find SQL Server Agent job ran on specific date with its status (Click Here)
Search SQL Server Job based on keyword provided (Click Here)
Script all SQL Server Job automatically by SSIS and SMO (Click Here)
Get list of failed SQL Server Agent job in SQL Server (Click Here)
Query to Find missing job in SQL Server(Click Here)

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

Reference: Ritesh Shah

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

Copy and archive SQL Server database backup

Though you are working as SQL Server DBA, you have to think, at least sometime, beyond relational. We can schedule different types of backup, ie: full, differential, transaction etc., from within SQL Server but what if you want to set archiving policy also? There are so many different ways to accomplish this task but I, personally, prefer to go for .BAT file which can be called from windows schedule task.

Suppose I want following policy (obviously one can change the policy given below as per their need).

I want full/trn backup to be kept in same server where SQL Server is installed for one day
Very next day that backup to be moved to network location
Network location should keep backup for three days and older backups then three days should be removed

I already have maintenance plan running in SQL Server which keeps taking full backup everyday and transaction backup at every 15 minutes. I will handle copy/archive process from .BAT file and schedule this batch file to run at everyday 12:05AM to move previous days backup and remove older backup.

Here is the script I would have in my batch file:

Sqlcmd -E -S"RiteshPC\Dev2012" -dmsdb -Q"set nocount on ; SELECT DISTINCT bmf.Physical_device_name +','+'\\192.168.1.200\RiteshPCDev2012Backup\DatabaseName\'FROM msdb.dbo.backupset (nolock) bs INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf on (bs.media_set_id = bmf.media_set_id) WHERE bs.backup_finish_date > DATEADD(HOUR,-24,GETDATE()) " -o "E:\Batchfiles\BackupFileDev.txt"
FOR /F "tokens=1,2 skip=2 delims=," %%G IN (E:\BatchFiles\BackupFileDev.txt) DO xcopy /Y %%G %%H


forfiles.exe /p E:\Database-Backup\Dev /s /m *.bak /d -1 /c "cmd /c del @file"
forfiles.exe /p E:\Database-Backup\Dev /s /m *.trn /d -1 /c "cmd /c del @file"
forfiles.exe /p \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName /s /m *.bak /d -3 /c "cmd /c del @file"
forfiles.exe /p \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName /s /m *.trn /d -3 /c "cmd /c del @file"

Herewith I want to list some points where you may need change while setting up batch file for your environment.

“RiteshPC\Dev2012″ is my SQL Server Instance name, you have to provide yours.
I have used “-E” parameter in the first line of batch file as I want my SQL Server can be connected with windows authentication, if you want SQL Authentication, you have to provide SQL Server login/password which has permission to read system table in MSDB database
I have used “DATEADD(HOUR,-24,GETDATE())” in SELECT query as I want to deal with all the backup comes in last 24 hours only.
“\\192.168.1.200\RiteshPCDev2012Backup\DatabaseName\” is the network path where I want to put backup for three days.
Select query ran in MSDB database will output results in text file at “E:\Batchfiles\BackupFileDev.txt”. That file will be read, copy the backup to network path.
In first two “forfiles.exe” command, I am deleting .bak and .trn from the server which are older then 1 day (you can observe “-1” parameter value I gave after “/d” parameter)
In 2^nd and 3^rd “ForFiles.exe” I ham deleting .bak and .trn files from the networks which are older then 3 days.

Do you have any other way to manage copy/archive of backup?

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

Reference: Ritesh Shah

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

Error Fix Database Mail XPs error in SQL Server

Today I have received one email from one of the blog reader since he was facing an issue in his .NET application which is related to SMTP email in SQL Server. Here is the error he was facing:

SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Database Mail XPs’ by using sp_configure. For more information about enabling ‘Database Mail XPs’, see “Surface Area Configuration” in SQL Server Books Online.

The statement has been terminated.

Here is the screen capture he sent to me:

Well, by default, SMTP mail functionality comes disabled and if you intend to use it, you have to enable it with the help of SP_Configure system stored procedure.

Here is the small TSQL snippet which helps you to enable database mail functionality.


USE MASTER
GO

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE 'Database Mail XPs', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Once you execute above given TSQL Snippet, you can use database mail functionality.

BTW, I have written few articles related to SP_Configure system stored procedure, if you are interested, have a look:

OpenRowSet and OpenQuery in SQL Server 2005/2008 (Click Here)
Enable XP_cmdshell in SQL Server 2008 (Click Here)
Copy files with SQL Server from one location to another location. (Click Here)
Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005 (Click Here)

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

Reference: Ritesh Shah

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

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

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

Keep watch on Service Broker Queue Delay in SQL Server

We have very large setup of Service Broker enabled in our live databases. Out of which, some of the nightly business processes create a huge target queue which we suppose to finish during the night time itself. If we can’t cleanup all these queue in night time, it creates additional overhead during the day time when most of the customer uses our SaaS product which we can’t afford.

This is the reason I am using one very small script which gives me approximate (not exact) idea about how much time current queue will take to complete. If it is taking longer time then I can increase the threads to process it faster to get it done in night time. BTW, one needs to keep watch on locking if threads are blocking each other, you won’t get much benefit of increasing threads.

Note: I am using one user defined function “GetQueueCount” in the script below so if you don’t have function “GetQueueCount” than kindly create it first.

Here is the script I am using:

/*
EXECUTE CheckQueueDelay 'ProviceYourQueueName',100
*/
CREATE PROCEDURE CheckQueueDelay
(
@QueueName VARCHAR(100),
@MaxMin INT = 10
)
AS
DECLARE @cnt INT
SELECT @cnt = 1

DECLARE @a INT, @b INT, @tot INT

WHILE @cnt <= @MaxMin BEGIN
SELECT @a = rows FROM GetQueueCount() WHERE name = @QueueName
IF @a = 0 break;
WAITFOR DELAY '00:01:00'
SELECT @b = rows FROM GetQueueCount() WHERE name = @QueueName
IF @b = 0 break;
SELECT @tot = @a - @b
RAISERROR ('Processed %d rows in 1 minute', 10, 1, @tot) WITH NOWAIT
SELECT @tot = @b / (@a - @b)
RAISERROR ('Need %d minutes to process remaining %d rows', 10, 1, @tot, @b) WITH NOWAIT
SELECT @cnt = @cnt + 1
END
GO

Now, you can execute this stored procedure to see how much time would it take to complete the queue. If queue is processing and it is also getting new records at the same time, you might not have exact value.

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

Reference: Ritesh Shah

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

Milestone of 500+ article

This post is 504^th 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.

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