Registering all queries in a SQL Server 2008 Express database?

Question

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

This is a development machine, so the negative side effects of Select-Queries logging are not a problem.

Before someone suggests using SQL Profiler: this is not available in Express (does anyone know if it is available in the web editor?), And I'm looking for a way to register requests even when I leave.

Answer 1

SQL Server Profiler:

• File → New Trace
• The General tab is displayed.
• Here you can select "Save to file:" so that it is written to the file.
• Click on the "Event Selection" tab.
• Select the items you want to register.
• TSQL -> SQL: BatchStarting helps you choose sql
• Stored Procedures -> RPC: When completed, you will receive stored procedures.

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

Update - SQL Express Release:

A remark was made that the MS SQL Server proxy is not available for the express version.
There seems to be a free alternative: Profiler for Microsoft SQL Server 2005 Express Edition

Answer 2

... Late answer, but I hope this would be useful to other readers here ...

Using SQL Server Express with advanced auditing requirements, for example, is not entirely optimal, unless it is used only in a development environment.

You can use the tracks (www.broes.nl/2011/10/profiling-on-sql-server-express/) to get the data you need, but you have to disassemble them yourself.

There are third-party tools that can do this, but their cost will be quite high. ApexSQL's log explorer can log everything except select, and Ideras Compliance Manager will also record statements, but its cost is much higher.

Answer 3

There is another way to get information about queries that were executed in MS SQL Server Express, described here: http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently -ran-query /

In short, it launches an intelligent query on system tables and receives information (text, runtime) about queries (or cached query plans, if necessary). Thus, you can get information about completed queries without a profiler in the release of MSSQL 2008 Express.

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 

Answer 4

You can register changes. SQL Server 2008 will make this especially easy with Change Data Capture. But SQL Server is not very good at registering SELECT.

Theoretically possible with a profiler, but it will kill your performance. You can “get away with it” on your desktop, but I think you will notice that your machine is slow enough to cause problems. And it will definitely not work after any deployment.

One important point that several more people have already missed: if they didn’t change something in 2008, which I didn’t hear about, you cannot call SELECT.

Answer 5

It seems that you can create traces using T-SQL

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

This can help.

Answer 6

For the record only, I include tips to use the DataWizard SQL Performance Profiler as a separate answer, as it is indeed the opposite answer pointing to the SQL Server profiler .

There is a free trial for 14 days, but even if you need to buy it, it is only $ 20 for 3 servers (at the time of writing, 2012-06-28). It seems to me more than fair, considering that thousands of users using the version of SQL Server Express have saved.

I just used the trial version and offers exactly what the OP is looking for: a way to keep track of all the requests coming into a particular database. He also suggests exporting the trace to an XML file. The paid version offers a few more features, but I have not tried them yet.

Disclaimer: I am just another developer working with the database from time to time, and I am in no way affiliated with the DataWizard. I just liked their tool so much and wanted people to know that it exists because it helped me profile my SQL Server Express installation.

Answer 7

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