June 17, 2021 at 2:38 pm
Over the past 5 years with my employer, I have created a number of stored procedures, jobs, etc as I built interfaces for outside vendors. As the vendors have changed, and we entered into an affiliation agreement with a larger organization, many of the jobs are now disabled and the tables and SPs are no longer used. What process is recommended to clean up old, disabled jobs, SPs and tables? Should they be deleted/dropped? Left in place? I'm the only sql person, so I don't have any one there to ask. My management will likely defer to my decision. TIA
June 17, 2021 at 2:54 pm
It all depends on the chance that the data or code will be needed later.
If the jobs are no longer needed, you could script them to a file before deleting if you think they may be useful for future reference.
Same with stored procedures. Script them to files, if you think they might be needed, or have useful code you might want to look at later.
If you are nervous to delete, you can rename with prefix 'ZZ_' so they resequence alphabetically to the bottom of the list and do not clutter up the view of active stored procedures.
Tables could be copied to an "Archive" database and backed up, then dropped from production. Make sure there are no relationships to active processes.
June 28, 2021 at 11:46 am
Interesting that a business would defer to an IT person to determine business needs.
There must be someone in your company that is responsible for record retention. That person should make the decision.
As noted above, be sure to back up everything, maybe twice, since no matter what the business decides, someone could always reverse that.
June 29, 2021 at 8:07 am
I would leave the tables and stored procedures in place, unless you know for certain they are not being used. As suggested by others I would script out the jobs to text file to be stored somewhere secure and then delete those disabled jobs which have not been run for a while. You can decide how long this period can be.
It might be worthwhile you documenting your new process and getting sign-off from your management.
If some of the tables no longer used are quite large you could always define a process to establish they are no longer referenced by anything and then purge/archive as you decide.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply