trace all queries coming for specific database

[rafaelbahia]

Hi. I know that in SQL Server we have Extended Events and SQL Profiler that allow you very detailed tracing capabilities. I also know that SAP ASE offers session tracing (I have used it some times to trace specific SPIDs). However, I need to trace all sessions coming to a database to troubleshoot a problem we're having. What's the best way to do it? Does ASE have something like Extended Events or SQL Profiler?

I need the trace to contain general connection info plus the query being executed by the session.

I am using Adaptive Server Enterprise/16.0 SP04 PL01/EBF 29704 SMP/P/x86_64/SLES 12.4/ase160sp04pl01x/3473/64-bit/FBO/Tue Jul 20 07:58:11 2021

[DilipVoora · Accepted solution]

Hi,

Please check the below and see which one is feasible to you in your case.

1.Using MDA tables 
   select * from monProcessSQLText
   select * from monSysSQLText

2. You can enable auditing to capture SQL text by turning it on to for each login/object you want to monitor. Please see the documentation under www.help.sap.com for auditing.

3. Using dbcc commands for SQL text and from sp_w/sp_who/master..sysprocesses for the info.
dbcc traceon (3604)
dbcc sqltext(<spid>)

Regards,
Dilip Voora

[rafaelbahia · Accepted solution]

Thanks for the options. Looks like 2 is the closest to what I need. Can you enable auditing for a specific database only? I don't want to audit everything that happens in all databases since we have quite a few.

[DilipVoora · Accepted solution]

Yes, you can and below is the syntax.

sp_audit "all","all",<database_name>,"on"