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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-13 : 11:15:44
|
| Paulie writes "I started looking after 3 web apps that connect to a single database a while ago. I've since noticed that there are a number of tables that don't seem to be used by any of the apps although some of these do have data in them - I think they may have just been orphaned as the apps grew in size and functionality.I'd like to safely remove these from the database so they don't cause clutter & confusion. Rather than performing text searches on each application for each table in MS InterDev I was hoping there may be an administrative tool in SQL Server that will identify these orphaned tables quickly by means of determining the last time they were referenced or some similar indicator.I've already tried using DBCC statements with little success - perhaps I'm not reading the results properly? Any suggestions on how I can achieve this would be greatly appreciated. My role is programming based and my DBA skills are sadly lacking (time to buy a decent book I think :-) )Many Thanks in advance,Pauliehaha" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-13 : 11:27:19
|
| About the best you can do is check dependencies for each table. You can do this using the "sp_depends" system procedure:sp_depends 'myTable'It will list the objects (tables, views, etc.) that myTable depends upon, and also the objects that depend upon it. Be aware though that some dependencies may not show up with this procedure (depends on which version of SQL Server you're using, I think it's fixed with 2000)Make sure you perform A FULL DATABASE BACKUP before you start dropping tables, and do everything incrementally (don't drop more than 2-3 tables at a time) This will reduce the number of things that might break, and make it easier to pinpoint which table(s) are crucial. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-13 : 11:49:52
|
| Another solution (if dropping tables on a live system might not be a good idea) is to put insert / update triggers on all teh tables, and have the trigger write to some log table.Come back in a week / month and see what tables are in your log table.I would think if you don't write to a table at least once a month, you probably don't need it. One question to ask yourself is, if the table has some data, but very little, do I just leave it in there and not chance fubaring something later? Just a thought. I like to error on the "Disks are cheaper than my job" side :)Rob's suggestion (as always) is a better and quicker way. Make SURE you do FULL DATABASE BACKUPS before dropping any data. Michael |
|
|
|
pauliehaha
Starting Member
1 Post |
Posted - 2002-05-13 : 12:41:46
|
| Hi Rob & Mike,Many Thanks for your time.I'm going to use both your suggestions to determine which tables I can remove. I need to determine whether any of the scripts in my application query these tables so writing update triggers to a log file should help me do that whilst the system sp, sp_depends should let me know of any dependancies.Also, thanks for your suggestions regarding the backup which are much appreciated. Some of the actions you suggested really got me thinking - I'll be sure to include some of these actions for any future backup I perform henceforth.For future reference, how would you guys ensure that should anyone else come to administer your databases in the future they would be able to tell whether a table is being regularly used?I was thinking along the lines of including a 'Date Last Changed' Column in any new table I create or is this overkill?Remember, I'm coming from a programmer's perspective not a DBA's so it could be that SQL Server provides administrative tools for doing this at table creation that I am unaware of.By the way the db's are on SQL Server 2000 Enterprise Edition.Many Thanks,Paul. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-13 : 13:05:07
|
quote:
For future reference, how would you guys ensure that should anyone else come to administer your databases in the future they would be able to tell whether a table is being regularly used?
The best way is to document the database(s). Talk to the designer or previous admin, if possible. See if there's any documentation on it now.You can create some documentation using MS Visio, it can reverse engineer the database and create entity-relationship diagrams. That might help you find some orphaned tables too; if you see a table that doesn't connect to any others, chances are it's one of those prototypes that's not in use anymore.There is also the sp_fkeys and sp_foreignkeys system procedures, these will list any declared foreign keys (relationships) between tables. Any tables that appear in these lists should NOT be dropped, even if they don't appear to be used or updated, they are there to maintain data integrity. These will show up on a Visio reverse engineer too, if you happen to go that route.I really like the trigger idea, it's pretty easy to do. The only problem is that it won't get SELECT activity. But....you can use SQL Profiler to catch that! There are events that you can trace, probably the easiest one is the Object:Opened event. It will catch every access to a table, SELECT, UPDATE, whatever. It will knock down performance a bit, so you might want to run it occasionally. You can dump the trace activity into a table as well, for later review.Also look at any jobs that exist on the server, especially scheduled ones. Some tables might only be used for maintenance purposes and only get touched once a month or whatever.You can certainly add a "last modified" column to your tables, but you'll also have to write triggers to keep it up to date; no big deal, but still some work. I would suggest that once you have everything documented you drop all of the unnecessary tables, sprocs, jobs, etc. and leave strict instructions that whatever is left is NECESSARY. Make sure that the documentation is easy to find too! (you won't believe what I've found UNDERNEATH and BEHIND file cabinets and desks!) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-13 : 14:51:48
|
quote:
I really like the trigger idea, it's pretty easy to do.
Thanks Rob! Michael |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-05-13 : 16:40:47
|
| One other thing to consider. Just because you don't write to a table doesn't mean t is useless. Make sure it is not a lookup table, or some other table that is queried.-Chad |
|
|
|
|
|
|
|
|
Identifying unused tables in SQL Server