SQL profiler to trace trigger events

Use Standard Default template and MUST Add SP:StmtCompleted 


Now kick off SQL Profiler and a new trace; you need only trace the SP:StmtCompleted event because that’s where trigger execution appears. 

Choose text data if you want to trace for particular tables 

Trigger to find insert update and delete on table

 use db name 
go 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  TRIGGER [dbo].[tr_TableAudit_AccountTbl] 
   ON [dbo].Account
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
SET NOCOUNT ON;


BEGIN TRY
IF EXISTS (SELECT * FROM DELETED) OR EXISTS (SELECT * FROM INSERTED)
BEGIN
DECLARE @Database_Name sysname, @Schema_Name sysname, @Trigger_Name sysname;
SELECT @Database_Name = DB_NAME(), @Schema_Name = OBJECT_SCHEMA_NAME([parent_id]), @Trigger_Name = OBJECT_NAME([parent_id])
FROM sys.triggers WHERE object_id = @@PROCID;

----get the result set
WITH XML_Values AS (
SELECT 
@Database_Name + '.' + @Schema_Name + '.' + @Trigger_Name AS [TableName],
(SELECT * FROM DELETED D1 WHERE D1.AccountID=D.AccountID FOR XML RAW, ROOT, TYPE, ELEMENTS XSINIL) AS [OldRecord],
(SELECT * FROM INSERTED I1 WHERE I1.AccountID=I.AccountID FOR XML RAW, ROOT, TYPE, ELEMENTS XSINIL) AS [NewRecord],
COALESCE(I.AccountID, D.AccountID) As RecordID--Added new 
FROM Inserted I
FULL OUTER JOIN Deleted D ON I.AccountID=D.AccountID
)


INSERT INTO [AuditLog] 
( [TableName], 
[OldRecord], 
[NewRecord],
[RecordID]--Added New
)

SELECT 
[TableName],
[OldRecord],
[NewRecord],
   [RecordID]
 
FROM XML_Values
WHERE ISNULL(CONVERT(VARCHAR(MAX), [OldRecord]), '')<>ISNULL(CONVERT(VARCHAR(MAX), [NewRecord]), '');
END
END TRY
BEGIN CATCH
-- No op
END CATCH
END

Migration SQL Server 2008R2-SQL Server 2014(incomplete)

Deprecated features: find what features are deprecated?

https://www.mssqltips.com/sqlservertip/1370/identifying-deprecated-sql-server-code-with-profiler/
https://www.mssqltips.com/sqlservertip/1857/identify-deprecated-sql-server-code-with-extended-events/


when database backup and restored to any machine server, users will be moved , all we need to move is logins

when database is read only, backed up,
restore will also have same read only 

Find Job Schedules good one

USE msdb
GO
CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
  @freq_interval INT ,
  @freq_subday_type INT ,
  @freq_subday_interval INT ,
  @freq_relative_interval INT ,
  @freq_recurrence_factor INT ,
  @active_start_date INT ,
  @active_end_date INT,
  @active_start_time INT ,
  @active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT

IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description
END


SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))  
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time,  
dbo.udf_schedule_description(dbo.sysschedules.freq_type,
dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type,
dbo.sysschedules.freq_subday_interval,
dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor,
dbo.sysschedules.active_start_date,
dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time,
dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled

INTO #temp
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

SELECT Name, ScheduleDscr
from #Temp
where ScheduleDscr NOT LIKE '%Automatically starts when SQLServerAgent starts%'
order by name 

we cannot find Table Dependencies if the Job is using Table name visa T SQL

firstly:
Table A: if this table A is used inside a View or SP , i can find out that SP,View are dependent on Table A.

What about a Job uses Table A( select/Insert/delete inside a Job?), ?
we CANNOT find out ,

So it is always a best Practice to USE SP( convert existing t SQL inside a job and use in SP) in a job.

same like when applications using T SQL we cannot find out the dependencies, we need to TRACE via profiler and find out.

Find how:
we can query to find out where tableA is being used inside all databases
I Personally LIKE Redagte search

Trace Will not Capture row data if got inserted via Bulk Insert

fyi,
i was capturing all events happening against a Table, I was able to find S/I/U/D etc events for few Phone number
But there was a phone number i was particular about and i couldn't see in Insert ,so with the time it got inserted I see SSIS package doing BULK INSERT.


so, what i learned is if we insert data via ssis or bulk copy/insert we will not see ROW data.
we can see  all Statements happening with SSIS or BULK ...