Are the read-only ad-hoc requests stored in the SQL Server transaction log?

Question

In SQL Server 2008 with a database recovery model configured to full , there are queries such as

select col1, col2, col3 from TableName

logged in transaction log files.

In other words, can I determine which queries were executed in the database on a specific day using transaction log backups?

Answer 1

Not. The transaction log does not record requests at all. It simply records the information needed to fast forward or roll back transactions (and the SELECT query does not generate any logged actions)

You may try

 select top 100 * from sys.fn_dblog(default,default) 

to look at the material recorded.

If you need this information, you will need to set up a trace / extended events / audit sessio n session to record it. This can be prohibitively heavy in most environments.

You can use the following to get a general idea of ​​what adhoc requests are running.

 SELECT text from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where objtype='Adhoc'