SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to identify un-used DB objects for cleanup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NewDBA6
Starting Member

USA
2 Posts

Posted - 08/28/2013 :  11:13:12  Show Profile  Reply with Quote
Hello my friends, I am new to db discussion forums. This is my first post. :)

I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company. Thank you in advance :)

Regards,

NewDBA6

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/28/2013 :  11:42:41  Show Profile  Reply with Quote
quote:
Originally posted by NewDBA6

Hello my friends, I am new to db discussion forums. This is my first post. :)

I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company. Thank you in advance :)

Regards,

NewDBA6

None of what I am saying below can be used to include an object for deletion. It can be used to exclude something as a candidate for deletion.

You can use sys.dm_db_index_usage_stats to get an indication for tables.
SELECT OBJECT_NAME(object_id),* FROM 
sys.dm_db_index_usage_stats WHERE database_id = DB_ID();
Look in procedure cache for recent usages of stored procs
SELECT * FROM sys.syscacheobjects WHERE dbid = DB_ID()
This is not fool-proof; if the process cache was freed or if the server was restarted, or if SQL OS decided to dump a plan, it wouldn't show up.

Regardless of how you determine something is unused, before you delete something, take a good backup, rename the object to see what breaks etc.
Go to Top of Page

NewDBA6
Starting Member

USA
2 Posts

Posted - 08/28/2013 :  12:15:57  Show Profile  Reply with Quote
James, thank you so much for that valuable information and code. it really does help :) Any other input from others would be great as well! thanks!

Regards,

NewDBA6
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000