Logging ALL Queries on a SQL Server 2008 Express Database?

Question

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

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.

Answer 1

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.

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.

Answer 2

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

Answer 3

SQL Server Profiler:

• File->New Trace
• The "General" Tab is displayed.
• Here you can choose "Save to file:" so its logged to a file.
• View the "Event Selection" Tab
• Select the items you want to log.
• TSQL->SQL:BatchStarting will get you sql selects
• Stored Procedures->RPC:Completed will get you Stored Procedures.

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

Update - Express Edition:

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

Answer 4

Seems that you can create traces using T-SQL

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

That might help.