Preparing old SQL Servers for migration

%3CLINGO-SUB%20id%3D%22lingo-sub-3175715%22%20slang%3D%22en-US%22%3EPreparing%20old%20SQL%20Servers%20for%20migration%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3175715%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20inherited%20two%202008R2%20SQL%20Servers%2C%20and%20am%20facing%20thousands%20of%20tables%2C%20views%2C%20and%20stored%20procedures%20that%20have%20not%20been%20documented%20or%20cleaned%20up%20since%20the%20very%20beginning.%20As%20a%20first%20step%2C%20I%20am%20trying%20to%20figure%20out%20a%20way%20to%20identify%20unused%20objects.%20I%20have%20already%20gone%20through%20several%20rounds%20with%20the%20DMVs%20using%20several%20approaches%20discussed%20in%20different%20forums.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%3A%20Does%20anyone%20know%20an%20easy%20to%20deploy%20a%20solution%20that%20would%20allow%20me%20to%20skip%20the%20fiddling%20with%20SQL%3F%20Most%20of%20the%20common%20monitoring%20tools%20seem%20to%20focus%20on%20performance%20only.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20highly%20appreciated.%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EAlbert%20Fabritius%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3182721%22%20slang%3D%22en-US%22%3ERe%3A%20Preparing%20old%20SQL%20Servers%20for%20migration%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3182721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1309470%22%20target%3D%22_blank%22%3E%40Space_Bert%3C%2FA%3E%26nbsp%3B%2C%20not%20really.%20You%20can%20use%20the%20SSMS%20dependency%20viewer%20to%20see%2C%20where%20an%20object%20is%20used%2C%20e.g.%20a%20table%20in%20which%20view%20or%20procedure.%20But%20this%20can't%20tell%20you%20which%20table%2Fview%20is%20used%20in%20which%20application%20or%20ad-hoc%20query.%3C%2FP%3E%3CP%3EYou%20can%20rename%20tables%2Fviews%2FSP%20and%20wait%2C%20until%20one%20user%2Fapplication%20complains%20about%20a%20missing%20object.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOlaf%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3183591%22%20slang%3D%22en-US%22%3ERe%3A%20Preparing%20old%20SQL%20Servers%20for%20migration%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F792867%22%20target%3D%22_blank%22%3E%40olafhelper%3C%2FA%3E%26nbsp%3Bthanks%20a%20lot.%20I%20feared%20as%20much.%20Would%20you%20happen%20to%20know%20if%20the%20absence%20of%20a%20table%20that%20is%20listed%20in%20the%20DMV%20%22sys.tables%22%20in%20the%20DMV%20%22sys.dm_db_index_usage_stats%22%20implies%20that%20the%20table%20has%20not%20been%20used%3F%20I%20know%20the%20usage%20stats%20are%20reset%20upon%20starting%20the%20instance.%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20thanks%20a%20lot%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I inherited two 2008R2 SQL Servers, and am facing thousands of tables, views, and stored procedures that have not been documented or cleaned up since the very beginning. As a first step, I am trying to figure out a way to identify unused objects. I have already gone through several rounds with the DMVs using several approaches discussed in different forums. 

My question is: Does anyone know an easy to deploy a solution that would allow me to skip the fiddling with SQL? Most of the common monitoring tools seem to focus on performance only.

Any help would be highly appreciated.

Best regards,

Albert Fabritius

2 Replies

@Space_Bert , not really. You can use the SSMS dependency viewer to see, where an object is used, e.g. a table in which view or procedure. But this can't tell you which table/view is used in which application or ad-hoc query.

You can rename tables/views/SP and wait, until one user/application complains about a missing object.

 

Olaf

@olafhelper thanks a lot. I feared as much. Would you happen to know if the absence of a table that is listed in the DMV "sys.tables" in the DMV "sys.dm_db_index_usage_stats" implies that the table has not been used? I know the usage stats are reset upon starting the instance. 

Again, thanks a lot for your help!