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 |
|
CraigL77
Starting Member
4 Posts |
 Posted - 2009-11-20 : 11:35:03
|
| Hello,I would like to setup some kind of process to monitor a production database that has thousands of tables (most of them created by developers a long time ago). The goal is to eliminate all tables that arent being used by a huge collection of ETL jobs and reports. I would like this process to run for 1 month before I go dropping anything.I thought about using a trace, but a trace would require that I either paste in every table name. Has anyone come up with a good way to monitor table usage in sql 2000? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-20 : 11:49:48
|
| I was gonna say that you could generate a trigger for each, but that would only track insert, updates and deletes, and not SELECTSA trace is probably the only way to go, but running a trace for a month might be prohibitiveMaybe you can identify some good filtersBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2009-11-25 : 13:49:43
|
| This would be an ideal deal as am also trying to find number of tables in the database that are not beings used for so long. |
|
|
|
CraigL77
Starting Member
4 Posts |
Posted - 2009-12-10 : 17:24:47
|
quote:
Originally posted by sql_newbie121
This would be an ideal deal as am also trying to find number of tables in the database that are not beings used for so long.
i am going with a trace. I will load the trace into a table and run a query on the output and see whats not being used.if u want the query i can send it to you |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-12-10 : 17:44:14
|
quote:
Originally posted by CraigL77
quote:
Originally posted by sql_newbie121
This would be an ideal deal as am also trying to find number of tables in the database that are not beings used for so long.
i am going with a trace. I will load the trace into a table and run a query on the output and see whats not being used.if u want the query i can send it to you
Do you want us to send you ours?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
|
|
Determining last time a table was used?