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 ««123»»

Identifying Unused Objects in a Database Expand / Collapse
Author
Message
Posted Monday, September 11, 2006 6:40 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711

Right.. I guess you'd have to look real close at each object and make really sure it's not being used.  It could point you in the right direction I guesss...  You could identify for sure that an object is used, but not be 100% certain an object isn't used...

 

 

Post #307800
Posted Monday, September 11, 2006 6:42 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

Thanks, that you get attention to my wording (not many people captured it)

There are several tricks. When you define the suspicious objects that may not be used, you can add simple trigger for select, insert, update with simple insert to log table with info about the table beeng accessed, date, and user. In addition, I am lucky because we adapt the policy and management supporting me with it that all calls (even simple reports with 1 SQL statement) only by stored procedures.

)



Post #307801
Posted Tuesday, September 12, 2006 5:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299
Yesterday I run

select bucketid, cacheobjtype, objtype, objid, dbid     
 from master.dbo.SYSCACHEOBJECTS
 where  objid = 1718297181

at 10:30 am.

I do not understand why it keep lock the other process.

 

Post #307872
Posted Tuesday, September 12, 2006 5:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299

I do not understand I run the process

select bucketid, cacheobjtype, objtype, objid, dbid     
 from master.dbo.SYSCACHEOBJECTS
 where  objid = 1718297181.

 

It still lock the  process. My other scheduled task aplication in the server could not run due to the lock. Thanks.

Post #307876
Posted Tuesday, September 12, 2006 11:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539

Try running this instead:

select bucketid, cacheobjtype, objtype, objid, dbid     
 from master.dbo.SYSCACHEOBJECTS with(nolock)
 where  objid = 1718297181





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #307985
Posted Tuesday, September 12, 2006 4:07 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

Please try to read article carefully before trying.

#1. You can use "nolock" hint

#2. I did exclude where clause from the tests I was doing. Insert all rows to the temporary table and then get the objectid you are looking for. I show it for one object as an example at the beginning of the article. But in reality I never done it on the object by object bases.

Always all records from syscache table and then make an analysis




Post #308049
Posted Tuesday, September 11, 2007 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 7:40 AM
Points: 4, Visits: 12
I did a slight alteration to the way this works in order to better do what I wanted... Rather than doing the insert each time and then relying on the query at the end to determine if it had been in the cache at one point, I insert up front and then have the job delete from the table any references to that object if it's currently in the cache. This way, whatever is in the table at the end of my monitoring period are things that weren't in the cache at some point during the period.

Granted, it isn't fool-proof going off the cache, but it at least helps narrow things down.

--Kevin Fairchild
Post #398170
Posted Wednesday, July 23, 2008 10:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:35 PM
Points: 23, Visits: 128
Okay, this works for SS2005. What exists to do the same kind of thing in SS2000?
Post #539490
Posted Wednesday, September 17, 2008 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2008 1:04 PM
Points: 1, Visits: 1
I saw this tool that finds Unused Stored Procedures:
[url=http://www.kellermansoftware.com/p-35-unused-stored-procedures.aspx][/url]


Post #571086
Posted Thursday, July 30, 2009 1:54 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 178, Visits: 618
Sorry to resurrect this old thread, but I"m in the middle of a project to upgrade our sql 2000 dbs to sql 2008. I'd like to also use this as an opportunity to get rid of any procedures that aren't being used.

For the most part, I like the solution. I'm curious to hear everyone's input though about the frequency he ran the update code. Every 15 minutes? Ouch! Can somoene explain to me why this couldn't just as easily be run every hour or every 3 or 4 hours. If I were to run that code on my dbs every 15 minutes for a month, and have a whole other issue to contend with!
Post #762737
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse