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.
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 procsSELECT * 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. |
|
|
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 |
|
|
|
|
|
|
|