Hi there,

On 5/2/17, we had a blip in our numbered orders.
We went from order ID 229734 and less than 40 minutes later went to order ID 230730. Somehow we skipped 996 order numbers.

Any ideas on why this might have occurred?

fhd

Hi

I've no idea how that would happen but have you checked your stats for that day to make sure there wasn't any spike in bot action (machine visits).

Andy

Please feel free to review / rate our software

We used to get this on Servelink servers, but I believe it was long ago fixed.
I can check my old tickets with them to see what the issue was, but I think they upgraded servers and it stopped.
Orders would jump by 1000 (about) and it happened 5 or 6 times.

David
ECT Power User

We use Rackspace and have a dedicated server. We've only been on this server for one year, so it's pretty new. Yes David, if you don't mind checking the old tickets to see what they thought it might have been, that would be so helpful.

And yes too, this isn't the first time it's happened to us either, it has happened at least once or twice in the past too.

thanks!

fhd

Looking over the old ticket, it seems to have to do with SQL server 2012

Message from Vince

quote:

---

The trouble seems to be that in SQL Server 2012 MS have introduced a new feature and at the same time an existing feature the "IDENTITY" has stopped working as it used to. So it's not really a case of MS not expecting the IDENTITY to be used this way as I'm sure it's used this way everywhere. I really would class this in the "BUG" category but that said, I've also seen other bugs reported in SQL Server that go unresolved for a long time. If we did code for this new sequence system we'd still have to support the IDENTITY system for older DB versions and it would be a lot of work. I've read about the workaround they mention and even though it is performance impacting, the server would have to be generating a lot of IDENTITY values for this to be an issue I would have thought. Really, this is what I would try first the "Trace flag 272" workaround and then monitor the server to see if performance really is impacted.

---

Response from servelink was they set the trace flag.
I have not seen the problem for a very long time.
Are you on SQL server 2012?

David
ECT Power User

Lots of info out there on a google search for - Trace flag 272 workaround

David
ECT Power User

Yes, that is what we are using, unfortunately

Well, I haven't seen any other impact other than the jumping order numbers. Did you have any other issues at that time with anything related?

fhd

The biggest issue we were having was processing and shipping orders.
Quite often, we would work off the last few numbers of an order.
What began to happen is we shipped order 34200 to the customer that actually ordered 33200.

Fst forward to now, that would not be an issue because we print a bar code at the top of orders and scan the bar code at the shipping bench to pull up the order. This ensures we never pull up the wrong order.

Upside was if a customer ordered last week and ordered again after numbers jumped, they got the impression that we were really busy -

If you have access to the database server, I think you can fix it. Or maybe by now, there is an update from Microsoft that you can apply that has a fix.

I guess we are lucky to have Servelink on top of things for us too.

David
ECT Power User

Thanks, David!
Unfortunately, Rackspace doesn't support the SQL, so any fix would have to be done by me kinda scary,

But I'll research now with the information you've given.
Your input has been much appreciated!

fhd

quote:

---

Your input has been much appreciated!

---

No problem, I enjoy help my fellow ECTers...

David
ECT Power User

This has happened to me today after restarting the server. Our order numbers jumped by 1000 which is going to provide a challenge internally on how we deal with orders.

Can anyone recommend a query that I can run in SSMS to set the ordID back to the number it's supposed to be? For instance it's showing up as 12516 when it should be 11516.

I'd hope to have this fixed before more orders start to come in.

Hi

I'm afraid I don't know of a way of reducing the order number but I'll see if anyone here has any suggestions.

Andy

Please feel free to review / rate our software

It's my understanding with the way the database work, you can never roll back a numbering sequence like that.
What about a backup - does the host have a database backup from just before the order numbers jumped?

While you are speaking with the host, I would show them this problem and ask them if they can set the trace flag.

:::EDIT::: - A google search and I came across this - BACKUP YOUR DATABASE and use at your own risk - https://stackoverflow.com/questions/510121/reset-autoincrement-in-sql-server-after-delete

I have a backup from yesterday, but there's been a lot of orders and changes since.

I think I can go into design mode and make the identity seed = NO.

Then I could use a query to change ordID 12516 to 11516. Then set the identity seed back to YES.

Does anyone know the correct syntax for the query?

I would strongly recommend you to leave things as they are. Rolling back the order ID's will almost certainly cause further issues.



* Database Migrations and Conversions*
* ASP to PHP Cart Conversions*
*Contact Us*
*Buy The PHP Capture Card Plugin*
*Rate Our Services/View Our Feedback*

I agree with Phil.
Look at it this way, your returning customers will see the new order numbers and say "wow - these folks are really busy"