Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75 Could not drop article. A subscription exists on it.

When we try to delete a Publication, It says "Could not drop article. A subscription exists on it". But, subscriber also deleted! (Still was getting the following Err when I delete the Publication!)

So, I Tried to delete the article first using the script given below!

Use  PublicationDB
sp_droparticle  @publication = N'Publication Name',

@article = N'Article Name',
@force_invalidate_snapshot = 1

But, I could not delete the article! and occured the following Error message!

Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

But, No subscription was not there for the Publication!

So, I could realize that the Subscription not removed properly but may be partially(May be some of the flag not updated internally)!

So, I tried to remove the Subscription using the following way...!

Use  distribution
MSS From MSsubscriptions MSS JOIN msarticles MSA

On (MSS.article_id = MSA.article_id)
Where MSA.article ='Article Name you trying to Delete'
And MSS.publisher_db ='Publisher DB Name'

I removed the subscription for the particular publication and I tried to remove the article now

Use PublicationDB

sp_droparticle @publication = N'Publication Name',
@article = N'Article Name',
@force_invalidate_snapshot = 1

Wow...! I removed the article successfully from the Publication without any error!

Finally, I could able to remove the publication also without any error! :)

Where is the Object ? In which Database ?

I want to use one stored procedure or any other object name contains 'Blog' from current Server or Instance.

But, I don't know where is/are the procedure or object actually exists! or In which database ?

Is there any way to search an object in all the databases in current Instance ?
Use  Master
@WhereIs Table

DBName Varchar(100),
ObjectName Varchar(150),
ObjectType Varchar(150)

Insert @WhereIs
Exec sp_msforeachdb 'use [?]; select DB_NAME(), name, Type_desc from sys.objects where name like ''%Blog%'''

Select * from @WhereIs


Restoring Database - while the database is in use!

The following error will be thrown...! When try to restore a database while the same being used/accessed.

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

The reason is very common as the Database being tried to restore is/are used/accessed somehere else through SSMS/Application(s)...

But, somebody says that, "First change the Database restrict access to SINGLE USER Mode and try to restore the same instead of KILLing the opened/accessed connections(SPIDs)"

Is that True ?

I don't think so!

Because, When you try to change the database restrict access to SINGLE USER mode using the below script/using the wizard. It'll close/KILL the opened/accessed sessions(SPID)

USE [Master]


Because, "SQL Server will close all other connections to the database" before change the Restrict access mode to SINGLE USER mode!

So, Changing the Database restrict access mode to SINGLE_USER will also close(KILL) all the connection(s) to the database!

Identifying DeadLock Issue/Rootcause using Extended Events!

SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. It has been intorduced in SQL Server 2008!

So, We don't want to enable/apply Trace Flag on StartUp parameters! to log detailed DeadLock info in SQL Server Error Log. Instead, DeadLock info can be retrived using Extended Events!

It has some issue with system_health session! But, Still it can be used...

/*Sample Table and test data*/
Create Table Table1
Id Int Identity(1,1),
Column1 Varchar(25)

Create Table Table2
Id Int Identity(1,1),
Column1 DateTime

Table1(Column1) Values('SQL Server')

Insert Table2(Column1) Values(GETDATE())

/*Session #1(i.e: 59)*/
Begin Tran
Update Table1 Set Column1 ='SQL Server Buddy' Where Id=1
Waitfor Delay '00:00:02'
Update Table2 Set Column1 =GETDATE() Where Id=1

/*Session #2(i.e: 61)*/
Begin Tran
Update Table2 Set Column1 =GETDATE() Where Id=1
Waitfor Delay '00:00:02'
Update Table1 Set Column1 ='SQL Server Buddy' Where Id=1

/*DeadLock occurred in one of the session*/
Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

/*Trackdown and Extract the DeadLock info from Extended Events*/
If OBJECT_ID('DeadLockInfo') Is Null
Create Table DeadLockInfo
Id Int Identity(1,1),
DeadLockInfo XML

OBJECT_ID('DeadLockInfo_Temp') Is Null
Create Table DeadLockInfo_Temp
Id Int Identity(1,1),

Table DeadLockInfo
Truncate Table DeadLockInfo_Temp

@Max Int, @Min Int

Insert DeadLockInfo_Temp(DeadLockInfo)
REPLACE(XED.X.value('(data/value)[1]', 'varchar(max)'),
'', ''),
') as DlockInfo
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XED (X)
where XED.X.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Select @Max = @@IDENTITY
Select @Min = 1

While(@Min <= @Max)
Begin Try
Insert DeadLockInfo(DeadLockInfo)
Select DeadLockInfo from DeadLockInfo_Temp where Id = @Min
End Try
Begin Catch
--Just Skip the Error/Unformated Record
End Catch

Select @Min = @Min +1


;With CTE
Select DeadLockInfo.value('(deadlock-list/deadlock/victim-list/victimProcess/@id)[1]','varchar(20)') [Victim Process],
DeadLockInfo.value('(deadlock-list/deadlock/process-list/process/@spid)[1]','Int') [SPID1]
From DeadLockInfo
), FinalCTE
Select Id [Batch No],case when [Victim Process] Is Not Null Then 'Yes' Else 'No' End [Victim Process],[Process],[Wait Resource],[Wait Time],[Transaction Name],[Lock Mode],[SPID],[Batch Started],
[Batch Ended],[Client Application],[Hosted From],[Login],[Isolation],[DBID],[Proc Name],[Line],[SQL Handle],
[Script] from

Select Id,
DeadLock.Node.value('(@id)[1]','varchar(20)') [Process],
DeadLock.Node.value('(@waitresource)[1]','varchar(50)') [Wait Resource],
DeadLock.Node.value('(@waittime)[1]','BigInt') [Wait Time],
DeadLock.Node.value('(@transactionname)[1]','varchar(100)') [Transaction Name],
DeadLock.Node.value('(@lockMode)[1]','varchar(25)') [Lock Mode],
DeadLock.Node.value('(@spid)[1]','Int') [SPID],
DeadLock.Node.value('(@lastbatchstarted)[1]','Datetime') [Batch Started],
DeadLock.Node.value('(@lastbatchcompleted)[1]','Datetime') [Batch Ended],
DeadLock.Node.value('(@clientapp)[1]','Varchar(100)') [Client Application],
DeadLock.Node.value('(@hostname)[1]','Varchar(100)') [Hosted From],
DeadLock.Node.value('(@loginname)[1]','Varchar(100)') [Login],
DeadLock.Node.value('(@isolationlevel)[1]','Varchar(100)') [Isolation],
DeadLock.Node.value('(@currentdb)[1]','Int') [DBID],
DeadLock.Node.value('(executionStack/frame/@procname)[1]','Varchar(100)') [Proc Name],
DeadLock.Node.value('(executionStack/frame/@line)[1]','Int') [Line],
DeadLock.Node.value('(executionStack/frame/@sqlhandle)[1]','Varchar(50)') [SQL Handle],
DeadLock.Node.value('(inputbuf)[1]','Varchar(Max)') [Script]
From DeadLockInfo Cross Apply DeadLockInfo.nodes('//deadlock-list/deadlock/process-list/process') DeadLock(Node)
) as A Left Join CTE On (A.Process = CTE.[Victim Process] And A.SPID = CTE.SPID1)

Select * from FinalCTE Order By [Batch No] Desc


To get Nth Weekday name for a particular Month, Year

Declare @Year Int, @Month Int, @DayName Varchar(25), @NthDay Int
Select @Year = 2012 /*YEAR you want to perform*/
Select @Month = 8 /*MONTH you want to perform*/
Select @DayName = 'Saturday' /*WEEKDAY you want to perform*/
Select @NthDay = 3 /*Nth WEEKDAY you want to perform*/

;With CTEs
Select 0 Slno
Union All
Select Slno + 1 From CTEs Where Slno <=30
Select DateName(Weekday,DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01')) [Day], DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01') [Date] From CTEs
Select ROW_NUMBER() Over(Order By [Date]) Nth,* from [Days] Where Month([Date]) =@Month And [Day] = @DayName

Select [Day], [Date] from [DayWithSeq] Where Nth = @NthDay


How to generate sequence number based on specific column(Without using LOOP, CTE, #TABLE, @TABLE VARIABLE, RANKING Function)

How to generate sequence number based on specific column(Without using LOOP, CTE, #TABLE, @TABLE VARIABLE, RANKING Function)

Sample Table and record(s):
CREATE TABLE [dbo].[AddressInfo](
    [Address1] [varchar](250) NULL,
    [City] [varchar](250) NULL

INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Viejas Outlet Center', N'Alpine')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Vista Marketplace', N'Alhambra')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Wade Road', N'Basingstoke Hants')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Ward Parkway Center', N'Kansas City')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Warrington Ldc Unit 25/2', N'Woolston')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Washington Square', N'Portland')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'West Park Plaza', N'Irvine')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Wharfdale Road', N'Berkshire')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'White Mountain Mall', N'Rock Springs')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Wymbush', N'Milton Keynes')

Generating Sequence number based on "City" column
SELECT (SELECT COUNT(1) FROM AddressInfo A WHERE a.City <= b.City) [Seq],B.* FROM AddressInfo B