How to see query history in SQL Server Management Studio

Question

Is the query history stored in some log files  If yes  can you tell me how to find their location  If not  can you give me any advice on how to see it

User · Answer

Late one but hopefully useful since it adds more details…

There is no way to see queries executed in SSMS by default. There are several options though.

Reading transaction log – this is not an easy thing to do because its in proprietary format. However if you need to see queries that were executed historically (except SELECT) this is the only way.

You can use third party tools for this such as ApexSQL Log and SQL Log Rescue (free but SQL 2000 only). Check out this thread for more details here SQL Server Transaction Log Explorer/Analyzer

SQL Server profiler – best suited if you just want to start auditing and you are not interested in what happened earlier. Make sure you use filters to select only transactions you need. Otherwise you’ll end up with ton of data very quickly.

SQL Server trace - best suited if you want to capture all or most commands and keep them in trace file that can be parsed later.

Triggers – best suited if you want to capture DML (except select) and store these somewhere in the database

User · Answer

As others have noted  you can use SQL Profiler  but you can also leverage it s functionality through sp trace   system stored procedures  For example  this SQL snippet will  on 2000 at least  I think it s the same for SQL 2008 but you ll have to double-check  catch RPC Completed and SQL BatchCompleted events for all queries that take over 10 seconds to run  and save the output to a tracefile that you can open up in SQL profiler at a later date   DECLARE  TraceID INT DECLARE  ON BIT DECLARE  RetVal INT SET  ON   1  exec  RetVal   sp trace create  TraceID OUTPUT  2  N Y  TraceFile trc  print  This trace is Trace ID       CAST  TraceID AS NVARCHAR  print  Return value       CAST  RetVal AS NVARCHAR  -- 10   RPC Completed exec sp trace setevent  TraceID  10  1   ON     -- Textdata exec sp trace setevent  TraceID  10  3   ON     -- DatabaseID exec sp trace setevent  TraceID  10  12   ON        -- SPID exec sp trace setevent  TraceID  10  13   ON        -- Duration exec sp trace setevent  TraceID  10  14   ON        -- StartTime exec sp trace setevent  TraceID  10  15   ON        -- EndTime  -- 12   SQL BatchCompleted exec sp trace setevent  TraceID  12  1   ON     -- Textdata exec sp trace setevent  TraceID  12  3   ON     -- DatabaseID exec sp trace setevent  TraceID  12  12   ON        -- SPID exec sp trace setevent  TraceID  12  13   ON        -- Duration exec sp trace setevent  TraceID  12  14   ON        -- StartTime exec sp trace setevent  TraceID  12  15   ON        -- EndTime  -- Filter for duration  column 13  greater than  operation 2  10 seconds    10 000ms  declare  duration bigint set  duration   10000 exec sp trace setfilter  TraceID  13  0  2   duration   You can find the ID for each trace-event  columns  etc from Books Online  just search for the sp trace create  sp trace setevent and sp trace setfiler sprocs  You can then control the trace as follows   exec sp trace setstatus 15  0       -- Stop the trace exec sp trace setstatus 15  1       -- Start the trace exec sp trace setstatus 15  2       -- Close the trace file and delete the trace settings      where  15  is the trace ID  as reported by sp trace create  which the first script kicks out  above    You can check to see what traces are running with   select   from   fn trace getinfo default    The only thing I will say in caution -- I do not know how much load this will put on your system  it will add some  but how big that  some  is probably depends how busy your server is

User · Answer

You can Monitor SQL queries by SQL Profiler if you need it

User · Answer

you can use  Automatically generate script on every save   if you are using management studio  This is not certainly logging   Check if useful for you

User · Answer

The system doesn t record queries in that way   If you know you want to do that ahead of time though  you can use SQL Profiler to record what is coming in and track queries during the time Profiler is running

User · Answer

SELECT deqs last execution time AS  Time   dest text AS  Query   dest   FROM sys dm exec query stats AS deqs CROSS APPLY sys dm exec sql text deqs sql handle  AS dest WHERE dest dbid   DB ID  msdb   ORDER BY deqs last execution time DESC   This should show you the time and date of when a query was ran

User · Answer

Query history can be viewed using the system views    sys dm exec query stats sys dm exec sql text sys dm exec query plan   For example  using the following query   select  top 100          creation time          last execution time          execution count          total worker time 1000 as CPU          convert money   total worker time    execution count 1000 as  AvgCPUTime           qs total elapsed time 1000 as TotDuration          convert money   qs total elapsed time    execution count 1000 as  AvgDur           total logical reads as  Reads           total logical writes as  Writes           total logical reads total logical writes as  AggIO           convert money   total logical reads total logical writes   execution count   0 0   as  AvgIO            sql handle           plan handle          statement start offset          statement end offset          plan generation num          total physical reads          convert money  total physical reads  execution count   0 0   as  AvgIOPhysicalReads           convert money  total logical reads  execution count   0 0   as  AvgIOLogicalReads           convert money  total logical writes  execution count   0 0   as  AvgIOLogicalWrites           query hash          query plan hash          total rows          convert money  total rows  execution count   0 0   as  AvgRows           total dop          convert money  total dop  execution count   0 0   as  AvgDop           total grant kb          convert money  total grant kb  execution count   0 0   as  AvgGrantKb           total used grant kb          convert money  total used grant kb  execution count   0 0   as  AvgUsedGrantKb           total ideal grant kb          convert money  total ideal grant kb  execution count   0 0   as  AvgIdealGrantKb           total reserved threads          convert money  total reserved threads  execution count   0 0   as  AvgReservedThreads           total used threads          convert money  total used threads  execution count   0 0   as  AvgUsedThreads           case              when sql handle IS NULL then                 else substring st text  qs statement start offset 2  2                   case                     when qs statement end offset  -1 then len convert nvarchar MAX  st text   2                           else qs statement end offset                     end - qs statement start offset  2             end as query text          db name st dbid  as database name          object schema name st objectid  st dbid      object name st objectid  st dbid  as  object name           sp  query plan  from sys dm exec query stats as qs with readuncommitted  cross apply sys dm exec sql text qs  sql handle   as st cross apply sys dm exec query plan qs  plan handle   as sp WHERE st  text  LIKE   query     Current running queries can be seen using the following script   select ES  session id         ER  blocking session id         ER  request id         ER  start time         DateDiff second  ER  start time   GetDate    as  date diffSec          COALESCE                      CAST NULLIF ER  total elapsed time    1000  0  as BIGINT                      CASE WHEN  ES  status   lt  gt   running  and isnull ER  status         lt  gt   running                                THEN  DATEDIFF ss 0 getdate   - nullif ES  last request end time    1900-01-01T00 00 00 000                        END                   as  total time  sec          CAST NULLIF  CAST ER  total elapsed time  as BIGINT  - CAST ER  wait time  AS BIGINT     1000  0   as bigint  as  work time  sec          CASE WHEN  ER  status   lt  gt   running  AND ISNULL ER  status       lt  gt   running                    THEN  DATEDIFF ss 0 getdate   - nullif ES  last request end time    1900-01-01T00 00 00 000            END as  sleep time  sec  --                        NULLIF  CAST  ER  logical reads    ER  writes     8   1024 as numeric 38 2    0  as  IO  MB          CASE  ER transaction isolation level         WHEN 0 THEN  Unspecified          WHEN 1 THEN  ReadUncommited          WHEN 2 THEN  ReadCommited          WHEN 3 THEN  Repetable          WHEN 4 THEN  Serializable          WHEN 5 THEN  Snapshot          END as  transaction isolation level desc         ER  status         ES  status  as  status session         ER  command         ER  percent complete         DB Name coalesce ER  database id   ES  database id    as  DBName          SUBSTRING                       select top 1   text  from sys dm exec sql text ER  sql handle                        ER  statement start offset  2 1                                               CASE WHEN   ER  statement start offset  lt 0  OR  ER  statement end offset  lt 0                                   THEN DATALENGTH   select top 1   text  from sys dm exec sql text ER  sql handle                                  ELSE ER  statement end offset                          END                         - ER  statement start offset                        2  1                    as  CURRENT REQUEST          select top 1   text  from sys dm exec sql text ER  sql handle    as  TSQL          select top 1   objectid  from sys dm exec sql text ER  sql handle    as  objectid          select top 1   query plan  from sys dm exec query plan ER  plan handle    as  QueryPlan         NULL as  event info -- select top 1   event info  from sys dm exec input buffer ES  session id   ER  request id    as  event info         ER  wait type         ES  login time         ES  host name         ES  program name         cast ER  wait time  1000 as decimal 18 3   as  wait timeSec         ER  wait time         ER  last wait type         ER  wait resource         ER  open transaction count         ER  open resultset count         ER  transaction id         ER  context info         ER  estimated completion time         ER  cpu time         ER  total elapsed time         ER  scheduler id         ER  task address         ER  reads         ER  writes         ER  logical reads         ER  text size         ER  language         ER  date format         ER  date first         ER  quoted identifier         ER  arithabort         ER  ansi null dflt on         ER  ansi defaults         ER  ansi warnings         ER  ansi padding         ER  ansi nulls         ER  concat null yields null         ER  transaction isolation level         ER  lock timeout         ER  deadlock priority         ER  row count         ER  prev error         ER  nest level         ER  granted query memory         ER  executing managed code         ER  group id         ER  query hash         ER  query plan hash         EC  most recent session id         EC  connect time         EC  net transport         EC  protocol type         EC  protocol version         EC  endpoint id         EC  encrypt option         EC  auth scheme         EC  node affinity         EC  num reads         EC  num writes         EC  last read         EC  last write         EC  net packet size         EC  client net address         EC  client tcp port         EC  local net address         EC  local tcp port         EC  parent connection id         EC  most recent sql handle         ES  host process id         ES  client version         ES  client interface name         ES  security id         ES  login name         ES  nt domain         ES  nt user name         ES  memory usage         ES  total scheduled time         ES  last request start time         ES  last request end time         ES  is user process         ES  original security id         ES  original login name         ES  last successful logon         ES  last unsuccessful logon         ES  unsuccessful logons         ES  authenticating database id         ER  sql handle         ER  statement start offset         ER  statement end offset         ER  plan handle         NULL as  dop --ER  dop         coalesce ER  database id   ES  database id   as  database id         ER  user id         ER  connection id  from sys dm exec requests ER with readuncommitted  right join sys dm exec sessions ES with readuncommitted  on ES session id   ER session id  left join sys dm exec connections EC  with readuncommitted  on EC session id   ES session id where ER  status  in   suspended    running    runnable   or exists  select top 1  1 from sys dm exec requests as ER0 where ER0  blocking session id  ES  session id     This request displays all active requests and all those requests that explicitly block active requests   All these and other useful scripts are implemented as representations in the SRV database  which is distributed freely  For example  the first script came from the view  inf   vBigQuery   and the second came from view  inf   vRequests    There are also various third-party solutions for query history  I use Query Manager from Dbeaver   and Query Execution History from SQL Tools  which is embedded in SSMS

User · Answer

Since this question will likely be closed as a duplicate    If SQL Server hasn t been restarted  and the plan hasn t been evicted  etc    you may be able to find the query in the plan cache   SELECT t  text  FROM sys dm exec cached plans AS p CROSS APPLY sys dm exec sql text p plan handle  AS t WHERE t  text  LIKE N  something unique about your query      If you lost the file because Management Studio crashed  you might be able to find recovery files here   C  Users  lt you gt  Documents SQL Server Management Studio Backup Files    Otherwise you ll need to use something else going forward to help you save your query history  like SSMS Tools Pack as mentioned in Ed Harper s answer - though it isn t free in SQL Server 2012   Or you can set up some lightweight tracing filtered on your login or host name  but please use a server-side trace  not Profiler  for this      As  Nenad-Zivkovic commented  it might be helpful to join on sys dm exec query stats and order by last execution time   SELECT t  text   s last execution time FROM sys dm exec cached plans AS p INNER JOIN sys dm exec query stats AS s    ON p plan handle   s plan handle CROSS APPLY sys dm exec sql text p plan handle  AS t WHERE t  text  LIKE N  something unique about your query   ORDER BY s last execution time DESC

User · Answer

I use the below query for tracing application activity on a SQL server that does not have trace profiler enabled  The method uses Query Store  SQL Server 2016   instead of the DMV s  This gives better ability to look into historical data  as well as faster lookups  It is very efficient to capture short-running queries that can t be captured by sp who sp whoisactive      Adjust script to your needs      Run full script  F5  - gt  Interact with UI - gt  Run full script again  F5      Output will contain the queries completed in that timeframe         Requires Query Store to be enabled      ALTER DATABASE  lt db gt  SET QUERY STORE   ON     ALTER DATABASE  lt db gt  SET QUERY STORE  OPERATION MODE   READ WRITE  MAX STORAGE SIZE MB   100000      USE  lt db gt     Select your DB     IF OBJECT ID  tempdb   lastendtime   IS NULL     SELECT GETUTCDATE   AS dt INTO  lastendtime ELSE IF NOT EXISTS  SELECT   FROM  lastendtime      INSERT INTO  lastendtime VALUES  GETUTCDATE       WITH T AS   SELECT      DB NAME   AS DBName       s name         o name AS ObjectName       qt query sql text       rs runtime stats id       p query id       p plan id       CAST p last execution time AS DATETIME  AS last execution time       CASE WHEN p last execution time  gt   lastendtime dt THEN  X  ELSE    END AS New       CAST rs last duration   1 0e6 AS DECIMAL 9 3   last duration s       rs count executions       rs last rowcount       rs last logical io reads       rs last physical io reads       q query parameterization type desc FROM       SELECT    ROW NUMBER   OVER  PARTITION BY plan id  runtime stats id ORDER BY runtime stats id DESC  AS recent stats in current priod     FROM sys query store runtime stats        AS rs INNER JOIN sys query store runtime stats interval AS rsi ON rsi runtime stats interval id   rs runtime stats interval id INNER JOIN sys query store plan AS p ON p plan id   rs plan id INNER JOIN sys query store query AS q ON q query id   p query id INNER JOIN sys query store query text AS qt ON qt query text id   q query text id LEFT OUTER JOIN sys objects AS o ON o object id   q object id LEFT OUTER JOIN sys schemas AS s ON s schema id   o schema id CROSS APPLY  lastendtime WHERE rsi start time  lt   GETUTCDATE   AND GETUTCDATE    lt  rsi end time     AND recent stats in current priod   1        Adjust your filters         -- AND  s name IN    lt myschema gt    OR s name IS NULL  UNION SELECT NULL NULL NULL NULL NULL NULL dt NULL NULL NULL NULL NULL NULL  NULL FROM  lastendtime   SELECT   FROM T WHERE T query sql text IS NULL OR T query sql text NOT LIKE    lastendtime   -- do not show myself ORDER BY last execution time DESC  TRUNCATE TABLE  lastendtime INSERT INTO  lastendtime VALUES  GETUTCDATE

User · Answer

If you want an history for the queries you executed through SMSS  You may want to try SSMSPlus  https   github com akarzazi SSMSPlus This feature does not exists out of the box in SSMS  You ll need SSMS 18 or newer  Disclaimer   I m the author

User · Answer

If the queries you are interested in are dynamic queries that fail intermittently  you could log the SQL and the datetime and user in a table at the time the dynamic statement is created  It would be done on a case-by case basis though as it requires specific programming to happen and it takes a littel extra processing time   so do it only for those few queries you are most concerned about  But having a log of the specific statements executed can really help when you are trying to find out why it fails once a month only  Dynamic queries are hard to thoroughly test and sometimes you get one specific input value that just won t work and doing this logging at the time the SQL is created is often the best way to see what specifically wasn in the sql that was built

User · Answer

A slightly out-of-the-box method would be to script up a solution in AutoHotKey  I use this  and it s not perfect  but works and is free  Essentially  this script assigns a hotkey to CTRL SHIFT R which will copy the selected SQL in SSMS  CTRL C   save off a datestamp SQL file  and then execute the highlighted query  F5   If you aren t used to AHK scripts  the leading semicolon is a comment    CTRL SHIFT R to run a query that is first saved off   r    Copy Send   c   Set variables EnvGet  HomeDir  USERPROFILE FormatTime  DateString  yyyyMMdd FormatTime  TimeString  hhmmss   Make a spot to save the clipboard FileCreateDir  HomeDir  Documents sqlhist  DateString  FileAppend   Clipboard    HomeDir  Documents sqlhist  DateString   TimeString  sql   execute the query Send   f5  Return   The biggest limitations are that this script won t work if you click  Execute  rather than use the keyboard shortcut  and this script won t save off the whole file - just the selected text  But  you could always modify the script to execute the query  and then select all  CTRL A  before the copy save   Using a modern editor with  find in files  features will let you search your SQL history  You could even get fancy and scrape your files into a SQLite3 database to query your queries

[sql-server] How to see query history in SQL Server Management Studio

Examples related to sql-server

Examples related to logging

Examples related to ssms