SQL Server Performance Forum – Threads Archive

removing unused stored procedures SQL 2000

gurus forgive me if this has been discussed before or if it is a basic fundamental… but… is there a quick/easy way in MSSQL Server 2000 to remove stored procedures that have accumilated during the development cycle but are no longer used by my application. stored procedures are called by my asp application. so i am thinking SQL Server will have no idea of what my asp application does and doesn’t need any more. please tell me i am wrong and that the guys working for bill gates forsaw that booger heads like me will be using their products and incorporated a magic button that with one click will remove all the stored procedures that i created during development, that are no longer being used. richgran "…as a thief in the night…"

Well, the clown in me would say: Yes, there is an easy way. Just drop the procedure(s), and see if anything breaks. [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] Unfortunately there is no easy way. Probably the easiest one would be to start a trace and let it run over some period of time and then analyse it which objects have been referenced and which have not. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

If you meant to know unused objects, see if this helps http://www.pinpub.com/ME2/Audiences…50&tier=4&id=CE9E01AE99334E1CBDC32C150AE6BE08 Madhivanan Failing to plan is Planning to fail

Hi, theirs a discussion about unused object (indexs) in our SSP too , see if it helps : http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9613</a> <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

>>theirs a discussion about unused object (indexs) in our SSP too , see if it helps : I think the questioner wants to know unused sps and not indices [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] is there a quick/easy way in MSSQL Server 2000 to remove stored procedures that have accumilated during the development cycle but are no longer used by my application. Madhivanan Failing to plan is Planning to fail

After the fact, there is never a quick and easy way. You’ve got to keep track of these things as part of the design process. IOW: clean up after yourself.

Hi, * think the questioner wants to know unused sps and not indices thats very true that questioner asked about sps but as the logic used to identify unused index, the same logic will be used to identify sps too …. isn’t it ?! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

Not really. When trying to identify unused sp’s you can for example use a workaround like that: Add another auditing table to the db. Script all sp’s into one script. Add code to the sp’s that they do a simple INSERT INTO the auditing table. One column would be @@PROCID (or the name of the proc if you prefer a cleaner way) and another one GETDATE(). Reapply the script to the db Now each time an sp is called it INSERTs the information to the audit table and you can analyse that table after a while and remove procedures you don’t find in there. Try that with indexes. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Can’t we get sps name in profiler trace ! suppose profiler trace for a weekly or monthly trace would give us a list of sps and the others are not used as oftenly ! with this can’t we figure out something !? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

Well, not to be rude or anything, but the original poster’s question was for SPs that have accumilated during the development cycle but are no longer used by my application. Why all the technical solutions? My advice "clean up after yourself" was the appropriate answer.

Adriaan, yes, while your answer is the correct one, it doesn’t help the OP here, since he hasn’t done so. Hopefully this will be a lesson for the future. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Hemantgiri, it’s more difficult to analyse a trace correctly than simply reading a single column. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Guys! guysâ€¦. You#%92ve been having a good discussion behind my back without me! Just kidding! I failed to subscribed to this thread and was waiting for an emailâ€¦ just visited the post now and would like to thank you gurus for interesting input. Excellent!
Thanks for your help ps. As a clown myself , the first suggestion appeals….
"…as a thief in the night…"

Heya,
I believe the program Apex SQL Clean can do the job for you. Ben ‘I reject your reality and substitute my own’ – Adam Savage

BTW why do you want to clear off the stored procedures that are not used, you can take help ofhttp://www.pinpub.com/ME2/Audiences…50&tier=4&id=CE9E01AE99334E1CBDC32C150AE6BE08 to get the information. But I suggest leave them on the database and do not give any EXEC permissions to the logins in order to tighten the security. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>