SA11 Log all commands

[Former Member]

How can I log all commands the clients are sending to database server?

I wanna to "see" those commands to know how entity framework 4.0 is generating sqls and maybe try to change ling expressions.

[VolkerBarth]

Have you tried dbsrv11 -zr SQL -zo RLL.log already (and possibly the other -zr options)?

[Breck_Carter]

@Volker: If anything qualifies as an actual "answer" as opposed to a comment, your comment does... I think 🙂

[VolkerBarth]

@Breck: Who would dare to argue with the Administrator:) - The main reason for just commenting is my quite small own experience with RL. But you're right, this site should not suffer from too shy users.

[thomas_duemesnil · Accepted solution]

I use these Commands to enable request level logging as needed.

-- Set Destination relative to db server !!!
call sa_server_option('Request_level_log_file', 'C:\\temp\\mylog.txt');

-- Log everything
call sa_server_option('Request_level_logging', 'ALL');
-- Log only SQL 
call sa_server_option('Request_level_logging', 'SQL');

For more Options see sa-server-option-system-sysproc

-- Add a Comment to the log file
call sa_audit_string('Ahead of Procedure call');

-- Stop logging
call sa_server_option('Request_level_logging', 'NONE');

With these commands you can switch on the logging from a iSQL Session before you press a Button in your Application. This make it possible to log only the interesting SQL code.

[Former Member]

While request logging will log the statements, the Application Profiling feature within Sybase Central permits both the logging of all statements PLUS their graphical plans. As Application Profiling results are stored in a database, you can then mine the logging data using whatever specific criteria you desire (for example, listing only those statements that refer to a specific table, or only those statements that took longer than a certain elapsed time).

[Former Member]

I never can to use this option. I can create perf db and start/stop capture, but I don't know how to analyse data. 😞

[Breck_Carter]

@Glenn: I feel Zote's pain... there is a certain [cough] barrier to entry when it comes to getting the most out of Application Profiling... LogExpensiveQueries was easier even though it required a great deal of manual effort. It's a GUI thing, as in Graphic Usability Issues.

[reimer_pods]

@Glenn: Breck's comment says what I think too. A few SQL statements loaded from a script file, and off you go. I tried to use Application Profiling with a 9 GB database: creating the log database took hours. And finding the right way to get to analyzing the result was a real pain.

[VolkerBarth]

I guess "Request logging" will do what you request.

I would try

dbsrv11 -zr SQL -zo RLL.log

(and possibly the other -zr options as well).

[Former Member]

You can switch on SQL logging capability locally in client side (see ODBC connection properties, advanced tab). In that case you don't need to have any administrative access to db server, and can intercept and analyse sql generated only by your application.

[VolkerBarth]

...as long as the client is using ODBC (whereas in Zote's original case, I guess it's a native ADO.Net connection and therefore not using ODBC at all)...