Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thank you!

edit I'm un-tagging the response below as an accepted answer. I have converted the data type to BIGINT using this script 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)
share|improve this question

1 Answer 1

When you exhaust the upper bound of INT you will receive, for every new insert:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Outside of the MDW use case

Converting to BIGINT is far safer (IMHO) than what some people do - go back to 0 and fill in the gaps, or go to -2 billion and just delay dealing with it until you use the same number of values one more time. If 2 billion rows isn't enough, neither is 4 billion. If you use compression changing the data type for values < 2 billion will actually save you some space.

However you will come across issues if this is part of a primary key constraint, referenced by foreign keys, has other constraints, etc. You'll need to perform some extra work in addition to just changing the data type.

For MDW specifically

The problem with the solution above, for MDW specifically, is that the data collector package (SSIS Packages/Data Collector/PerfCountersUpload) includes some logic that relies on the underlying data type to be INT. So, the collector jobs fail because of this mismatch.

This should buy you some time:

DBCC CHECKIDENT('snapshots.performance_counter_instances', RESEED, -2147483648);

And between now and when you start approaching 0 again (you should set up some kind of monitoring to check the max value and send some kind of alert when you get close), go in and clean up and make sure there are no positive values.

Then, when you start approaching 2 billion again, clean out all the negative values, and reseed.

Is this annoying? Absolutely. I think this might be one of several reasons why you don't see a very large MDW adoption.

share|improve this answer
    
+1 to what Aaron has said. Do keep in mind that changing the data type for an identity column is going to suck as it's the clustered key so you'll have a lot of IO and some VERY big transactions to deal with. –  mrdenny Mar 4 '13 at 21:01
    
@mrdenny well, identity is not always the clustered key, though I will admit I don't know much about MDW's specific schema. :-) –  Aaron Bertrand Mar 4 '13 at 21:01
    
I'm making a rash assumption based on minutes of looking at the MDW schema months ago. I could totally be wrong. For the OPs sake I hope I am. I did a data type change on a 1B+ row table recently. It wasn't fun. –  mrdenny Mar 4 '13 at 21:04
    
@mrdenny right, just wanted to clarify your original comment, which could be taken as a generalization (and is often believed as one!) outside the context of MDW specifically. –  Aaron Bertrand Mar 4 '13 at 21:07
    
fair point Aaron. (OK, I've got no idea if that's the correct fair or not.) –  mrdenny Mar 4 '13 at 21:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.