How do I trace database writes in SQL Server?

Question

I'm using SQL Server 2008 R2, trying to reverse-engineer an opaque application and duplicate some of its operations, so that I can automate some massive data loads.

I figured it should be easy to do -- just go into SQL Server Profiler, start a trace, do the GUI operation, and look at the results of the trace. My problem is that the filters aren't working as I'd expect. In particular, the "Writes" column often shows "0", even on statements that are clearly making changes to the database, such as INSERT queries. This makes it impossible to set a Writes >= 1 filter, as I'd like to do.

I have verified that this is exactly what's happening by setting up an all-inclusive trace, and running the app. I have checked the table beforehand, run the operation, and checked the table afterward, and it's definitely making a change to the table. I've looked through the trace, and there's not a single line that shows any non-zero number in the "Writes" column, including the line showing the INSERT query. The query is nothing special... Just something like

exec sp_executesql 
    N'INSERT INTO my_table([a], [b], [c]) 
      values(@newA, @newB, @newC)',
    N'@newA int,@newB int,@newC int', @newA=1, @newB=2, @newC=3

(if there's an error in the above, it's my typo here -- the statement is definitely inserting a record in the table)

I'm sure the key to this behavior is in the description of the "Writes" column: "Number of physical disk writes performed by the server on behalf of the event." Perhaps the server is caching the write, and it happens outside of the Profiler's purvue. I don't know, and perhaps it's not important.

Is there a way to reliably find and log all statements that change the database?

Answer 1

Have you tried a Server Side Trace? It also works to document read and writes, which - if I'm reading you correctly - you are wanting to document writes.