Kill negative (orphan) pid SQL Server

Sometimes in sql server you will see negative pid running and blocking other processes. These are distributed transactions and sometimes they don’t complete or hang and block other processes. Negative PID can’t be killed by issuing normal kill pid command, to kill negative PID request_owner_guid is required.


USE Master;
GO

SELECT
DISTINCT(request_owner_guid)
FROM sys.dm_tran_locks
WHERE request_session_id =-2
GO

Once you have the guid, use it to kill the negative PID.

KILL 'guid'

Generate restore command for SQL Server Database backup.

This script will generate restore command for the most recent backup. This script can be used for databases in simple and full recovery model, this script ignores differential backup, which I will include in a later post.


DECLARE @databaseName sysname
DECLARE @newDatabaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
DECLARE @stats varchar(2)
DECLARE @replace char(1)
DECLARE @repCom varchar(50)
DECLARE @copyOnly bit

-- set database values
SET @databaseName = 'dbName' --- Original Database.
SET @newDatabaseName = 'newDBName' --- Restored AS
SET @stats = '5'
SET @replace = '0'
SET @copyOnly = 0

DECLARE @filename varchar(255), @physicalfile varchar(255), @comMove varchar(500),
@fullMove varchar(4000), @dbName varchar(100), @newDbName varchar(100);
SET @dbName = @databaseName;
SET @newDbName = @newDatabaseName;
SET @fullMove = '';
IF LTRIM(RTRIM(@dbName)) = LTRIM(RTRIM(@newDbName)) OR @replace = 1
BEGIN
SET @repCom = ', REPLACE This will replace database!!'
END
ELSE
SET @repCom = ''

DECLARE update_file_name CURSOR
FOR SELECT name,REPLACE(physical_name, @dbName, @newDbName) FROM sys.master_files
WHERE 1=1
AND database_id = (SELECT database_id FROM sys.databases WHERE 1=1 AND name = @dbName)
ORDER BY CASE WHEN FILE_ID = 2 THEN 1000 ELSE FILE_ID END
OPEN update_file_name
FETCH NEXT FROM update_file_name
INTO @filename, @physicalfile

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @comMove = ', ' + 'MOVE N''' + @filename + ''' TO N''' + @physicalfile + ''''
SET @fullMove = @fullMove + @comMove

FETCH NEXT FROM update_file_name INTO @filename, @physicalfile;

END

CLOSE update_file_name;
DEALLOCATE update_file_name;

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND is_copy_only = @copyOnly

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start
AND is_copy_only = @copyOnly

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @newDatabaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY' + @fullMove + ', STATS = ' + @stats + @repCom
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
AND is_copy_only = @copyOnly

UNION
SELECT backup_set_id, 'RESTORE LOG ' + @newDatabaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
AND b.type = 'L'
AND is_copy_only = @copyOnly
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @newDatabaseName + ' WITH RECOVERY'
ORDER BY backup_set_id