Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to program against a vendor supplied database, and would like to understand what SQL statements or procedures they are calling.

Is there a way I can monitor a specific database? The system was setup with both the production and test database in the same instance (so monitoring an instance is too broad).

share|improve this question
add comment

2 Answers

You can accomplish this with the SQL Server Profiler.

http://msdn.microsoft.com/en-us/library/ms181091.aspx

First you will want to create a trace.

http://msdn.microsoft.com/en-us/library/ms175047.aspx

Then you will want to specify the events you want to track.

http://msdn.microsoft.com/en-us/library/ms188674.aspx

Then setup a filter for the database name.

http://msdn.microsoft.com/en-us/library/ms175520.aspx

share|improve this answer
1  
One thing you will want to note is that if you are doing this on a production database, you will probably want to either limit the Profiler trace to a very limited amount of time (it will get too big anyway) due to the extra strain it puts on the system. Otherwise you might need to look for other ways to monitor, like Extended Events. –  Sean Long Jul 15 '13 at 14:55
    
Sean brings up excellent points. Thanks. –  Brian Bentley Jul 15 '13 at 19:06
    
Thanks. So to make sure I cover all bases, if I go into my vendor app and enter a new customer record I should be monitoring what on the trace? --I don't really know EVERY table that the vendor might touch --I don't really know EVERY operation the vendor might use I can target a test database that I will be the only person using, so should I try to capture all reads and writes? –  jhoop2002 Jul 17 '13 at 19:45
    
Also, I see where I enter the database name. It seems to be asking for ApplicationName instead. –  jhoop2002 Jul 17 '13 at 19:51
    
You will want to start with SQL:BatchCompleted, SQL:BatchStarting and SP:StmtCompleted. The Standard Profiler template includes these events. You will want to make sure and collect the DatabaseName and TextData columns. Once you have selected the Database Name column it will be available in the filter window. –  Brian Bentley Jul 17 '13 at 23:07
add comment

Another option is to use SQL Server Audit. It also provides auditing of a single database, not only of the whole SQL Server instance. You can specify the events and objects you want to audit, and thus reduce the noise in the resultset

Create a Server Audit and Database Audit Specification

Captured audited info can be stored in an event log, security event log or a *.audit file To see a SQL Server audit log content, use T-SQL such as:

SELECT * FROM sys.fn_get_audit_file ('\\serverName\Files\Audit2013*.sqlaudit',default,default)
share|improve this answer
    
I don't think this approach will work because I don't know all of the tables my vendor my select or insert to while creating a new client record. –  jhoop2002 Jul 17 '13 at 20:08
    
Yes, that it a setback for this method –  Milena Petrovic Jul 19 '13 at 7:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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