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 would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter).

If yes, where can I see it? If not, how could I set it up?

share|improve this question

3 Answers 3

By default, SQL Server activity is not logged the way you expect. Some write activity is recorded in the Transaction Log, but this also depends on how your databases are set up.

There are four main options for tracking SELECT activity on a server:

  1. You can use SQL Server Profiler to connect to your server and watch for specific activity as it happens.

  2. You can create a server-side trace to log activity to a trace file on the server, which can then be read by SQL Server Profiler, or by using fn_trace_gettable to load it into a table for further analysis.

  3. You can use Extended Events, which offers more functionality than server-side traces, and which Microsoft recommends instead of server-side traces starting with SQL Server 2012.

  4. You could use C2 audit mode.

You can use SQL Server Profiler to set up your trace (choose the specific events, filters, etc. that you want), then script it out using the File menu and execute it on the server to create a server-side trace, as described here.

share|improve this answer

By default, it does not log SELECT statements. You can refer to my answer here for more details to audit SELECT statements.

Also, by default it does not even log T-SQL statements, instead it logs EVENTS like Server Memory Change, Audit Login Failed, Audit Addlogin Event, etc and you can find more info here along with the T-SQL Scripts to extract information from Default trace.

share|improve this answer

As long as you don't care who ran the queries, there are some ways to see metrics about queries that are being run in your system. This is limited to data since the last restart and potentially by plan cache (or other memory) pressure depending on how much deeper you extend the query.

;WITH x AS
(
  SELECT 
    [text] = SUBSTRING(t.[text], 
      (s.statement_start_offset/2)+1, 
      COALESCE(NULLIF(s.statement_end_offset,-1),DATALENGTH(t.[text])*2)
       -(s.statement_start_offset/2)), 
    s.execution_count, s.last_execution_time,
    s.max_logical_reads, s.max_elapsed_time
  FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
)
SELECT * FROM x
WHERE LTRIM([text]) LIKE 'SELECT%';

You may want to extend the pattern - for example this will ignore queries that start with ;WITH, ironically, and can capture SELECT INTO and even variable assignments that don't reference actual tables.

However if you need more granular information than that, Nathan's answer is a good start (except if you're going to use trace, do NOT use Profiler). Just remember that logging every single query against your system is not going to be free.

share|improve this answer
    
I agree with the above statement I would be incredibly cautious about monitoring all select statements in your database. –  Zane Apr 26 '13 at 14:17

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.