CODEWITHSUNDEEP
sqlsql-serverdatabaseloggingssms
Michael Stum
Michael Stum

Reputation: 181094

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.

Upvotes: 63

Views: 102407

Answers (8)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

The SQL query below can show simple query logs:

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

Upvotes: 0

Dmytriy Voloshyn
Dmytriy Voloshyn

Reputation: 1062

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

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

Upvotes: 32

KyleLanser
KyleLanser

Reputation: 2719

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

Upvotes: 69

Herbert Lynch
Herbert Lynch

Reputation: 751

…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 (www.broes.nl/2011/10/profiling-on-sql-server-express/) 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 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.

Upvotes: 28

Oliver
Oliver

Reputation: 9518

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.

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.

Upvotes: 1

Jason Glover
Jason Glover

Reputation: 618

Seems that you can create traces using T-SQL

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

That might help.

Upvotes: 0

Jason N. Gaylord
Jason N. Gaylord

Reputation: 8324

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

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.

Upvotes: 3

Related Questions