Recently i had decided to enable the Data Collection feature in sql server 2008. This is a very neat feature to capture some important statistics about the database and queries. When you enable this feature it also creates SSIS packages. One day when we were cleaning up all the unwanted SSIS packages, we decided not to use the Data Collection feature hence removed all the SSIS packages. I encountered a problem while removing the SSIS packages, go some errors pertaining to Foreign Key constraints. This lead me to deleting the syscollection tables in the msdb database, this was a wrong thing to do i realised later. These system tables were deleted. Later i had to stop and start the dev server, once the dev server restarted i started getting errors while trying to expand the Management folder in SSMS. I realised that there were views in the msdb database which were referencing the system tables. This was kind of a mjor problem since msdb houses lot of important information related jobs,SSIS packages,policies. Then based on the article here http://feodorgeorgiev.com/blog/2010/03/how-to-rebuild-msdb/
i located the install msdb script, i used this to recreate all the data collector system tables, once this done my errors with SSMS went away. The take away was to completly understand all the related objects which are created for the new feature (here data collector) and plan the disabling of the feature in a organized way.
No comments:
Post a Comment