Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cip
Starting Member
1 Post |
 Posted - 2011-09-18 : 10:37:30
|
| Hello .we are managing a DataBase server running SQL Server 2005 .Now we want to know all of the transactions on our DB Server .These transactions include all of the commands like (select,insert,update,delete,drop, etc ...) .seem, in sql server 2008, SQL Server Audit server is responsible for such tasks but we found nothing for SQL Server 2005 service .I would like to know which application/service is responsible for this job (monitoring all of the transactions on DB Server) .after that, we should write a .net application for collecting all of the statements (select,insert,update,create | across all over the database and it's objects) and the users causes these actions ...which is the best solution for this kind of job in 2005 ?thanks .--- |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-09-19 : 13:27:08
|
| You might want to create history tables and triggers to capture that data. As long as the row that gets copied into the history table has a timestamp/user associated with it, that should give you a nice audit trail on who changed data. Just be careful, because history tables can get large fast so you might want to take steps to purge out old history data.If you just want to monitor everything that is happening (procedures being kicked off, performance, etc), a trace might be your best route. Here is another article on tracing that also runs through how to dump the trace data from the file into a table. It also includes reference links to Microsoft's site.http://www.sqloptimizationsschool.com/Pages/Tracking%20Performance/Performance%20Tracing.aspx |
 |
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-09-27 : 13:24:42
|
| What about enabling C2 Audit? If you don't have a third party auditing tool, C2 can be useful.On the other hand, if you just want to check what all transactions are underway currently, you can query sys.dm_exec_requests DMV that will list all requests that are either running or waiting. Check out for session id > 50 which are 'generally' user requests. |
|
|
|
|
|
|
|
|
Monitoring SQL Server 2005 transactions