To run SQL Server Profiler or to use the SQL Server Agent's Jobs function in SSMS or ...?

Question

I have a problem here - I wish to keep track of all transaction executed on the SQL Server but was wondering how should I proceed.

If I were to use the SQL Server Profiler and configure it to store all the transaction history into a file and perform a roll-over if a specified size is hit, then I will need to make sure that the SQL Server Profiler is always open and running. Also from my understanding, it will decrease the server performance.

If I were to use the Jobs function under the SQL Server Agent in SSMS, then it become a schedule task that only run when it is being called.

So, how do I go about to keep track of all SQL transaction make to the SQL Server and record it 24/7 or until I manual stop it (and also would not slow down the performance of the server)?

Answer 1

In the past I wrote some scripts to capture slow running queries.

I used SQL Profiler via T-SQL stored procedures. The overhead for this wasn't huge and at the time provided valuable insight.

More info and some examples are here: http://www.toadworld.com/platforms/sql-server/w/wiki/9827.running-traces-with-tsql.aspx

I would write two stored procedures, one to start the trace and one to stop it. These could be called via a SQL Agent job or a scheduled task, whatever is preferred. Log only the info you want to a table rather than files for easier management. You could create a third job to clean up records after "X" days/weeks/months depending on your requirements.

The performance hit will depend on the existing load on the server and there is no easy way of doing this so test any recommended solutions thoroughly before deciding which way to go.