May 18, 2010 at 3:43 pm
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!
May 18, 2010 at 4:00 pm
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...
May 18, 2010 at 4:40 pm
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
May 18, 2010 at 4:44 pm
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
May 19, 2010 at 12:01 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy