SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help auditing


Need help auditing

Author
Message
shahgols
shahgols
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2175 Visits: 5738
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!



Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19645 Visits: 7660
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
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6672 Visits: 4115
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
shahgols
shahgols
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2175 Visits: 5738
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.



Dennis Post
Dennis Post
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 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... :-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......



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
Arjen Krap
Arjen Krap
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 215
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.
Antares686
Antares686
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25712 Visits: 785
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.



jcrawf02
jcrawf02
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4748 Visits: 19324
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search