automation for listing deadlocks in sql server

Following script creates stored procedure which can be called via sql job to run every 6 hours to get detals of dedalock on server through e-mail .

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[listDeadLocks]    Script Date: 04/23/2015 02:41:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[listDeadLocks]
AS
BEGIN
DECLARE @RawLogs TABLE (id INT IDENTITY (1, 1), logdate DATETIME, processinfo NVARCHAR(50), logtext NVARCHAR(MAX))
DECLARE @tab_Deadlocklist TABLE (deadlockNo INT ,  processinfo NVARCHAR(20), logtext VARCHAR(MAX))
--CREATE INDEX IDX_TEMP ON @tab  (logtext);
DECLARE @DEADLOCKQUERIES TABLE (logtext NVARCHAR(MAX))
DECLARE @SNAME AS NVARCHAR(64), @ERRORBODY VARCHAR(MAX), @BODY VARCHAR(MAX) ,@ERRORSUBJECT NVARCHAR(256) ,@TO AS NVARCHAR(100),@ERRORBODY_shell VARCHAR(MAX)
DECLARE @found INT , @insert_flag INT , @v_deadlock_ex_count INT, @DNO INT, @rawlogs_cnt INT
DECLARE @c1_logdate datetime, @c1_processinfo NVARCHAR(50), @c1_logtext varchar(max)
DECLARE @v_currentProcessId NVARCHAR(256),@v_processed NVARCHAR(256),@Db_name NVARCHAR(100)
DECLARE @timeInHours INT, @default_threshold_value INT

SET @found = 0
SET @timeInHours = 16  --- this takes last 6 hours deadlocks from ErrorLog
SET @DNO = 1
SET @insert_flag=0
SET @v_currentProcessId=''
SET @rawlogs_cnt = 0
SET @default_threshold_value=1
SELECT GETDATE() AS CurrentStartTime

INSERT INTO @RawLogs
EXEC sp_readerrorlog 1
SET @rawlogs_cnt = @@ROWCOUNT

SELECT GETDATE() AS CurrentTimeAfterRawlogs1

INSERT INTO @RawLogs
EXEC  sp_readerrorlog
SET @rawlogs_cnt = @rawlogs_cnt + @@ROWCOUNT

SELECT GETDATE() AS CurrentTimeAfterRawlogs2
SELECT @rawlogs_cnt

IF object_id('tempdb..#Database_Exceptions') is not null
BEGIN
DROP TABLE #Database_Exceptions
END
CREATE TABLE #Database_Exceptions
(
      DBname VARCHAR(50),
      deadlock_count INT
 );

 --trying append date to the error file
--DECLARE @vstrDate AS CHAR(10)
--select @vstrDate = CONVERT(char(10), GETDATE(), 121)
--select @vstrDate

 --- DeadLock threshold counts are defined in the following file.
BEGIN TRY
BULK
INSERT #Database_Exceptions
FROM '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count_Error.txt'
)
END TRY
BEGIN CATCH
SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
        RETURN -1
END CATCH

select * from #Database_Exceptions
SELECT GETDATE() AS CurrentTimeAfterFileRead

DECLARE c1 CURSOR FOR
SELECT logdate,processinfo,logtext FROM @RawLogs
WHERE
logdate >= dateadd(hh, -1*@timeInHours, getdate())
and processinfo not in ('Backup','Logon')
ORDER BY processinfo,id asc;

OPEN c1
FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext

WHILE @@FETCH_STATUS = 0
BEGIN
     IF @c1_logtext ='deadlock-list' OR @insert_flag=1
     BEGIN
IF @v_currentProcessId=@c1_processinfo OR @v_currentProcessId=''
BEGIN
   SET @v_currentProcessId=@c1_processinfo
   INSERT INTO @tab_DeadLockList VALUES (@DNO,  @v_currentProcessId, @c1_logtext)
   SET @insert_flag=1
END
ELSE
BEGIN
SET @insert_flag=0
--SELECT @v_currentProcessId
SET @v_currentProcessId=''
SET @DNO = @DNO + 1
END
END
     FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext
     END
CLOSE c1
DEALLOCATE c1

SELECT GETDATE() AS TimeStampAfterC1
-- total deadlocks
--select @DNO-1
DECLARE @c2_deadId INT
DECLARE @c2_deadlocktext varchar(max)
DECLARE @c2_currentDeadId INT
DECLARE @c2_concattext VARCHAR(MAX)
set @c2_concattext= ''
DECLARE @v_cnt int
set @v_cnt=0
set @c2_currentDeadId=0
DECLARE c2 CURSOR FOR
SELECT deadlockNo ,logtext
      from @tab_Deadlocklist
  where --logtext like '%frame procname=marqueestats.dbo.Dispatcher_UpdateServer li%'
    logtext not like '%deadlock-list%'
and logtext not like '%deadlock victim%'
and logtext not like '%process-list%'
and logtext not like '%executionStack%'
and logtext not like '%process id%'
and logtext not like '%inputbuf%'
and logtext not like '%owner id%'
and logtext not like '%waiter-list%'
and logtext not like '%waiter id%'
and logtext not like '%resource-list%'
and logtext not like '%owner-list%'
and logtext not like '%pagelock fileid%'
and logtext not like '%exchangeEvent%'
and logtext not like '%==%'
-- added on 17/09/12
and logtext not like '--%'
and logtext not like 'keylock %'
ORDER BY deadlockNo ASC


OPEN c2
FETCH NEXT FROM c2 INTO @c2_deadId, @c2_deadlocktext

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @c2_currentDeadId=0 or @c2_currentDeadId=@c2_deadId
            BEGIN
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure:  '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'.  '+ CHAR(13)+ 'Query: '
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
            END
     ELSE
BEGIN
   --- insert the contactenated queries into a temp table
INSERT INTO @DEADLOCKQUERIES  SELECT  @c2_concattext

SET @c2_concattext=''
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure:  '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'.  '+ CHAR(13)+ 'Query: '
--SET @db_name=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('.',@c2_deadlocktext,1)-1)
--SELECT @Db_name
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
END
   
    FETCH NEXT FROM c2
     INTO @c2_deadId, @c2_deadlocktext
END
INSERT INTO @DEADLOCKQUERIES SELECT  @c2_concattext

CLOSE c2
DEALLOCATE c2

SELECT GETDATE() AS TimeStampAfterC2

--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--GROUP BY substring(logtext,1,500)
----HAVING COUNT(*)>1

SET @SNAME=(SELECT @@SERVERNAME)

SET @TO='e-mail@domain.com;e-mail-group@domain.com;'

--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
--GROUP BY substring(logtext,1,500)
--HAVING COUNT(*)>1

DECLARE C3 CURSOR FOR
SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
GROUP BY substring(logtext,1,500)
HAVING COUNT(*)>=@default_threshold_value --- min dead lock count changed to 50

OPEN c3
FETCH NEXT FROM c3 INTO @v_cnt, @BODY

WHILE @@FETCH_STATUS = 0
BEGIN
     SET @found =1
     SET @ERRORBODY = 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s).   ' + CHAR(13) +@BODY
     --SET @ERRORBODY_shell= 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s).`r`n ' +@BODY
     SET @Db_name=SUBSTRING(@BODY,12,CHARINDEX('.',@BODY,1)-12)
     SET @ERRORSUBJECT = @SNAME + ': DeadLocks Found in ' + UPPER(LTRIM(@Db_name)) + ' database.'
IF UPPER(LTRIM(RTRIM(@Db_name))) not like '%ADHOC%' AND upper(@Db_name) not like  '%MSDB%'

BEGIN
--SELECT  * FROM #Database_Exceptions
--WHERE UPPER(DBname)=  UPPER(@Db_name)
--SELECT  TOP 1 deadlock_count FROM #Database_Exceptions ---- to remove any duplicate entried in the file.
--WHERE UPPER(DBname)=  UPPER(LTRIM(RTRIM(@Db_name)))

set @v_deadlock_ex_count =  (SELECT  TOP 1 deadlock_count FROM #Database_Exceptions
WHERE UPPER(DBname)=  UPPER(LTRIM(RTRIM(@Db_name))) )

set @v_deadlock_ex_count = ISNULL(@v_deadlock_ex_count,@default_threshold_value)

SELECT UPPER(LTRIM(RTRIM(@Db_name))),@v_cnt, @v_deadlock_ex_count

IF  @v_cnt > @v_deadlock_ex_count
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Standard Profile',
@recipients = @TO,
@body = @ERRORBODY,
@subject = @ERRORSUBJECT ;

END
    END
   
     FETCH NEXT FROM C3
     INTO @v_cnt, @BODY
END

CLOSE c3
DEALLOCATE c3
SELECT GETDATE() AS TimeStampAfterC3
END



GO

on shared path deadlock_db_exception_count , edit the text file and type
database_name,<deadlock count>
EX: db1,3  -- 3 deadlcoks on db1 database


example e-mail :

SQL Configuration Manager WMI issue

If you see this specific issue while launching SQL Configuration Manager, you can follow the workaround specified in this blog post




Workaround:
Open Command Windows as Elevated administrator user
cd %ProgramFiles(x86)%\Microsoft SQL Server\110\Shared
mofcomp sqlmgmproviderxpsp2up.mof



Re launch SQL Configuration manager.

automating rebuild or reorg index on specific large tables of the database in SQL server which are really fragmented

 Following Script can be helpful to automate the Rebuild/Reorg Index on tables with in database which are huge and really fragmented.

Scenario :  While working with few clients I observed there was huge database with large table sizes and the indexes of the table were not much fragmented , but still there was maintenance plan configured to run rebuild index and update stats on all the tables of the database on every weekend

observations : performing index rebuild and update stats can cause performance issue (like CPU being pegged continuously) and log file growing huge.

Thus it makes sense to run the rebuild/reorg index only if its needed

following code serve the purpose :

Below code make use of dmv to identify fragmentation on the indexes with in the tables of the database , and perform either rebuild or reorg index only on affected indexes , later ran update stats only on the tables whose indexes were rebuild/reorganized

USE [provide dbname where table indexes are fragmented]

declare @dbid smallint = DB_ID();

declare @IndexInfo table (
  SchemaName SYSNAME NOT NULL,
  TableName SYSNAME NOT NULL,
  IndexName SYSNAME NOT NULL,
  IndexPageCount BIGINT NOT NULL,
  FragmentationPercent FLOAT NOT NULL,
  CurrentFillFactor TINYINT NOT NULL,
  StatsName NVARCHAR(256) NULL,
  StatsLastUpdate DATETIME2 NULL,
  StatsModificationCount BIGINT NULL,
  PRIMARY KEY (SchemaName, TableName, IndexName)
);

insert @IndexInfo (SchemaName, TableName, IndexName, IndexPageCount, FragmentationPercent, CurrentFillFactor, StatsName, StatsLastUpdate, StatsModificationCount)
select ss.name, so.name, si.name, ips.page_count, ips.avg_fragmentation_in_percent, si.fill_factor, st.name, stmeta.last_updated, stmeta.modification_counter
from sys.indexes si
join sys.[objects] so
  on so.[object_id] = si.[object_id]
join sys.schemas ss
  on ss.[schema_id] = so.[schema_id]
join sys.dm_db_index_physical_stats(@dbid, NULL, NULL, DEFAULT, DEFAULT) ips
on ips.database_id = @dbid
and ips.[object_id] = so.[object_id]
and ips.index_id = si.index_id
left join sys.stats st
  on st.[object_id] = so.[object_id]
  and st.[stats_id] = si.[index_id]
  and st.[is_temporary] = 0
  and st.[auto_created] = 0
  and st.[user_created] = 0
outer apply sys.dm_db_stats_properties(so.[object_id], st.stats_id) stmeta
where si.type_desc in ('clustered', 'nonclustered')
and so.is_ms_shipped = 0
  and ips.alloc_unit_type_desc = 'IN_ROW_DATA'
order by ips.page_count desc;

declare @line nvarchar(max) = null;
declare cur cursor local static for
select t.MaintenanceCommand
from (
  select 1 [Ordinal], p.IndexPageCount,
    N'SET QUOTED_IDENTIFIER ON;ALTER INDEX [' + p.IndexName + N'] ON [' + p.SchemaName + N'].[' + p.TableName + N'] ' +
    case when p.FragmentationPercent >= 30.0 then N'REBUILD PARTITION=ALL  WITH (ONLINE = ON);' else N'REORGANIZE PARTITION=ALL;' end + N'' [MaintenanceCommand]
  from @IndexInfo p
  where p.IndexPageCount >= 50
    and p.FragmentationPercent >= 5.0
union all
  select 2 [Ordinal], p.IndexPageCount,
    N'UPDATE STATISTICS [' + p.SchemaName + N'].[' + p.TableName + N'] [' + p.IndexName + N'] WITH FULLSCAN;' [MaintenanceCommand]
  from @IndexInfo p
  where p.StatsModificationCount > 100
    and (p.IndexPageCount < 50
      or p.FragmentationPercent < 30.0)
    and ((p.StatsModificationCount >= (p.IndexPageCount / 3))
      or datediff(day, p.StatsLastUpdate, sysdatetime()) >= 14)
) t
order by t.Ordinal asc, t.IndexPageCount desc;


open cur;
fetch next from cur into @line;
while @@fetch_status = 0 begin

exec sp_executesql @line

fetch next from cur into @line;
end;

close cur;
deallocate cur;

unable to shrink log file of database in SQL server due to log_reuse_wait_desc shows replication

Issue : When you try to shrink the database log file , you are unable to shrink

try to investigate the column select log_reuse_wait_desc,* from sys.databases

if the column shows replication for the referred database name , below are the observations to be made

1, Is replication really configured on the database (the reason i said this is because some times database is restored from backup file , and the backup file is of the database on source server where replication is configured )

2, replication is configured (i.e database is publisher and there observes to be replication issue in launch replication monitor for the specified publication)

3, CDC (change data capture) is enabled for the database and the job cdc.dbname_capture isn't running or in an bad state


Case I :  The database log_reuse_wait_desc column shows replication and there wasn't any replication configured on the server

later I observed the database was restored form the backup file , which was of the database (db_a) which has replication configured on server a (servera is source server where db_a database exists and acts as a publisher)

Steps taken to fix the issue :

Use [provide dbname where the issue is observed]

EXEC sp_repldone @xactid = NULL,
  @xact_segno = NULL, @numtrans = 0,
  @time= 0, @reset = 1

sp_removedbreplication [provide dbname where the issue is observed]

Case II : Database was configured with CDC (Change data Capture) and observed job status through sql server agent suggests cdc.dbname_capture jobs was idle and last run outcome was failed.

restarted the job and  soon available free space in log file of database increased (thus shrinking of log file is possible now)

note : cdc.dbname_capture job is expected to be running continuously or at specified intervals , based up on the requirement or business needs for CDC configuration

** for checking database log file free space and shrinking the log file to release back the space to OS

Run the following code to see the free space of the database files (all data and log files might appears)

USE  [provide dbname where the issue is observed]

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,50),
FILENAME = left(a.FILENAME,100)
from
sys.sysfiles a

** If above code shows log file of database has more than 60% free space , proceed with shrinking log file by code

USE  [provide dbname where the issue is observed]
DBCC shrinkfile ('logical_name_of_file',size_in_MB)

--size_in_MB is the amount of space that to be left in the file after shrink finishes

list long running queries in sql server through automation

The following script can help DBA to list the long running queries on SQL server .

1, First creates a stored procedure with name spgetopentransactions
2, uses the stored procedure to get the query information which are running on sql from long time

ex:-  EXEC msdb.dbo.spGetOpenTransactions @HoursBehind = 6

when above statement is run DBA would get e-mail to the recipient mentioned in recipients parameter of sp_send_dbmail procedure.

The mail would be in HTML format and would have information about all the queries running on sql server from last 6 hours and which are still in active state i.e session currently running



USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[spGetOpenTransactions]    Script Date: 4/7/2015 12:51:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--EXEC msdb.dbo.spGetOpenTransactions @HoursBehind = 7
CREATE PROC [dbo].[spGetOpenTransactions]
@HoursBehind INT
AS
-- This proc gets a list of processes with active transactions in excess of @HoursBehind hours

Declare @ActiveProcess table (
session_id int NOT NULL,
kpid int null,
cmd varchar(2000) null,
open_tran tinyint,
lastwaittype varchar(2000) null,
waitresource varchar(2000) null,
blocked int,
sql_handle varbinary (4000) null,
stmt_start int,
stmt_end int,
waittime int,
physical_io bigint,
memusage int
)

-- Grab current processes
insert into @ActiveProcess
select
distinct spid,kpid,cmd,open_tran,lastwaittype,waitresource,blocked,[sql_handle],stmt_start,stmt_end,waittime,physical_io,memusage
from sys.sysprocesses b with (nolock)
where (open_tran>0 or blocked >0) and spid <> @@spid or spid in (select blocked from sys.sysprocesses b with (nolock) where blocked >0)

-- select * from @ActiveProcess

select
s.session_id as SessionID,
p.kpid as Kpid,
blocked as BlockingSession,
SUBSTRING(qt.text, (p.stmt_start/2)+1,
((CASE p.stmt_end
WHEN -1 THEN DATALENGTH(qt.text)
WHEN 0 THEN DATALENGTH(qt.text)
ELSE p.stmt_end
END - p.stmt_start)/2) + 1) AS QueryExecuted,
datediff(ss, COALESCE(t.transaction_begin_time,s.last_request_end_time,r.start_time), getdate()) as SessionIdleSec,
s.host_name as HostName,
convert(varchar(2000),s.program_name) as ProgramName,
s.login_name as LoginName,
convert(varchar(2000),s.status) as SessionStatus,
convert(varchar(2000),p.cmd) as Command,
convert(varchar(2000),coalesce(r.last_wait_type,p.lastwaittype)) as WaitType,
convert(varchar(2000),coalesce(r.wait_resource, p.waitresource)) as WaitResource,
p.waittime/1000 as WaitTimeSec,
convert(int, p.open_tran) as OpenTransactionCount,
COALESCE(t.transaction_begin_time,s.last_request_end_time,r.start_time) as TransactionBeginTime,
case
when t.transaction_type <> 4 then
case t.transaction_state
when 0 then 'Invalid'
when 1 then 'Initialized'
when 2 then 'Active'
when 3 then 'Ended'
when 4 then 'Commit Started'
when 5 then 'Prepared'
when 6 then 'Committed'
when 7 then 'Rolling Back'
when 8 then 'Rolled Back'
end

when t.transaction_type <> 4 then
case t.dtc_state
when 1 then 'Active'
when 2 then 'Prepared'
when 3 then 'Committed'
when 4 then 'Aborted'
when 5 then 'Recovered'
end
else
'Not Active'

end as TransactionStatus,
CASE
WHEN coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 0 THEN 'Unspecified'
WHEN coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 1 THEN 'ReadUncommitted'
WHEN coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 2 THEN 'ReadCommitted'
WHEN coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 3 THEN 'Repeatable'
WHEN coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 4 THEN 'Serializable'
WHEN coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 5 THEN 'Snapshot'
END AS TransactionIsolationLevel,
case st.is_user_transaction
when 0 then 'User Transaction'
when 1 then 'System Transaction'
end as IsUserTransaction,
case t.transaction_type
when 1 then 'Read/write transaction'
when 2 then 'Read-only transaction'
when 3 then 'System transaction'
when 4 then 'Distributed transaction'
end as TransactionType,
coalesce(r.transaction_id, st.transaction_id) as TransactionId,
case t.transaction_state
when 0 then 'The transaction has not been completely initialized yet'
when 1 then 'The transaction has been initialized but has not started'
when 2 then 'The transaction is active'
when 3 then 'The transaction has ended. This is used for read-only transactions'
when 4 then 'The commit process has been initiated on the distributed transaction'
when 5 then 'The transaction is in a prepared state and waiting resolution'
when 6 then 'The transaction has been committed'
when 7 then 'The transaction is being rolled back'
when 8 then 'The transaction has been rolled back'
end as TransactionState,
st.enlist_count as EnlistCount,
r.percent_complete as PercentComplete,
r.estimated_completion_time as EstimatedCompletionTime,
r.cpu_time/1000 as CpuConsumedSec,
r.total_elapsed_time/1000 as TimeConsumedSec,
coalesce((r.reads+r.writes),p.physical_io) as PhysicalIO,
coalesce(granted_query_memory,p.memusage) as MemUsage,
s.last_request_start_time as LastRequestStartTime,
s.last_request_end_time as LastRequestEndTime
INTO #Temp
from @ActiveProcess p
left join sys.dm_exec_sessions s with (nolock) on s.session_id = p.session_id
left join sys.dm_exec_requests r with (nolock) on s.session_id = r.session_id
left join sys.dm_tran_session_transactions st with (nolock) on s.session_id = st.session_id
left join sys.dm_tran_active_transactions t with (nolock)on t.transaction_id = st.transaction_id
outer apply sys.dm_exec_sql_text(p.sql_handle) as qt
WHERE DATEDIFF(HOUR,s.last_request_end_time,GETDATE()) > @HoursBehind
IF  @@ROWCOUNT > 0
BEGIN


DECLARE @xml NVARCHAR(MAX)
DECLARE @body VARCHAR(4000)


SET @xml = CAST(( SELECT
SessionID AS 'td','',
ISNULL(ProgramName,'') AS 'td','',
ISNULL(TransactionBeginTime,'') AS 'td','',
ISNULL(TransactionType,'') AS 'td','',
ISNULL(TransactionState,'') AS 'td','',
ISNULL(TransactionStatus,'') AS 'td','',
ISNULL(QueryExecuted,'') AS 'td','',
ISNULL(HostName,'') AS 'td',''

FROM  #Temp ORDER BY SessionID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @body ='<html><body><H3>Sessions running against ' + @@SERVERNAME + ' for more than ' + CONVERT(VARCHAR,@HoursBehind) + ' hours</H3>
<table border = 1>
<tr>
<th> SessionID </th>
<th> ProgramName </th>
<th> TransactionBeginTime </th>
<th> TransactionType </th>
<th> TransactionState </th>
<th> TransactionStatus </th>
<th> QueryExecuted </th>
<th> HostName </th>
</tr>'  


SET @body = CONVERT(VARCHAR(4000),@body + @xml +'</table></body></html>')



EXEC msdb.dbo.sp_send_dbmail
@profile_name = ' standard profile', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'someone@domain.com', -- replace with your email address
@subject = 'Transactions running against server for more than n hours' ;


DROP TABLE #Temp
 END


GO

fix error a call to LogonUserW failed with error code 1385 in sql server while accessing xp_cmdshell

Issue : when you try to run xp_cmdshell on sql server , you get error message.

An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'

Observations :

1, The login which is trying to run xp_cmdshell has EXEC permission on the strored procedure xp_cmdshell
2, when we grant the login with sysadmin privilege , the login can run xp_cmdshell without any issues. however when sysadmin permission is revoked , the login faces belwo error message while using xp_cmdshell

"a call to LogonUserW failed with error code 1385"

steps for  Fixing the issue :

1, open local security policy on the computer which has SQL server installed on it.

ex: if login was trying to connect to serverA and run the query xp_cmdshell , which resulted in error message. then connect to serverA and open local security Policy.

2,  Navigate to Security Settings > Local policies > User rights assignment

3, Double click on policy 'Log on as a batch job'  Add user or group which needs to run xp_cmdshell command in sql . apply the settings

performing above steps in my case solved the issue

The login from an untrusted domain and cannot be used with Windows authentication. In SQl server

Issue : You may fine below  messages in error log of SQL server , this is usually not an issue with SQl but rather windows active directory permisison issue

Message :

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: ipaddress]

Scenario:

DomainA and DomainB are the 2 domains . your Sql server is in DomainB .

A group called DomainB\windows_group exists which has access to sql server database in DomainB.

the group consists of users like DomainA\user1 , DomainA\users2 , DomainB\user3 .

the user3 in the group can access sql server without any issues, however user1 and user2 gets below error messages when trying to login to sql server

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Fixing of issue :

In the Active Directory identify the computer name on which SQl server is installed

2, right click on computer name and click on security page on the pop up menu

3, Add the group DomianB\windows_group and grant read and allowed to authenticate permission for group

DBA notified before tempdb fills on SQL Server

Below is the script which would create an Alert,Job to  monitor Tempdb usage

script comprises of following parts :-

1,  create a job tempdb full alert
2,  create an alert which will trigger the job when condition is met i.e (tempdb files is about to reach its limits , and drive on which the file resides is having 10% free space of teh total alloted space on Drive )
3, you can uncomment notification section if the DBA needs to be notified of issue via e-mail

tempdb full alert job functioning :

a, checks the drive where temdp file resides, uses power shell script to get total allotted space and free space details of the drive
b, extract the active transactions running against tempdb (or) dbcc results of the open transactions sessions held on tempdb
c, pull the information in html format and log the information in event viewer

(you can make use of  sp_send_dbmail stored procedure to get the extracted information, rather than logging on to event viewer)


scripts to perform the activity :-

If exists (select * from msdb.dbo.sysjobs where name='Tempdb_Full_alert')  select 'job alreday exist'
else

begin


USE [msdb]

DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'Tempdb_Full_alert',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'Raise RPD when tempdb log file percent used is more than 90 and free space on the drive is less than 10 percent of total space',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Tempdb_Full_alert', @server_name = @@servername

USE [msdb]

EXEC msdb.dbo.sp_add_jobstep @job_name=N'Tempdb_Full_alert', @step_name=N'Raise Rpd',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
set concat_null_yields_null off
declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = ''powershell.exe -c "Get-WmiObject -ComputerName '' + QUOTENAME(@svrName,'''''''') + '' -Class Win32_Volume -Filter ''''DriveType = 3'''' | select name,capacity,freespace | foreach{$_.name+''''|''''+$_.capacity/1048576+''''%''''+$_.freespace/1048576+''''*''''}"''
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql

/*
--script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(''|'',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''|'',line)+1,
      (CHARINDEX(''%'',line) -1)-CHARINDEX(''|'',line)) )) as Float)/1024,0) as ''capacity(GB)''
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''%'',line)+1,
      (CHARINDEX(''*'',line) -1)-CHARINDEX(''%'',line)) )) as Float) /1024 ,0)as ''freespace(GB)''
from #output
where line like ''[A-Z][:]%''
order by drivename
*/

--script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(''|'',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''|'',line)+1,
      (CHARINDEX(''%'',line) -1)-CHARINDEX(''|'',line)) )) as Float),0) as capacity_MB
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''%'',line)+1,
      (CHARINDEX(''*'',line) -1)-CHARINDEX(''%'',line)) )) as Float),0) as freespace_MB
into #temp
from #output
where line like ''[A-Z][:]%''



declare @drive varchar (3)
set @drive= (select filename from sys.sysaltfiles where dbid=2 and filename like ''%.ldf%'')

if exists (select * from #temp where drivename=@drive and freespace_MB < ( capacity_MB*0.1) )

begin

DECLARE @Body VARCHAR(8000) =''<br>              <br> Tempdb running out of space . Following are the transactions running against tempdb <br>   <br>              <table border=1>                     <tr>                           <td>session_id</td>                           <td>transaction_begin_time</td> <td>program_name</td>  <td>host_name</td>                           <td>login_name</td>                           <td>status</td>                           <td>Query</td>                     <tr>''
 print ''dmvs''              
select st.session_id,st.transaction_id,at.name,at.transaction_begin_time,db_name(dt.database_id) Dbname,es.login_time,es.host_name,es.program_name,es.login_name,es.status ,syp.last_batch,est.text query
into #temptable from sys.dm_tran_session_transactions st  join sys.dm_tran_active_transactions at on st.transaction_id=at.transaction_id
join sys.dm_tran_database_transactions dt on dt.transaction_id=st.transaction_id join sys.dm_exec_sessions es on st.session_id=es.session_id
join sys.sysprocesses syp on syp.spid=st.session_id cross apply sys.dm_exec_sql_text(syp.sql_handle) est
where dt.database_id=2 and st.session_id<>@@spid

if @@ROWCOUNT>0
begin

DECLARE  @sessionid int,@txtbegintime datetime,@programname nvarchar(128),@hostname nvarchar(128),@login nvarchar(128),@status nvarchar(30), @SQLTEXT nVARCHAR(MAX)
     DECLARE tempdb_Cursor CURSOR LOCAL FOR
              SELECT session_id,transaction_begin_time,program_name,host_name,login_name,status,Query FROM #temptable
              OPEN tempdb_Cursor;
              FETCH NEXT FROM tempdb_Cursor INTO @sessionid,@txtbegintime,@programname,@hostname,@login,@status,@SQLTEXT;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@sessionid) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@txtbegintime) + ''</td>''
  SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR(128),@programname) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@hostname) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@login) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@status) + ''</td>''
  SET @Body = @Body + ''<td>'' +CONVERT(NVARCHAR(max),@sqltext) +''</td>''

                           SET @Body = @Body + ''</tr>''
                       FETCH NEXT FROM tempdb_Cursor INTO @sessionid,@txtbegintime,@programname,@hostname,@login,@status,@SQLTEXT;;
                 END;
              CLOSE tempdb_Cursor;
              DEALLOCATE tempdb_Cursor;
 drop table #temptable
     SET @BODY = @BODY + ''      </tr>              </table>              <br>              <br> ''

end
ELSE
begin


 -- Create the temporary table to accept the results.

CREATE TABLE #OpenTranStatus (
   ActiveTransaction varchar(25),
   Details sql_variant
   );
-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
   EXEC (''DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS'');


print ''dbcc''
select er.session_id,blocking_session_id,er.status,start_time as transaction_begin_time,
wait_type,wait_resource,wait_time,last_wait_type,substring(et.text,(er.statement_start_offset/2)+1,
((case er.statement_end_offset
when -1 then datalength(et.text)
when 0 then datalength(et.text)
else er.statement_end_offset
end - er.statement_start_offset)/2)+1 ) as query,es.login_name,es.host_name,es.program_name into #temptable_dbcc  from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) et left join sys.dm_exec_sessions es on er.session_id=es.session_id  where er.session_id in (select convert(int,details,0) from #OpenTranStatus where ActiveTransaction=''OLDACT_SPID'')

if @@ROWCOUNT>0 begin

DECLARE  @sessionid_dbcc int,@txtbegintime_dbcc datetime,@programname_dbcc nvarchar(128),@hostname_dbcc nvarchar(128),@login_dbcc nvarchar(128),@status_dbcc nvarchar(30), @SQLTEXT_dbcc nVARCHAR(MAX)
     DECLARE tempdb_Cursor_dbcc CURSOR LOCAL FOR
              SELECT session_id,transaction_begin_time,program_name,host_name,login_name,status,Query FROM #temptable_dbcc
 OPEN tempdb_Cursor_dbcc;
              FETCH NEXT FROM tempdb_Cursor_dbcc INTO @sessionid_dbcc,@txtbegintime_dbcc,@programname_dbcc,@hostname_dbcc,@login_dbcc,@status_dbcc,@SQLTEXT_dbcc;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@sessionid_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@txtbegintime_dbcc) + ''</td>''
  SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR(128),@programname_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@hostname_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@login_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@status_dbcc) + ''</td>''
  SET @Body = @Body + ''<td>'' +CONVERT(NVARCHAR(max),@sqltext_dbcc) +''</td>''

                           SET @Body = @Body + ''</tr>''
                       FETCH NEXT FROM tempdb_Cursor_dbcc INTO @sessionid_dbcc,@txtbegintime_dbcc,@programname_dbcc,@hostname_dbcc,@login_dbcc,@status_dbcc,@SQLTEXT_dbcc;;
                 END;
              CLOSE tempdb_Cursor_dbcc;
              DEALLOCATE tempdb_Cursor_dbcc;
 drop table #temptable_dbcc
     SET @BODY = @BODY + ''      </tr>              </table>              <br>              <br> ''

end
ELSE
begin
print ''dbcc noinfo''
select spid as session_id,last_batch as transaction_begin_time,program_name,hostname as host_name,loginame as login_name,status,cmd as query into #temptable_dbcc_noinfo from sys.sysprocesses where spid=(select convert(int,details,0) from #OpenTranStatus where ActiveTransaction=''OLDACT_SPID'')
DECLARE  @sessionid_dbcc_noinfo int,@txtbegintime_dbcc_noinfo datetime,@programname_dbcc_noinfo nvarchar(128),@hostname_dbcc_noinfo nvarchar(128),@login_dbcc_noinfo nvarchar(128),@status_dbcc_noinfo nvarchar(30), @SQLTEXT_dbcc_noinfo nVARCHAR(MAX)
     DECLARE tempdb_Cursor_dbcc_noinfo CURSOR LOCAL FOR
              SELECT session_id,transaction_begin_time,program_name,host_name,login_name,status,Query FROM #temptable_dbcc_noinfo
 OPEN tempdb_Cursor_dbcc_noinfo;
              FETCH NEXT FROM tempdb_Cursor_dbcc_noinfo INTO @sessionid_dbcc_noinfo,@txtbegintime_dbcc_noinfo,@programname_dbcc_noinfo,@hostname_dbcc_noinfo,@login_dbcc_noinfo,@status_dbcc_noinfo,@SQLTEXT_dbcc_noinfo;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@sessionid_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@txtbegintime_dbcc_noinfo) + ''</td>''
  SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR(128),@programname_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@hostname_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@login_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@status_dbcc_noinfo) + ''</td>''
  SET @Body = @Body + ''<td>'' +CONVERT(NVARCHAR(max),@sqltext_dbcc_noinfo) +''</td>''

                           SET @Body = @Body + ''</tr>''
                       FETCH NEXT FROM tempdb_Cursor_dbcc_noinfo INTO @sessionid_dbcc_noinfo,@txtbegintime_dbcc_noinfo,@programname_dbcc_noinfo,@hostname_dbcc_noinfo,@login_dbcc_noinfo,@status_dbcc_noinfo,@SQLTEXT_dbcc_noinfo;;
                 END;
              CLOSE tempdb_Cursor_dbcc_noinfo;
              DEALLOCATE tempdb_Cursor_dbcc_noinfo;
 drop table #temptable_dbcc_noinfo
     SET @BODY = @BODY + ''      </tr>              </table>              <br>              <br> ''


end

drop table #OpenTranStatus

end

EXEC master.dbo.xp_logevent 77007,@Body,INFORMATIONAL  
/*use sp_send_dbmail above incase mail needs to be received to DBA rather than logging in event viewer*/

 end


--script to drop the temporary table
drop table #temp
drop table #output




',
@database_name=N'tempdb',
@flags=0


USE [msdb]

EXEC msdb.dbo.sp_update_job @job_name=N'Tempdb_Full_alert',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'Raise RPD when tempdb log file percent used is more than 90 and free space on the drive is less than 10 percent of total space',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''




USE [msdb]

declare @unqjobid uniqueidentifier
set @unqjobid=(select job_id from msdb.dbo.sysjobs where name='Tempdb_Full_alert')

EXEC msdb.dbo.sp_add_alert @name=N'Tempdb Log File Percent Used',
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@performance_condition=N'SQLServer:Databases|Percent Log Used|tempdb|>|90',
@job_id=@unqjobid

/* uncomment below part if notification to team via email is required

if Exists (select name from msdb.dbo.sysoperators where email_address='someemail@domain.com')

begin

Declare @sqloperator  as varchar(128)
set @sqloperator=(select name from msdb.dbo.sysoperators where email_address='someemail@domain.com')
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb Log File Percent Used', @operator_name=@sqloperator, @notification_method = 1

end

*/

end

Automating restores in sql server

Automating fixing of logins,users , orphaned users in sql .

Scenario : -  After database restores from source to destination , most of the time we face issues of orphaned users and logins , following script can be helpful in fixing the issue

  scripts to automate the process

it comprises of 5 steps

1, save logins prior to restore
2, perform restore of database
3, extract   logins info post restoration of database
5, create users if not existing,map users , fix orphaned users (usually SQL logins)

--save logins
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id =  OBJECT_ID(N'tempdb.dbo.prior_restore_dbname') )
DROP TABLE tempdb.dbo.prior_restore_dbname
GO
create table tempdb.dbo.prior_restore_dbname
 (UserName sysname null,
 RoleName sysname null,
 LoginName varchar(80) null,
 DefDBName sysname null,defschemaname sysname null,
 UserID bigint null ,
 SID Bigint null)

 INSERT INTO tempdb.dbo.prior_restore_dbname EXEC SP_HELPUSER

-- Perform restoration of Database

--post restore logins
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.post_restore_dbname') AND type in (N'U'))
DROP TABLE tempdb.dbo.post_restore_dbname
GO
create table tempdb.dbo.post_restore_dbname
 (UserName sysname null,
 RoleName sysname null,
 LoginName varchar(80) null,
 DefDBName sysname null,defschemaname sysname null,
 UserID bigint null ,
 SID Bigint null)

 INSERT INTO tempdb.dbo.post_restore_dbname EXEC SP_HELPUSER

--create users
DECLARE @usercreation VARCHAR(8000)
 if exists               
 (select distinct pr.username 
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null)

begin
     
DECLARE  @login nvarchar(max)
       DECLARE crteuser_Cursor CURSOR LOCAL FOR
               select distinct pr.username --,pr.rolename,pr.loginname,po.username,po.rolename,po.loginname
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null and pr.rolename<>'public'
              OPEN crteuser_Cursor;
              FETCH NEXT FROM crteuser_Cursor INTO @login
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @usercreation = 'create user ['+@login+'] for login ['+@login+']'
                           exec (@usercreation)
    
                       FETCH NEXT FROM crteuser_Cursor INTO @login
                 END;
              CLOSE crteuser_Cursor;
              DEALLOCATE crteuser_Cursor;
             
              end
             
             
--map users
DECLARE @usermapping VARCHAR(8000)
 if exists               
 (select pr.username ,pr.rolename --,pr.loginname,po.username,po.rolename,po.loginname
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null)

begin
     
DECLARE  @user nvarchar(100) , @role nvarchar(100)
       DECLARE maplogin_Cursor CURSOR LOCAL FOR
               select pr.username ,pr.rolename --,pr.loginname,po.username,po.rolename,po.loginname
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null and pr.rolename<>'public'
              OPEN maplogin_Cursor;
              FETCH NEXT FROM maplogin_Cursor INTO @user , @role
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @usermapping = 'EXEC sp_addrolemember '''+@role+''' , '''+@user+''''
                           exec (@usermapping)
    
                       FETCH NEXT FROM maplogin_Cursor INTO @user , @role
                 END;
              CLOSE maplogin_Cursor;
              DEALLOCATE maplogin_Cursor;
             
              end
-- fix orphan users
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.orphanuser_dbname') AND type in (N'U'))
DROP TABLE tempdb.dbo.orphanuser_dbname
GO
create table tempdb.dbo.orphanuser_dbname
( username  varchar(80) , SID  bigint null)
insert into tempdb.dbo.orphanuser_dbname exec sp_change_users_login 'report'
--orphan users
DECLARE @fixorphan VARCHAR(8000)
 if exists               
 (select orp.username from tempdb.dbo.orphanuser_dbname orp  left join tempdb.dbo.prior_restore_dbname pr  on pr.UserName=orp.username where pr.LoginName is not null)

begin
     
DECLARE  @orphuser nvarchar(max)
       DECLARE fixorphan_Cursor CURSOR LOCAL FOR
               select orp.username from tempdb.dbo.orphanuser_dbname orp  left join tempdb.dbo.prior_restore_dbname pr  on pr.UserName=orp.username where pr.LoginName is not null
              OPEN fixorphan_Cursor;
              FETCH NEXT FROM fixorphan_Cursor INTO @orphuser
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @fixorphan = 'EXEC sp_change_users_login ''update_one'','''+@orphuser+''','''+@orphuser+''''
                           exec (@fixorphan)
    
                       FETCH NEXT FROM fixorphan_Cursor INTO @orphuser
                 END;
              CLOSE fixorphan_Cursor;
              DEALLOCATE fixorphan_Cursor;
             
              end