3

I would like to capture the truncate statements information along with the user/Login information for all database in my production server.

Example: 

Use mydb
go 
truncate table deleteme_table

I would like to capture the information into the table like the 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 SQL Server 2012 Standard version. So granular level audit are not supported for that version.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sandeep Pulikonda
  • 774
  • 3
  • 13
  • 26
  • SQL Server doesn't track this information by default. You'll need to use a server-side trace (Profiler is *NOT* "a good way") or use extended events. This information is not captured by the default trace and there is no DDL event to support a DDL trigger approach (though [I just asked for one](https://connect.microsoft.com/SQLServer/feedback/details/1074977).). – Aaron Bertrand Jan 03 '15 at 19:30

1 Answers1

1

you can use the SQL Server Audit functionality and add an audit for those queries.

this article explains in detail how to obtain this.

Another good way of profiling your SQL Server is using SQL Profiler. Here is a SO question similar to yours and an answer describing how to use SQL Profiler to achieve the results.

SQL Server Profiler - How to filter trace to only display TSQL containing a DELETE statement?

Community
  • 1
  • 1
Adrian Nasui
  • 1,054
  • 9
  • 10
  • Sorry, I am using SQL Server 2012 Standard version. So Granular level audit are not supported for that version. – Sandeep Pulikonda Dec 17 '14 at 12:44
  • try my second suggestion, SQL Server Profiler. – Adrian Nasui Dec 17 '14 at 12:46
  • okay. can the tracer capture all times. I mean like table triggers – Sandeep Pulikonda Dec 17 '14 at 12:49
  • unfortunately Profiler does have a performance impact, and is not recommended for permanent use. – Adrian Nasui Dec 17 '14 at 13:04
  • I see that Extended Events is the technology that Microsoft Recommends using for these kinds of tasks, but I am uncertain if you could solve your problem this way. Check this article, it explains how Extended Events can be defined and used in SSMS 2012. https://www.simple-talk.com/sql/database-administration/getting-started-with-extended-events-in-sql-server-2012/ – Adrian Nasui Dec 17 '14 at 13:06