Logging all queries executed on MSSQL database

Submitted by Jochus on Sat, 30/03/2013 - 18:00 | Posted in: Database
Database


Related to this blogpost, I wanted to log all queries executed on a Microsoft SQL Server database.

This can easily be done by the SQL Server Profiler, which gets shipped with the SQL Studio Express application of Microsoft.
However, after navigating through my webapplication (which uses JPA (Hibernate)), I could only see a bunch of the following SQL statements:

EXEC sp_execute 6, 8
EXEC sp_execute 7, 8
EXEC sp_execute 4, 8

Hibernate is running a prepared query. To get the actual SQL query, you can run the following query (you will still have to look for the query, but, it's getting you a lot closer then sp_execute :-)):

SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > GETDATE()-1
ORDER BY      STATS.last_execution_time DESC

By tweaking the WHERE clause, you can easily retrieve the quer(y)(ies) you are looking for :-)!

Comments

Submitted by Anonymous (not verified) on Mon, 22/04/2013 - 20:17
 

This is really slick. Anyway to include the user that MADE the query?

Submitted by Jochus on Mon, 22/04/2013 - 23:57
 

Hmm, not really. Need to check :-) ...

Submitted by Anonymous (not verified) on Tue, 02/07/2013 - 02:30
 

some investigation? I need to know, the user that made the query

Submitted by Jochus on Tue, 02/07/2013 - 13:40
 

Hmm, what about this query:

SELECT      c.session_id, s.host_name, s.login_name, s.status, st.text, s.login_time, s.program_name, *
FROM        sys.dm_exec_connections c
INNER JOIN  sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY    c.session_id

Submitted by Nguyen Truong Vu (not verified) on Tue, 12/03/2019 - 05:40
 

How to get name of database executed.

Submitted by Jason (not verified) on Tue, 17/11/2015 - 10:32
 

#Jochus Thanks for the last query !

Submitted by Vishnu Rao (not verified) on Wed, 13/07/2016 - 11:54
 

Hi #Jochus,

Thank you for the last query , this helps ..

Is there a way find out login details for all sql handle as the last query looks for (most_recent_sql_handle) only.

Vishnu

Submitted by Jochus on Tue, 19/07/2016 - 10:36
 

Hi Vishnu,

No, sorry, not that I am aware of.

Kind regards,
Jochen

Submitted by Angel Lopez (not verified) on Wed, 31/07/2019 - 01:17
 

Great query, thanks for the help! in case that I don't want to see most_recent_sql_handle, and I want to see all the sqltext executed, filtered by dates, how I can modify the query to do this? thanks in advance for your support.

Add new comment

The content of this field is kept private and will not be shown publicly.
About text formats

Full HTML

  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.