fn_dblog

• adisql

  SSCrazy

  Points: 2893

  More actions

  March 14, 2018 at 4:01 pm

  #403177

  Hi ,

  We have setup a job  to track DML changes with below script(fn_dblog) .
  is it cause  any performance issues in the server.?

  WITH CTE
  as(
  SELECT distinct [Transaction ID],Operation, AllocUnitName FROM fn_dblog(NULL, NULL)
  where Operation in ('LOP_MODIFY_ROW','LOP_DELETE_ROWS','LOP_MODIFY_COLUMNS','LOP_INSERT_ROW')
  and AllocUnitName not like 'sys%'
  and AllocUnitName <> 'Unknown Alloc Unit'
  )
  INSERT INTO [TEST]..[transactionlog_ABC_AUDIT]
  select F.[Begin Time],F.[Transaction SID],C.[Transaction ID],C.Operation, C.AllocUnitName ,F.Description,
  SUSER_SNAME(F.[Transaction SID]) as TransactionUser
  from CTE C
  INNER JOIN fn_dblog(NULL, NULL) F
  ON (C.[Transaction ID]=F.[Transaction ID])
  WHERE F.[Transaction SID] IS NOT NULL
  and F.[Begin Time] is not null
  and F.Description not like '%Alloc%'

• adisql

  SSCrazy

  Points: 2893

  More actions

  March 14, 2018 at 4:47 pm

  #1983319

  FYI..We have scheduled this script to run before the Transaction log job for every 45min.