Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Audit Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 2:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 419, Visits: 2,122
Hi,
I just sent up an audit on a database to collect all the select queries on a table. Everything is working fine if we use just select statmenet with where condition. But if we use a variable it's not capturing the variable value. It just grabs the variable name. How to overcome this? I am using 2008 sp3 enterprise edition.
Post #1376669
Posted Thursday, October 25, 2012 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:59 AM
Points: 13, Visits: 173
Try 'Change Data Capture', it can capture DML statements
Post #1376980
Posted Thursday, October 25, 2012 12:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 419, Visits: 2,122
Is there any other way we can do it other than CDC?
Post #1377187
Posted Thursday, October 25, 2012 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Server-side traces and extended events are how I do that kind of thing. You can capture any DML events you want to, and filter them pretty much however you like, using either of those.

I recommend extended events, because Microsoft says they'll sunset traces in a future version of SQL Server and to use extended events for new development.

Data on the subject here: http://msdn.microsoft.com/en-us/library/dd822788(v=SQL.100).aspx


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1377196
Posted Thursday, October 25, 2012 9:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 35,772, Visits: 32,444
As Gus suggested, a server side trace with a LIKE filter looking for the table name will work quite nicely. So would "extended events".

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1377378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse