October 29, 2003 at 5:14 am
We have a large SQL server database and unfortunatly, one of the old developers has left a lot of VIEWS and Stored Procs on the database before it went into production.
Is there anyway of working out which of these are not used, apart from going through hundreds of ASP pages and the majority of the SQL database?
It gets complicated by the fact that the db also has a lot of DTS packages which reference these.
BTW - the database is SQL Server 2000 SP3, running on Windows 2000 Advanced Server.
October 29, 2003 at 5:32 am
- sql profiler for a while
- check its results
- sql profiler to support the next step and gather the errors
- rename 'tobedeleted' views to 'obsolete'
- after a while
- stop sqlprofiler
- a while after a while 😉 drop 'obsolete'-views
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 30, 2003 at 9:46 am
Would periodically querying the MASTER..SYSCACHEOBJECTS table also work? Place the procedure names from SYSCACHEOBJECTS into another table every hour or so for a few weeks, eliminating duplicate entries.
I suspect that procs which never show up in SYSCACHEOBJECTS coincide with the procs which are never called. So you could probably compare the items which occurred in SYSCACHEOBJECTS with those in SYSOBJECTS and find the obsolete ones.
October 30, 2003 at 4:40 pm
I would recommend using the Profiler method to see what's actually getting called. In addition though, you might want to consider using a grep program to sift through the ASP files to find references to the stored procedures/views/etc... and the sysdepends table in the database. I don't know what the new schema view is called that gives you the same information as the sysdepends table, but either one should tell you which tables/views/procedures are being referenced by other objects.
As for your DTS packages, I really don't know of a way. There might be something you could do using the DTS programming objects via VB or DMO, but I've never really tried to analyze DTS packages programmatically. One option there though is you might be able to save them all out as VB packages and then grep the lot of them for your views and procedures like the ASP pages.
Just some thoughts... A developer at a place I used to work at wrote a perl script that "grep'ed" the over 1000 procedures used in our application and reported which ones access which tables, but I haven't personally ever tried to directly tackle this problem.
Matthew Galbraith
October 30, 2003 at 11:48 pm
just to remind you sysdepends is not a full solution. (create a stored proc that calls an sp that does not yet exist. you'll get a notification)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 31, 2003 at 1:05 am
and whatever you do make sure that you monitor over the month end. Sometimes a proc lays dormant until the new month.
If it aint broke don't fix it!
Andy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply