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