|
|
 |
 |
Joined: 3/28/2012
Posts: 41
|
|
|
Good afternoon,
A very strange thing just happened. Both the orderid and the customerid fields in our database just jumped by 1000 each...sql view of smith_storeorders sorted by orderid descending:
OrderID CustomerID OrderDate
8243 2241 2012-07-26 10:29:09.787
7244 1242 2012-07-26 09:09:40.673
7243 1241 2012-07-26 09:04:35.760
How can this happen? We have no one manually entering orders.
Thank you,
|
|
|
|
 | |
|
|
 |
 |
Joined: 3/11/2010
Posts: 1979
|
|
|
Hi Scot,
The orderid and customerid are defined as identity fields in sql server which means that they are automatically generated by sql server when an insert on the table occurs. The following are things I can think of that would explain the gap in ids in your database:
- Data import to the customer or order table
- Records were inserted to the customer or order table
- In SQL server, Identity seed value changed on the customer and order table
Al;so, check your sql server log for any errors, inserts, or imports.
Hope this helps!
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
|
Joined: 3/28/2012
Posts: 41
|
|
|
That would only make sense if the records inserted to cause the jump were evident. They are not. There are no records, for either table, between the numbers shown in my listing supplied. It has taken us a month to create 1200 orders in the system on our store. There is nothing that I know have that would have written, and then immediately deleted, a thousand records in two tables in the space 1 hour and 20 minutes which is the time between the two orders on either side of the gap. I have also checked the log and there are no errors or indications of any other issue at the time.
I understand that this may be difficult to troubleshoot and am willing to write it off as a random occurrence as it does not appear that it will affect the system adversely but I thought that you should know about it.
|
|
|
|
| |
|
|
|
|
Joined: 3/28/2012
Posts: 41
|
|
|
This happened again today. But it happened after our hosting company restarted SQL Services because of a different issue. We think that this may have caused the jump as the previous time was also after a database restart.
Is there a way to shut down the store properly before doing a restart like that to ensure that we do not have 1000 record jumps in our orderids? We tried reseeding but before we could do so, another order came through and it then goes back to the highest number...
|
|
|
|
| |
|
|
|
|
Joined: 3/11/2010
Posts: 1979
|
|
|
The best way to shut down your store if your doing database maintenance is to stop the site using IIS Manager. This will prevent any new transactions to the database.
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
|
Joined: 3/28/2012
Posts: 41
|
|
|
This has now happened again with no evidence of a database shutdown. I need to find out what is causing this as our orderid numbers are jumping too fast. What is our next step in troubleshooting this?
|
|
|
|
| |
|
|
|
|
Joined: 3/11/2010
Posts: 1979
|
|
|
Scot,
I think the issue is with your sql server or the table itself, I would start by reviewing your sql server logs and windows logs. If you dont find anything useful in the logs try dropping and recreating the table that is skipping identity values.
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
|
Joined: 3/28/2012
Posts: 41
|
|
|
Scott- are you telling me I should drop and recreate my smith_storeorders table? I do not know a huge amount about SQL Server but won't that delete the couple thousand orders that I have in there as well as cause data link issues to all of the associated orders tables?
|
|
|
|
| |
|
|
|
|
Joined: 3/11/2010
Posts: 1979
|
|
|
Scot,
Important: Before you do anything make sure you have a full backup of your database.
When an order is successfully completed in the cart, the cart calls a stored procedure titled "Smith_AddStoreOrders" which inserts a new record in the orders table. The stored proc "Smith_AddStoreOrders" inserts one record in the orders table. The orderid is defined as an identity field in sql server which means that it is automatically generated by sql server when an insert on the table occurs. The issue you are having where your sql server skips identity values is not a bug in the cart but a problem with your sql server or in the orders table.
I googled the issue you are having where your sql server skips identity values and didnt come up with anything useful. The following are the 2 things I can think of that may be an issue:
- Your orders table is corrupted causing the identity values to skip
- A problem in your sql server that is causing the identity values to skip
To try an troubleshoot the issue please do the following:
- Check your sql server log for any errors
- Check your dnn event viewer for any errors
- Check your windows event viewer for any errors
Also, please post a copy of your "Smith_AddStoreOrders" stored proc so i can see it to make sure that it hasnt changed from the standard one delivered in the cart.
I recommend working with a DBA on this or someone who knows sql server very well. Before you do anything make sure you have a full backup of your database.
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
|
Joined: 3/11/2010
Posts: 1979
|
|
|
Also, in SSMS go into design mode on the orders table and check your identity seed values to make sure they havnt changed.
Scott Kelly
Project Manager
|
|
|
|
| |
|
|
|
|
Joined: 3/28/2012
Posts: 41
|
|
|
I believe that I have found the issue and you may want to warn your other customers. I recently moved to PowerDNN hosting and they are now utilizing SQL Server 2012. There is a known, but little documented bug relating to a new feature. It appears that it reseeds tables with integer identity adding 1000 when the SQL Server service is restarted. The issue can be found here: http://connect.microsoft.com/SQLServe...
PowerDNN were kind enough to place the workaround code in the Master database for me and so far it seems to have stopped the problem. The workaround is a stored procedure that executes whenever the server is started and reseeds the table to 1 past the max identity in the table already. This needs to be done for every table that is being affected.
If I do not post again in this thread over the next couple days then this was the problem.
|
|
|
|