Procedure for Removing Obsolete SQL Objects from Production Environments

  • Maybe I'm using the wrong key words, but I not found much on this topic, so I thought I would pose the question here. I am fairly new to team development, having been the only in-house developer at previous jobs. (Not counting my internships, since I was handed a string of bite-sized tasks and did not see much of project management.)

    Does anyone have a solid, comprehensive standard operating procedure for removing obsolete SQL objects from production environments?

    Background: We have various SQL-based systems that are integrated using out-of-box, third-party and custom code. We have a growing in-house team of developers (7+) and analysts (2+). We use source control for all code and custom project management software (yes, it actually works!...and all the developers actually use it!) to prioritize, schedule, coordinate, and measure all projects.

    As the development team grows, the management and administrative issues become increasingly complex in order to deal with the various inter-dependencies. As part of keeping our databases clean, we want to remove obsolete SQL objects, but do so without breaking anything.

    I have been tasked with developing standard procedure for retiring obsolete SQL objects gracefully. Any tips to point me in the right direction?

    Thanks in advance!

  • The main question would be: What would you define as "obsolete SQL objects"?

    I think you can only refer to views, procedures and/or functions that would use tables or columns that no longer exist. Those objects MIGHT be considered as obsolete but should be reviewed (and maybe scripted out) before removed.

    Regarding the remaining objects: There is not really a way to find obsolete objects. You can query and analyze sys.dm_exec_cached_plans to find objects which have a cached query plan. But that does not imply that all other objects are obsolete... All you can do is mark those objects as "in question".

    Just assume the following scenario:

    You have a procedure including some related tables that you use during an annual inventory check. The fact that those object have not been used in the last 10 month doesn't imply those objects being obsolete...

    Mybe there are some new methods provided with SS2K8 that would help here but I'm not sure...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • a fairly common question, actually; search for "unused procedures" and "unused tables", you'll see a ton of discussions, all pointing to Lutz's conclusion: any process, whether a trace or anything else you put in place will not be 100% accurate; some stuff may only be used at an end of year or end of fiscal year, or a table might exist for a functionality in an app that exists, but the current clientele don't bother using it;

    a perfect example is an application i have has memo/notes tables for 5 different sections of the app for collection various data, but only one or two tables actually get used....query every client db and there is never any data in 3 of those tables.

    take the table away, and eventually, someone will test that section and the app would crash with the old table/object not found or something....

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I've got a snippet I put together that searches for invalid views/procs/functions; if those objects don't compile anymore, it has to be due to a dependent object like a table or view being renamed,modified, or something like that that breaks the dependency.

    anything invalid would imply an incomplete /overlooked change due to an upgrade, or something that maybe could be removed, since it doesn't work anyway....

    this is one of the many prongs of investigating objects that can be removed.

    SET NOCOUNT ON

    DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))

    DECLARE @objname NVARCHAR(4000),

    @cmd NVARCHAR(max)

    --#################################################################################################

    --Views

    --#################################################################################################

    DECLARE acursor CURSOR FOR

    SELECT

    QUOTENAME(s.name) + '.' + QUOTENAME(v.name)

    FROM sys.views v

    INNER JOIN sys.schemas s ON v.schema_id = s.schema_id

    OPEN acursor

    FETCH NEXT FROM acursor INTO @objname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    BEGIN TRANSACTION

    BEGIN TRY

    exec sp_refreshview @objname

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    INSERT @BadObjects VALUES (@objname)

    END CATCH

    FETCH NEXT FROM acursor INTO @objname

    END

    CLOSE acursor

    DEALLOCATE acursor

    --#################################################################################################

    --Procs

    --#################################################################################################

    DECLARE c1 CURSOR FOR

    SELECT

    QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,

    mods.definition

    FROM sys.objects obs

    INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id

    INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id

    WHERE obs.is_ms_shipped = 0

    AND obs.type_desc IN('SQL_STORED_PROCEDURE')

    OPEN c1

    FETCH NEXT FROM c1 INTO @objname,@cmd

    WHILE @@fetch_status <> -1

    BEGIN

    BEGIN TRANSACTION

    BEGIN TRY

    SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))

    print @cmd

    exec (@cmd)

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    INSERT @BadObjects VALUES (@objname)

    END CATCH

    FETCH NEXT FROM c1 into @objname,@cmd

    END --WHILE

    CLOSE c1

    DEALLOCATE c1

    --#################################################################################################

    --Functions

    --#################################################################################################

    DECLARE c1 CURSOR FOR

    SELECT

    QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,

    mods.definition

    FROM sys.objects obs

    INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id

    INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id

    WHERE obs.is_ms_shipped = 0

    AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')

    OPEN c1

    FETCH NEXT FROM c1 INTO @objname,@cmd

    WHILE @@fetch_status <> -1

    BEGIN

    BEGIN TRANSACTION

    BEGIN TRY

    SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))

    print @cmd

    exec (@cmd)

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    INSERT @BadObjects VALUES (@objname)

    END CATCH

    FETCH NEXT FROM c1 into @objname,@cmd

    END --WHILE

    CLOSE c1

    DEALLOCATE c1

    SELECT * FROM @BadObjects

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Sorry, I was imprecise. I do not need code or a program to find obsolete SQL objects--I need a set of standard operating procedures (SOP/checklist) to follow when developers have a custom SQL object that they know should be retired.

    For example, two stored procedures (zp_a and zp_b) were specifically created for Project ABC. Eight months later, Project XYZ replaces and extends everything from Project ABC, so all custom objects related to Project ABC should be retired. We already know that zp_a and zp_b were created specifically for Project ABC from the original project documentation, so we do not need to find them. We do not want to leave obsolete code in the production database, so we need documented procedures to follow to gracefully retire the stored procedures. Theoretically, we could just do a DROP PROC zp_a and DROP PROC zp_b once Project XYZ is active and Project ABC is no longer in use; however, it is possible that some undocumented or rogue code that crept into production over the past eight months and we do not want to break anything else by doing so. We need a safe, reasonable, and progressive method of retiring obsolete objects that effectively cleans out old code without breaking anything else in production.

    Below is a heap of things I had in mind, but I wondered if anyone else has a system that has been proven to work.

    - Identify related/dependent objects that will become obsolete upon deploying new project.

    - Notify other developers of the change.

    - Use typical techniques to find other views/UDFs/stored procs that reference the objects in question.

    - Have a standard timeline for archiving/renaming/moving/deleting obsolete objects.

    - Of course, TEST IN DEV environment prior to any action in production.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply