是否在SQL Server事务日志中存储了临时只读查询? [英] Are ad-hoc read-only queries stored in SQL Server transaction log?

问题描述

在将数据库恢复模型配置为full的SQL Server 2008中，诸如

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

从TableName中选择col1，col2，col3

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?

推荐答案

否.事务日志根本不记录查询.它仅记录前滚或后滚事务所需的信息(SELECT查询根本不会生成任何记录的活动)

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)

您可以尝试

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'

这篇关于是否在SQL Server事务日志中存储了临时只读查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！