March 29, 2011 at 1:20 pm
Hello!
I have a database that was ported over from a SQL 2000 system a while ago, and I have an endless scrolling list of Stored Procedures that I am all but certain are not fully being used. I have such a script to look for obsolete indexes in a db, but is there something out there that will identify (by say a given number of days or some other kind of input parameter) any objects in my db that have not been used in a lifetime or longer?
I've checked the script library out here, but haven't had much luck in finding something.
Many thanks in advance!
🙂
March 29, 2011 at 2:23 pm
To find which procs are obsolete, you need to start logging the execution of your procs and do it for a few months (some may only be called monthly, quarterly and possibly even annually).
Then from the logged information that you have gathered, start devising a plan to remove the obsolete objects.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2011 at 3:42 pm
Yeah, I figured this would be the only way. It's not running on an Enterprise Edition right now, so there has been no CDC type data accumulating over these past months.
I'll keep looking for a way to DIY this, but I am still learning how to best code processes.
Thanks
March 30, 2011 at 8:59 am
You can do this by running a server-side trace as well. You can trace the RPC:Completed & SP:Completed events. The issue is that you need to run it for a year or more because you could have yearly reports or processes that you miss if you don't wait that long.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2011 at 3:20 pm
The methods mentioned for capturing stored procedure usage are definitely good. Whenever you decide to remove the stored procedures, you might consider first making a backup copy of the ones you want to remove. I'd recommend creating a metadata-only database that contains all objects in the database. Don't copy any data, so space requirements would be minimal. Capturing usage data for a year is definitely wise, even then there's always a chance you didn't catch everything.
Brian Kukowski
April 4, 2011 at 3:25 pm
I like the idea of creating a procs database.
Another approach to identifying unused procs is to work with the application dev team and verify by proc name which procs are being called from code or reports and which ones are not.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2011 at 4:13 pm
Don't forget to think about running this for a year to catch those once a year processes.
If you think about removing anything, make sure you get a script of the proc, AND all permissions, including roles that can access it.
April 4, 2011 at 4:17 pm
Steve Jones - SSC Editor (4/4/2011)
Don't forget to think about running this for a year to catch those once a year processes.If you think about removing anything, make sure you get a script of the proc, AND all permissions, including roles that can access it.
Or do what I did at one location. Rename everything to start with zzz_. Get the webdudes to do a TRY/CATCH on their code that went to a proc with EXECUTE AS dbo permissions, which would rename any object back to what it was without the zzz_ (procs only), then try again. If that proc reported it couldn't do the name change (or the proc was already there), all necessary errors percolated up. Sure we had a few glitches but we stripped out about 2/3's of the mess that way.
After the year-end reporting, they were going to script and remove all the leftover zzz_ procs. I can't tell you from there, I'd already swapped contracts.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 4:27 pm
Craig Farrell (4/4/2011)
Steve Jones - SSC Editor (4/4/2011)
Don't forget to think about running this for a year to catch those once a year processes.If you think about removing anything, make sure you get a script of the proc, AND all permissions, including roles that can access it.
Or do what I did at one location. Rename everything to start with zzz_. Get the webdudes to do a TRY/CATCH on their code that went to a proc with EXECUTE AS dbo permissions, which would rename any object back to what it was without the zzz_ (procs only), then try again. If that proc reported it couldn't do the name change (or the proc was already there), all necessary errors percolated up. Sure we had a few glitches but we stripped out about 2/3's of the mess that way.
After the year-end reporting, they were going to script and remove all the leftover zzz_ procs. I can't tell you from there, I'd already swapped contracts.
You reminded that I had done a similar thing at one place. Rename the procs that seem to be unused, starting with dev. Do a full QA cycle and then percolate the changes up to production. If you can get the QA team to do a full system test with the latest stable version of the product - you could eliminate a lot of "mess" that way too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2011 at 5:40 pm
Cannot thank you all enough for this great chunk of knowledge! I am going to see if we cannot do this prior to placing the system into test for the customer.
I'll let you know how it goes!
April 4, 2011 at 7:59 pm
You're welcome and good luck.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply