Tell me more ×
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.
up vote 4 down vote favorite
1

In SQL Server 2008 R2, admin users are allowed to view the Activity Monitor and under "Recent Expensive Queries", there is a list of SQL statements being ran on the server.

Is there a way to export each and every one of the SQL statements displayed without using SQL Server profiler?

I was given the responsibility of monitoring a database with many redundant tables and I have no idea which tables are actually in use.

I want to remove those dummy or unused tables and retain the productive tables.

Any suggestions on how to accomplish this?

share|improve this question
3  
I'm pretty sure the Activity monitor just pulls the root of its data from sys.dm_exec_query_stats (and obviously with sys.dm_exec_sql_text). As for a way to directly import it form Activity Monitor? I don't think that's possible. But you could just work with the direct DMV to get the data. – Thomas Stringer Apr 6 '12 at 12:40

1 Answer

up vote 1 down vote

Well you can (on a dev system of course) run a trace against yourself running activity monitor and then isolate the queries you want to use for monitoring. Then you could either create custom jobs or use custom collectors with management data warehouse to execute those queries on your desired schedule to log the activity you want to another table. Then you can just analyze the results there.

share|improve this answer

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.