Need help auditing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Need help auditing

Need help auditing

Rate Topic

Display Mode

Topic Options

Author

Message

shahgols

Posted Tuesday, September 25, 2012 3:08 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:12 AM
Points: 578, Visits: 3,258

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

Evil Kraig F

Posted Tuesday, September 25, 2012 5:19 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 5,688, Visits: 6,142

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

Jason Selburg

Posted Tuesday, September 25, 2012 5:31 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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

shahgols

Posted Wednesday, September 26, 2012 10:16 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:12 AM
Points: 578, Visits: 3,258

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

Dennis Post

Posted Thursday, September 27, 2012 7:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:27 AM
Points: 109, Visits: 290

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... Tongue

)
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.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Post #1365241

Arjen Krap

Posted Thursday, September 27, 2012 10:22 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, April 07, 2013 1:20 PM
Points: 51, Visits: 161

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

Antares686

Posted Friday, September 28, 2012 7:38 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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

jcrawf02

Posted Friday, September 28, 2012 8:49 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:29 AM
Points: 2,551, Visits: 18,885

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 »

Permissions