0

I need to find out where in the database changes are made, originating from an application. In other words: I'm not interested in the actual value changes, I'm interested in where these changes are taking place. Can this be done and if so, how?

Context

I'm tasked with managing a gigantic application that interacts with a very large database. Even though I have the source code available it is proving quite difficult to track which changes in the database the different parts of the application cause. More specifically which tables and columns get "touched" by the actions invoked from the application.

I have a test system set up (a clone of production) with it's own database. I am the only user on the test system so I can safely say that triggered changes are caused by my own actions.

I've been reading up on CDC, but as far as I can tell if you don't know which columns are being touched you have to query ALL the columns by name. This is problematic considering the database holds hundreds of tables with hundreds of columns.

Is there any other way to get a list of changes in the database per action?

|improve this question
  • I read with CDC on SQL Server 2008 R2 Enterprise or Developer edition, CDC should capture INSERT, UPDATE, and DELETE. By "touched" do you mean columns in tables that are SELECTed from? The CDC is supposed to help show you what changed if that's what you're looking for in this case. Here's a link to a supposed GUI tool to help a bit with it: cdchelper.codeplex.com and here's a link to an overview article you may have already read with your research but seems thorough: simple-talk.com/sql/learn-sql-server/… – Pimp Juice IT Sep 21 '15 at 11:36
  • @pj-mahony Thanks for your reply. By "touched" I mean records either created, deleted or altered by the application. – confused_person Sep 21 '15 at 13:46
  • What are your goals here? There are a few ways to track changes to a database, CDC is just one option. Each has its intended use case with its own pros/cons. You actually can find the column that was changed (touched) in CDC; in fact that's all you can do. CDC only captures changes so everything recorded by CDC has been changed since CDC was enabled. If you don't know what you're looking for, that's a different problem. Back to the first question: what are your goals? Why do you want to track changes? – SQLmojoe Sep 21 '15 at 16:26
  • @SQLmojoe Thanks, your answer already clears up a lot of confusion. My goal is simple, I need to be able to track database changes the application causes. I don't know what to look for before hand. What I'm trying to establish is this: click button in app (do nothing else), see what has changed at the database side (could be any column or more across the hundreds of tables the database has), repeat for other buttons in the app. – confused_person Sep 22 '15 at 13:55
2

It sounds like, for this particular scenario, you should run a trace in SQL Profiler. You can open it from the Tools menu in SSMS or find it in the Start > Applications > SQL Server 2008 R2 > Performance Tools folder.

Create a new trace and connect it to your database. Use the "blank" template and in the Events Selection tab just select Stored Procedures > RPC:Completed and TSQL > SQL:BatchCompleted.

Pause (but not stop) your trace until you're ready to click something, then start the trace back up and see exactly what queries the application produces. If you're still getting a lot of noise in your traces, you can use the column filters and the CientProcessID or SPID fields will probably help you nail it down.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.