| Author |
Topic |
|
mirzat
Starting Member
5 Posts |
 Posted - 2005-08-11 : 07:48:44
|
| It's the possible to see which object (table,view,proc) used (not changed) in last year?Thanks. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-11 : 07:52:37
|
| Can you give more details on what you are trying to do?MadhivananFailing to plan is Planning to fail |
 |
|
|
mirzat
Starting Member
5 Posts |
Posted - 2005-08-11 : 07:56:28
|
| I want to remove old object from database, object there are not used on last one year |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-11 : 08:02:05
|
| How do you define "they are not used"?MadhivananFailing to plan is Planning to fail |
|
|
|
mirzat
Starting Member
5 Posts |
Posted - 2005-08-11 : 08:08:00
|
| :)I don’t mean modified.When I say used, I mean on:Select * from TABLE, exec sp_proc, Select * from VIEW … |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-11 : 08:11:44
|
| >>I want to remove old object from database, object there are not used on last one yearI asked you on How you know the objects are not usedDo you mean the tables which have no entries for the last one year?MadhivananFailing to plan is Planning to fail |
|
|
|
mirzat
Starting Member
5 Posts |
Posted - 2005-08-11 : 08:15:20
|
| I mean on object (table,view,proc) there are not accessing from user for the last one year? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-11 : 08:18:46
|
| The only way to know that they were not accessed is to run a trace that captures all SELECT activity and examining it. Any tables you do NOT see in the trace output would be unused. This is probably not practical, but it's the only way to know for sure.An easier way to approach the problem is to first identify tables you MUST keep. From the remaining tables you want to eliminate, trying renaming one or two of them at a time and see if any applications stop working or cause errors. While this sounds kinda dumb, you will quickly find out if a table is still being used. |
|
|
|
mirzat
Starting Member
5 Posts |
Posted - 2005-08-11 : 08:24:57
|
| That way is not possible. This software uses 400-500 users. Software is 24-7 day work. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-11 : 09:23:01
|
| It is always better to do documentation on which tables are used in the database so that you can later drop the tables or other objects that were not used in the applicationMadhivananFailing to plan is Planning to fail |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-11 : 10:09:51
|
| There's no way to find out all used objects for a given time period without having deliberately traced or logged this information. Like Rob says, a profile trace is the only built-in way to know every call to the db server. Of course that is not recommended as a 24-7 solution. I have seen people (well person) set up a job to regularly capture info from syscacheobjects. That way is not fool proof but a possible way to capture usage/objects over time without keeping a trace running.Based on my 20-20 hindsight, the best way is to build usage logging into the application architecture. I can't tell you how many times we've used these usage tables for things we didn't even anticipate. They've been an invaluable diagostic and analysis tool. Of course that doesn't help for adhoc stuff, its specific to an application.Be One with the OptimizerTG |
|
|
|
|
Object info ?