Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help auditing Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 3:08 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:04 PM
Points: 636, Visits: 5,012
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!



Post #1364300
Posted Tuesday, September 25, 2012 5:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1364361
Posted Tuesday, September 25, 2012 5:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 5, 2014 10:21 AM
Points: 2,717, Visits: 3,855
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1364369
Posted Wednesday, September 26, 2012 10:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:04 PM
Points: 636, Visits: 5,012
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.


Post #1364816
Posted Thursday, September 27, 2012 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
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... )
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......




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1365241
Posted Thursday, September 27, 2012 10:22 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:32 AM
Points: 51, Visits: 186
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.
Post #1365373
Posted Friday, September 28, 2012 7:38 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
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.


Post #1365872
Posted Friday, September 28, 2012 8:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:55 AM
Points: 1,330, Visits: 19,306
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #1365917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse