Hi we run std 2008 r2.  I just started experimenting with some of the monitoring solutions offered at https://social.msdn.microsoft.com/Forums/en-US/5781c375-5339-47bd-9db0-43b4755a3eea/monitoring-usage-of-about-10-tables-between-11pm-and-2am?forum=sqldatabaseengine  .    I started a trace on our test server filtering db name and text data.  I entered two different db names and then in text data filtered 4 partial table names (no schemas, no periods no brackets etc) that would catch the  6 of the 10 tables I am interested in monitoring.

then in ssms I did a select top 1000 on one of those tables in one of those dbs and see nothing in my trace.  I already know that just filtering db's alone shows this select in the trace's text data column.  Is there some basic sql profiler behavior i'm missing?  Maybe i'm expecting too much out of a "like" filter on textdata and need to revert to just filtering the dbs.

Even after limiting the filter to just one partial name I still see nothing.  Also matching the case specified in my filter didn't help either.

 

• Edited by db042190 Tuesday, August 25, 2015 1:07 PM consistency

ahh maybe this is where josh's wildcard comes in.  I'll post back here.

yes, I put the % signs around the partial names and all is good.

reading more now on how/if proc sql will display.

• Edited by db042190 Tuesday, August 25, 2015 1:15 PM follow up

I added sp:stmtCompleted to the event list but that only helped if I left off the db filter. 

How can I ask profiler to tell me about any query coming to my target db regardless of source?  I'm guessing i'll have the same challenge for queries coming from linked servers.

I haven't played much with the database filter.  Don't you want to leave it off anyway?

You can capture for all databases and filter it out later in your analysis, if there's any reason to.

Josh

• Edited by JRStern Tuesday, August 25, 2015 8:58 PM

thx JR, that's not what I wanted to do but now that I see the behavior it might be what I have to do.  And i'm guessing for 100% coverage, i'd need the same scan running the same way on a number of other servers that are linked to this server.  Doesn't seem elegant. 

So I was asking if there is a way to capture any request going to this db (perhaps with at least textual type filters) without scheduling the scan on multiple servers.  I think u r saying no. 

I have to believe there is one place in every db where every query involving that db (remote or otherwise) gets logged, and perhaps that file or table can be scraped (before its purged) by a utility I'd throw together.  Or maybe one of the more popular 3rd party products already has such a feature.

Thx Erland.  I had a few events checked and one of them caught the sql inside the proc.  I'll replay it all when time permits and post back here which event that was.  I checked the events that were recommended by either a poster or an author on the web. 

thx JR, that's not what I wanted to do but now that I see the behavior it might be what I have to do.  And i'm guessing for 100% coverage, i'd need the same scan running the same way on a number of other servers that are linked to this server.  Doesn't seem elegant. 

Those you don't to worry about. Any connection from a linked server will show up in the trace, because the remote query generates one or more local queries.

Filtering on the database name may still be a problem, though, because the query from the remote server may run in the context of a different database.

thx.  Does anyone know of a reliable 3rd party product that deals with this class of problem?