在SQL Server 2008 Express数据库上记录所有查询?

Is there a way to tell SQL Server 2008 Express to log every query (including each and every SELECT Query!) into a file?

是否有办法让SQL Server 2008 Express将每个查询(包括每个选择查询!)记录到一个文件中?

It's a Development machine, so the negative side effects of logging Select-Queries are not an issue.

它是一个开发机器，因此日志选择查询的负面影响不是问题。

Before someone suggests using the SQL Profiler: This is not available in Express (does anyone know if it's available in the Web Edition?) and i'm looking for a way to log queries even when I am away.

在有人建议使用SQL Profiler之前:这不是Express(有人知道它是否在Web版本中可用吗?)，我正在寻找一种方法来记录查询，即使在我不在的时候。

7 个解决方案

#1

SQL Server Profiler:

SQL Server分析器:

File → New Trace
文件→新的跟踪
The "General" Tab is displayed.
显示“General”选项卡。
Here you can choose "Save to file:" so its logged to a file.
在这里，您可以选择“Save to file”，这样它就可以登录到一个文件。
View the "Event Selection" Tab
查看“事件选择”选项卡
Select the items you want to log.
选择要记录的项目。
TSQL → SQL:BatchStarting will get you sql selects
TSQL→SQL:BatchStarting SQL选择你
Stored Procedures → RPC:Completed will get you Stored Procedures.
存储过程→RPC:会让你存储过程完成。

More information from Microsoft: SQL Server 2008 Books Online - Using SQL Server Profiler

更多来自微软的信息:SQL Server 2008在线图书-使用SQL Server Profiler。

Update - SQL Express Edition:

更新- SQL Express Edition:

A comment was made that MS SQL Server Profiler is not available for the express edition.
There does appear to be a free alternative: Profiler for Microsoft SQL Server 2005 Express Edition

有人评论说，MS SQL Server Profiler在express edition中不可用。似乎有一个免费的替代方案:Microsoft SQL Server 2005 Express Edition的分析器

#2

…Late answer but I hope it would be useful to other readers here…

迟来的回答，但我希望对这里的其他读者有用……

Using SQL Server Express with advanced auditing requirements such as this is not really optimal unless it’s only in development environment.

使用SQL Server Express和高级审计需求(如此类)并不是最优的，除非它只在开发环境中。

You can use traces (www.broes.nl/2011/10/profiling-on-sql-server-express/) to get the data you need but you’d have to parse these yourself.

您可以使用跟踪(www.broes.nl/2011/10/profiling-on-sql-server-express/)获取所需的数据，但您必须自己解析这些数据。

There are third party tools that can do this but their cost will be quite high. Log explorer from ApexSQL can log everything but select and Idera’s compliance manager will log select statements as well but it’s cost is a lot higher.

有第三方工具可以做到这一点，但它们的成本将相当高。来自ApexSQL的日志资源管理器可以记录所有内容，但是select和Idera的合规管理器也会记录select语句，但是成本要高得多。

#3

There is one more way to get information about queries that has been executed on MS SQL Server Express described here: http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

还有一种方法可以获取关于在这里描述的MS SQL Server Express上执行的查询的信息:http://blog.sqlauthority.com/2008/01/03/sql- Server -2005-last- ran-query/

Briefly, it runs smart query to system tables and gets info(text, time executed) about queries(or cached query plans if needed). Thus you can get info about executed queries without profiler in MSSQL 2008 Express edition.

简单地说，它对系统表运行智能查询，并获取关于查询的信息(文本、执行时间)(如果需要，还可以缓存查询计划)。因此，您可以在MSSQL 2008 Express edition中获得关于已执行查询的信息。

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
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

#4

You can log changes. SQL Server 2008 will make this especially easy with Change Data Capture. But SQL Server isn't very good at logging SELECTs.

您可以登录更改。SQL Server 2008将使更改数据捕获变得特别容易。但是SQL Server并不擅长日志选择。

It is theoretically possible with the profiler, but it will kill your performance. You might "get away with it" on your desktop, but I think you'll notice your machine acting slow enough to cause problems. And it definitely won't work after any kind of deployment.

这在理论上是可能的，但它将扼杀你的性能。你可能会在你的桌面上“侥幸过关”，但我想你会注意到你的机器动作太慢，会引起问题。而且在任何形式的部署之后，它肯定不会起作用。

One important point a couple others have missed already: unless they changed something for 2008 I didn't hear about, you can't trigger a SELECT.

有几个人已经漏掉了一个重要的问题:除非他们为2008年做了一些我没听说过的改变，否则你无法触发选择。

#5

Seems that you can create traces using T-SQL

似乎可以使用T-SQL创建跟踪

http://support.microsoft.com/kb/283790/

That might help.

可能会有所帮助。

#6

Just for the record, I'm including the hints to use DataWizard's SQL Performance Profiler as a separate answer since it's really the opposite to the answer pointing at SQL Server Profiler.

仅就本文而言，我将包含使用DataWizard的SQL性能分析器作为单独答案的提示，因为它实际上与指向SQL Server分析器的答案相反。

There is a free trial for 14 days, but even if you need to buy it, it's only $20 for 3 servers (at the moment of writing, 2012-06-28). This seems more than fair to me considering the thousands everybody using SQL Server Express edition has saved.

免费试用14天，但即使你需要购买，3台服务器也只要20美元(本文撰写时为2012-06-28)。考虑到每个使用SQL Server Express edition的人都保存了数以千计的数据，这对我来说似乎非常公平。

I've only used the trial so far and it offers exactly what the OP was looking for: a way to trace all queries coming in to a specific database. It also offers to export a trace to an XML file. The paid version offers some more features but I haven't tried them yet.

到目前为止，我只使用了试用版，它提供了OP正在寻找的内容:跟踪所有查询到特定数据库的方法。它还提供将跟踪导出到XML文件的功能。付费版提供了更多的功能，但我还没有尝试过。

Disclaimer: I'm just another developer messing with DBs from time to time and I'm in no way affiliated with DataWizard. I just so happened to like their tool and wanted to let people know it existed as it's helped me out with profiling my SQL Server Express installation.

免责声明:我只是另一名开发人员，不时地与DBs打交道，我与DataWizard没有任何关系。我只是碰巧喜欢他们的工具，并想让人们知道它的存在，因为它帮助我分析SQL Server Express安装。

#7

I would either use triggers or use a third party software such as Red Gate to check out your SQL log files.

我要么使用触发器，要么使用第三方软件(如Red Gate)检查SQL日志文件。

#1