Logging ALL Queries on a SQL Server 2008 Express Database?

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.

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.

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

Seems that you can create traces using T-SQL

[To see links please register here]

That might help.

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

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.

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.

**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.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

…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.

You can use traces (

[To see links please register here]

) to get the data you need but you’d have to parse these yourself.

There are third party tools that can do this but their cost will be quite high. [Log explorer](

[To see links please register here]

) from ApexSQL can log everything but select and [Idera’s](

[To see links please register here]

) compliance manager will log select statements as well but it’s cost is a lot higher.

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][1]


_Update - 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][2]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

There is one more way to get information about queries that has been executed on MS SQL Server Express [described here][1].

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.

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


[1]:

[To see links please register here]

The SQL query below can show simple query logs:

```mssql
SELECT last_execution_time, text
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle)
ORDER BY last_execution_time
```

This is how it looks like below:

[![enter image description here][1]][1]


[1]: