September 25, 2012 at 3:08 pm
Hi everyone,
Before I got to this new job, people were allowed to create many unused objects in our production environment. I want to start cleaning up the databases, so I thought about running a server side trace to capture all objects that have locks granted to them. But at this company, they used the nolock hint a lot in their applications. Then I thought about capturing the textdata from the trace, but that slows down some of our reports and it's out of the question. Then I looked at creating DML and DDL triggers on each table that would insert the name of the table to an audit table and then create a job that deletes the triggers after a minute. But triggers do not fire for select statements. So, I am out of ideas, does anyone have any other ideas on how I can go about auditing the objects usage in my databases? Thanks in advance!
September 25, 2012 at 5:19 pm
Old problem, no real new solutions. You're looking at a real mess.
For some further ideas, it may just be you need some helpful keywords for your google research, you've hit all the obvious methods.
Keywords: Obsolete, objects, schema, database.
You'll find a number of blog posts and ideas there, but there's no fixall solution. It's an iterative process to get it cleaned up and you need to be able to reverse the gears at a moment's notice if an error is made.
In the end though, you're going to end up with a (server side) trace and your bosses will not be happy, and they'll have to live with that. Otherwise you just end up maintaining a bunch of obsolete structures. You've hit most of the methods already.
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
September 25, 2012 at 5:31 pm
Like Kraig said, there isn't any one-size-fits-all solution, but one thing I found a while back just might help you.
The method described uses the system cache to find procs that aren't used in a specific amount of time. Unfortunately, if your environment allows users to use ad-hoc sql and access tables directly (which it sounds like they can) then you'll have more work to do, but this can get you close.
http://www.sqlservercentral.com/articles/Administration/2582/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 26, 2012 at 10:16 am
Thanks very much guys, I really appreciate the input. I guess I'm going to have to wait until we upgrade to SQL 2012 and then play around with Event Notification and SQL's Auditing module that doesn't exist in 2005.
September 27, 2012 at 7:22 am
Hi,
Maybe it's an idea to find out WHO are creating all these objects and why. Could be that they need their own test environment. Even if you find a method to filter out unused objects, you will just be getting new objects in their place. If they have no business creating objects, then look into restricting their access. (You won't be making any friends though... :-P)
Use profilet to filter CREATE statements.
Btw the profiler route can be very helpful. Sounds like you'll have to trace remotely though.
I have recently used profiler to find unused tables. Use a LIKE filter on textdata for each table. As you find tables, remove it from the filter. Remember to note down what's used and what hasn't been identified as being used. You will have to add other filters to weed out ad-hoc and replication results. Very tedious, but it'll get you their in the end.
As it turned out in my case. They had decided to create each table in each similar database to keep things conform. If the empty table isn't used, then so what. Would have been nice to know this before I spent some much time looking in to removing unused tables......
September 27, 2012 at 10:22 am
One way to find unused objects is to look at index usage statistics. However, beware that some indexes are used less frequently e.g. monthly reports.
September 28, 2012 at 7:38 am
First off is this just one database you are trying to clean up? How many objects are we talking and is it mission critical? Do you know what objects are definitely in use and where it is being used / by what? Really just start from what you know then she what is left, then maybe use object rights to effectively disable a few at the time and when you get a complaint you have a clue, if not mission critical. There are other ways to approach it but you might have to play dirty for a bit to get those answers.
September 28, 2012 at 8:49 am
Change all objects into a new schema, then see who complains and transfer those back...
(clarification: that's a joke)
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply