I am looking for a way to log all events that occur on the server. I have used quary profier and that gets me the info i need but is there a way to truncate the file, say only log the last 30 minutes of activity and having it rolling so that it is always the last 30 minutes. Or is there some other software that will do this. The database times out at random times and I am trying to fix the problem but dont want to keep houndreds of log files that query profiler will create by leaving a trace running 24/7. Any suggestions.
sp_cycle_errorlog will only cycle the SQL Server error log which is not what the original poster requested.
You MIGHT be able to send the profiler results to a table in the database and then have some other process running periodically (every 1/2 hour) come in and delete from that table where the records are more than X minutes old.
This could potentially get quite ugly, however, and it is not something I would personally recommend. Have you looked at some 3rd part performance monitoring software? You can also use NT performance alerts and logs to track some (not all) SQL performance points.
Not sure if it violates the rules of this non-commercial forum. But here goes (apologies in advance to those who get offended):
I have used Spotlight on SQL (was very pleased with the graphical layout and ability to interact and correct problems). Download an evaluation copy at www.quest.com.
Embarcadero has several tools which I have seen demonstrated, but never actually used myself. www.embarcadero.com
I have also recently downloaded Microsoft's SQL Best Practices Analyzer tool and used it to analyze my installations. This is not a performance analyzer per se, but it may help you to highlight problem areas and/or inefficient designs.