0

I need to write a query to get the statements executed by the user in a particular database since the last restart.

First of all, I extract all the transactions from ::fn_dblog(NULL,NULL), which returns the transaction log. After this, I tried to get the cached queries using this two queries:

SELECT *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC;

-- OR

select * from master.dbo.syscacheobjects where dbid = db_id() and objtype = 'Adhoc';

My idea is to join the statements that can produce a transaction with its transaction, and show the others too. The problem is that the first query that tries to get the information of the queries doesn't provide any information to identify the database, and the other query doesn't provide any information that I can use to join it to the transaction log.

I'm trying to construct this query without previous knowledge of this particular DBMS, so please forgive my conceptual errors. Thank you in advance.

The guy at the corner
  • 1
  • 1
    (1) `sys.dm_exec_sql_text` does expose a `dbid` column, but what should it be if you have a join between tables in different databases? (2) please use `sys.dm_exec_cached_plans` over the backward compatibility view `syscacheobjects`. – Aaron Bertrand Jul 18 '12 at 11:58
  • (1) The problem is that in my case some queries have a NULL value in the field `dbid`, so I cannot identify the database in which they were performed (2) I'll take that into account if I need to use it, thank you. – The guy at the corner Jul 18 '12 at 12:24
  • Well like I said, queries don't always have context to exactly one database. You may want to look at `sys.dm_tran_locks`, `sys.dm_tran_database_transactions` and `sys.dm_tran_active_transactions`. – Aaron Bertrand Jul 18 '12 at 12:28

0 Answers0