Search code examples
sql-serversql-server-2008transaction-log

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


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

select col1,col2,col3 from TableName

logged to the transaction log files.

In other words, can I determine what queries were run on the database on a particular day using the transaction log backups?


Solution

  • No. The transaction log does not record queries at all. It just records the info necessary to roll forward or roll back transactions (and a SELECT query would not generate any logged activity at all)

    You can try

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

    to have a look at the kind of stuff recorded.

    If you needed this kind of information you would need to set up a trace / extended events session / audit session to record it. This could be prohibitively heavy weight in most environments.

    You could use the following to get a general idea about what adhoc queries are being run.

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