Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to identify un-used DB objects for cleanup

Author  Topic 

NewDBA6
Starting Member

2 Posts

Posted - 2013-08-28 : 11:13:12
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-28 : 11:42:41
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

2 Posts

Posted - 2013-08-28 : 12:15:57
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
   

- Advertisement -