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