On SQL Server 2008R2 Standard Edition (SP2 I hope), you have only 1 ½ options:
- A custom SQL Trace session (which you can prepare using Profiler) and then have to start automatically with server start by placing an auto-start procedure in master-db
- A custom Extended Events session
- the latter has less overhead, but many events are not available in 2008 yet – and some never will be because they belong to the Audit feature, which in SQL 2008 is enterprise only
You should get an exact list for what you really have to capture & log, but if you do not want to mix technologies, you will have to use SQLTrace alone.
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com