Category: sql server getstarted
Eswar Chandra on Thu, 03 May 2018 05:09:49
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.
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
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
VJ123456 on Sun, 06 May 2018 13:19:54
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
PRINT 'Write send email code'
Hope, this will help you