4

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?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Catch22
  • 3,261
  • 28
  • 34
  • Are you trying to performance tune, trouble-shoot, or do you need some sort of audit trail? – JeffO Apr 01 '11 at 20:25
  • It's not for performance tuning at all. I can use the DMV's for that. I just need to determine if someone queried specific tables on the database. A long shot I know; but thought I'd ask the question anyway. – Catch22 Apr 02 '11 at 08:57

1 Answers1

5

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'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Actually, `SELECT` queries can generate logs (in the `tempdb`) if the work tables are used by the plan. They are not backed up of course, so this is not relevant to the @op, but I thought I'd mention it just for the sake of correctness :) – Quassnoi Apr 01 '11 at 17:32
  • @Quassnoi - (Not disputing that this might be the case but...) Why would that need to be logged? The `tempdb` only needs `ACI` not `ACID`. It gets recreated every restart. – Martin Smith Apr 01 '11 at 17:35
  • @Martin: because transaction logs improve performance (using sequential write access). Not everything is logged of course: only things like page allocation or persistent worktables from cached plans. http://technet.microsoft.com/en-us/library/cc966545.aspx – Quassnoi Apr 01 '11 at 17:39
  • @Quassnoi - What kind of `SELECT` query would these appear in? I just tried a query with a spool and there was no additional logged entries in `tempdb` - Would it need a hash join or something? - Ah just noticed your link. Maybe a large sort operation would do it. – Martin Smith Apr 01 '11 at 17:49
  • Thanks for the info. So it looks like I can use the dm_exec_cached_plans query to find recent ad-hoc queries but very old queries probably won't be there (even when SQL Server not restarted). – Catch22 Apr 02 '11 at 09:15
  • @Catch22 - Yes when SQL Server needs to free up space in the cache it uses a combination of how recently the plan was used and the cost of the plan to compile to govern which ones get evicted. – Martin Smith Apr 02 '11 at 10:35