Sample Header Ad - 728x90

MDW performance_counter_instances table running out of identity values

2 votes
1 answer
653 views
My MDW database's performance_counter_instances table has run out of identity values. Has anyone ever encountered this? Is it safe to change the data type of the identity field performance_counter_id from INT to BIGINT without breaking anything? Bonus Question - What is the impact of this field running out of identity values? I can't find anything useful about this issue online. I have converted the data type to BIGINT using [this script](http://pastebin.com/FSbRuu1v) and now all of the collection_set_2_upload jobs are failing with the following error message: >LKU - Lookup into performance_counter_instances to obtain performance_counter_id for all counter paths that get inserted.Outputs[Lookup Match Output].Columns[performance_counter_id] and reference column named "performance_counter_id" have incompatible data types. Please let me know if this is something that can be fixed or if I will have to just eventually recreate the MDW database again. This happens about once every 6 months in my environment. For now I've reseeded the identity to -2147483647 to buy some time. DBCC CHECKIDENT ('snapshots.performance_counter_instances', RESEED, -2147483647)
Asked by Alf47 (981 rep)
Mar 4, 2013, 04:18 PM
Last activity: Sep 3, 2016, 07:55 AM