Remove Replication

-- Dropping the transactional subscriptions

use [DBA]

exec sp_dropsubscription @publication = N'ReplicationPublicationName', @subscriber = N'KCRCSTVSQL03', @destination_db = N'DestinationDB', @article = N'all'

GO

 

-- Dropping the transactional articles

use [DBA]

exec sp_dropsubscription @publication = N'ReplicationPublicationName', @article = N'Action', @subscriber = N'all', @destination_db = N'all'

GO

 

-- Dropping the transactional publication

use [DBA]

exec sp_droppublication @publication = N'ReplicationPublicationName'

GO

 

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

SET @publicationDB = N'DBA';

SET @publication = N'ReplicationPublicationName';

 

-- Remove a transactional publication.

USE COMMON

EXEC sp_droppublication @publication = @publication;

 

-- Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

  @dbname = @publicationDB,

  @optname = N'publish',

  @value = N'false';

GO

Long Running Queries Along with Kill Statement of SPID

drop table #a

go

SELECT

      HOST_NAME               AS [System Name],

      program_name                  AS [Application Name],

      DB_NAME(database_id)    AS [DATABASE Name],

      USER_NAME(USER_ID)      AS [USER Name],

      connection_id                 AS [CONNECTION ID],

      sys.dm_exec_requests.session_id AS [CURRENT SESSION ID],

      blocking_session_id           AS [Blocking SESSION ID],

      start_time              AS [Request START TIME],

      sys.dm_exec_requests.status   AS [Status],

      command AS [Command Type],

      (SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT],

      wait_type               AS [Waiting Type],

      wait_time               AS [Waiting Duration],

      wait_resource                 AS [Waiting FOR Resource],

      sys.dm_exec_requests.transaction_id AS [TRANSACTION ID],

      percent_complete        AS [PERCENT Completed],

      estimated_completion_time     AS [Estimated COMPLETION TIME (in mili sec)],

      sys.dm_exec_requests.cpu_time       AS [CPU TIME used (in mili sec)],

      (memory_usage * 8)      AS [Memory USAGE (in KB)],

      sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)] into #a

FROM sys.dm_exec_requests

            INNER join

      sys.dm_exec_sessions

            ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id

WHERE DB_NAME(database_id) = 'DBNAME'

GO

select 'kill ' + cast([CURRENT SESSION ID] as varchar) as Statement, * from #a where [Query TEXT] like '%procedurename%'

go