How to find trace or log information in sql server ?

For tracking the database for any incidents like droping any objects and event monitoring.

Starting the Trace
EXEC SP_Configure ‘default trace enabled’,1
RECONFIGURE

Stopping the Trace
EXEC SP_configure ‘default trace enabled’,1
RECONFIGURE

To find the trace information for any sql server table

select * from sys.traces where id =1

How to find trace or log information in sql server ? was last modified: October 14th, 2017 by Justin Antony

Find SSRS Subscription Error

Find SSRS Subscription Error

SSRS subscription error details can be fetched from the system table using the following code.

SELECT 
*
FROM 
DBO.SUBSCRIPTIONS AS S
INNER JOIN 
DBO.REPORTSCHEDULE RS ON S.SUBSCRIPTIONID = RS.SUBSCRIPTIONID
INNER JOIN 
DBO.[CATALOG] AS C ON C.ITEMID = S.REPORT_OID
INNER JOIN 
DBO.[USERS] U ON C.CREATEDBYID = U.USERID
INNER JOIN 
DBO.SCHEDULE SC  ON RS.SCHEDULEID = SC.SCHEDULEID
INNER JOIN 
MSDB.DBO.SYSJOBS SJ  ON CAST(SC.SCHEDULEID AS SYSNAME) = SJ.NAME
WHERE
S.LASTSTATUS LIKE '%FAIL%' 
OR S.LASTSTATUS LIKE '%PENDING%'
Find SSRS Subscription Error was last modified: October 1st, 2017 by Justin Antony

Database Transaction lock information

Transaction lock

View the Transaction lock complete information using the following TSQL code.

select * 
FROM    sys.dm_tran_locks TL
LEFT JOIN sys.partitions SP 
ON SP.hobt_id = TL.resource_associated_entity_id
LEFT JOIN sys.objects O 
ON O.object_id = SP.object_id
LEFT JOIN sys.dm_exec_sessions S 
ON S.session_id = TL.request_session_id
LEFT JOIN sys.dm_tran_session_transactions ST 
ON S.session_id = ST.session_id
LEFT JOIN sys.dm_tran_active_transactions AT 
ON ST.transaction_id = AT.transaction_id
LEFT JOIN sys.dm_exec_connections C 
ON C.session_id = S.session_id
Database Transaction lock information was last modified: October 1st, 2017 by Justin Antony