• Pruning is not something that needs to be done all the time but we have recently made massive changes to the structure of our main database, we currently have nearly 1000 stored procedures in our database and every single one of these would have to be recoded to handle the different table structures and any logic around the new usage. If I hadn't taken the time to investigate what procedures were in use and which were obsolete I wouldn't have been able to reduce the workload down to less than half that.

    I did, however, take a different approach. Rather that just go through trace files, I did the following..

    1. checked through the source code of all applications for each of the database objects.

    this reduced the number of stored procedures that were possibly in use, and although laborious, didn't take too long, most of the time being taken up by the search rather than by me. All these procs were pruned

    2. run a short term trace to isolating procedures which were definitely used

    3. of what was left I edited the remainder with a simple logging procedure which logged their last use, left this on for a number of months and then revisited what had been logged.

    Of course I have the advantage of having the source code but it saved the developers months of pointless extra work so they were happy to provide me with it.