Log whenever a SQL Server table is truncated / all rows are deleted.

Category: sql server getstarted

Question

Eswar Chandra on Thu, 03 May 2018 05:09:49


Hello Experts,

I need some monitoring mechanism where I can see/check whenever a table in SQL Server database is truncated/all the rows are deleted.

It should be a light weight process.

Thanks,

Eswar


Eswar

Replies

Visakh16 on Thu, 03 May 2018 05:55:15


You can do this in multiple ways assuming its a delete operation

1. Using Change tracking in database

2. using triggers

However if its TRUNCATE then please note that its minimally logged and cant be tracked using trigger or any DML based events


Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page


Shanky_621 on Thu, 03 May 2018 07:16:00


You can track it using Database audit feature which is enterprise only feature you may also use extended events trace to capture it. 

SQLNeophyte on Thu, 03 May 2018 08:47:16


How about configuring a job in SQL Server Agent which monitors the table periodically?

Shanky_621 on Thu, 03 May 2018 09:16:37


How about configuring a job in SQL Server Agent which monitors the table periodically?
How will you "monitor" the table, what would be in that job? 

SQLNeophyte on Thu, 03 May 2018 09:19:26


Something like checking count(*) from the table?

Shanky_621 on Thu, 03 May 2018 09:27:53


Something like checking count(*) from the table?
Well Count(*) is not as simple as it seems there are drawbacks when frequently running it. The problem with this is you have to run this as job every X sec which can be counter productive.

TiborK on Thu, 03 May 2018 10:45:57


Just a note that database audit functionality was included in lower editions as of SQL Server 2016 sp1. :-)

TiborK on Thu, 03 May 2018 10:46:34


It might be worth mentioning that Change Tracking won't tell us *who* did this.

Uri Dimant on Sun, 06 May 2018 06:34:15


Can you explain the purpose of monitoring? You want to restore the data? You want to prevent from users truncate the data?

trương việt dũng on Sun, 06 May 2018 06:56:26


thanks you very muck

Vishal Jharwade on Sun, 06 May 2018 13:19:54


Hello Eswar,

You can check truncation/ all rows deleted status by using the below query. E.g. Employee is a table

SELECT object_Name(object_id), [Rows] as RowCounts FROM sys.partitions WHERE object_id = object_id('Employee')

You can use this query in the SQL Agent to notify you/team if this will also the requirement.

IF (SELECT [Rows] as RowCounts FROM sys.partitions WHERE object_id = object_id('Employee')) <= 0

BEGIN

PRINT 'Write send email code'

END

Hope, this will help you

Thanks,
Vishal Jharwade