Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Do you really want everything? Or are there any filters such as only from certain logins, only in certain dbs, only ad-hoc queries, only on certain tables? Anything that captures that much information will have a performance impact. –  Stuart Moore Oct 11 '12 at 7:52
    
I would look into using extended events for something like this. They're more lightweight than trace but they'll still have an impact depending on the load on the server. What's the end goal you're trying to accomplish? –  cfradenburg Oct 11 '12 at 12:03
1  
And if you want to use trace, use a server-side trace, not profiler. Ugh. Use profiler to set up and script the trace, but don't run the trace through profiler. –  Aaron Bertrand Oct 11 '12 at 15:46
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.