How to capture information of a truncate statement in SQL Server 2012
I would like to grab the truncate statement information along with the user / login information for the entire database on my production server.
Example:
Use mydb
go
truncate table deleteme_table
I would like to write information into a table as shown below
Table Operation Database Login Time
deleteme_table Truncate mydb sandeep.pulikonda 17-12-2014 17:50:00
If the above scenario is not possible, please suggest possible ways to capture it
I am using the standard edition of SQL Server 2012. Therefore, verbosity checking is not supported for this version.
source to share
you can use SQL Server Audit functionality and add auditing for these queries.
this article explains in detail how to get this.
Another good way to profile your SQL Server is to use SQL Profiler. Here is a similar question to yours and an answer describing how to use SQL Profiler to achieve results.
SQL Server Profiler - how to filter trace only for display TSQL containing DELETE statement?
source to share