Tracking deletes on SQL Server

Question

I intend to track delete actions done on a SQL Server DB whose recovery model is simple.

Do such actions get logged when the DB is in this mode?

Answer 1

You can achieve your goal in many different way. If you want you can read delete operations from sql server transaction log, but you will "loose" it after each transaction log backup if you are in full recovery model. In simple recovery model you can not control the transaction log contents.

To find delete operations for a particular table you can use the following query:

DECLARE @MonitoredTable sysname
SET @MonitoredTable = 'YouTable'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @MonitoredTable + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

Another approach you can use is to write an "audit trigger" or you can use directly sql server auditing features/Sql server extended events as well explained in this Apex webpage:

SQL Server database auditing techniques