Question

tocid mysterious value jump

Laserfiche Version 9 Workflow Administration Security

Updated December 19, 2014

asked on December 8, 2014

My tocids on one server (Rio v9.1.1) mysteriously jumped to 2104885476 from 782180.

Anyone have insight as to why this happened and if it's a problem?

SELECT TOP 3000 [tocid], created
FROM [dbo].[toc]
order by tocid desc

tocid	created
...
2104885477	12/4/14 9:59 AM
2104885476	12/4/14 9:59 AM
782180	12/3/14 4:16 PM
782179	12/3/14 4:16 PM
...

Thanks in advance...

0 0

Replies

replied on December 8, 2014 • Show version history

What version of SQL are you using? Do you know of the SQL Server was restarted (once or many times) between 12/3 and 12/4?

Starting in SQL 2012, there is a reported identity value "jump issue" whenever the server restarts. More information can be found here. I haven't seen a jump that large though so I'm not sure if you're running into the same issue. The only concern is that the max value for an INT column in SQL Server is 2147483647 so if there's another jump in ID values, you could reach the max value for the column. The tocid column most likely can't just be changed to bigint because of constraints.

One recommendation would be to find all the new documents after 12/3 and migrate them to a temporary volume and export that volume, or just export the documents as a briefcase if there aren't too many. Then restore a backup of the repository database and volumes from 12/3. Ideally, the new documents you exported would be ones created just after the most recent backup before 12/4 had been taken. Then once you restore the backup, just import the new documents back in. Hopefully there won't be a jump in the tocid values this time around.

*Edit*

Could this also be an issue of having a runaway workflow?

Regards

1 0

replied on December 8, 2014

Thanks for the information Alexander.

We are on SQL 2008 R2, last restarted on 11/8, so it sounds like something else is going on. I was running a few queries in SSMS and LINQ against these tables on 12/3 but I can't see how that would cause this. Is that something to be wary of?

Unfortunately this is not a production box so I don't have backups of the repository, only the databases.

In place of restoring backups how horrible would it be to: stop the service, update tocid for all tables with tocid (I see 55, not sure if it exists by other names), reseed the identity and restart the service?

If this happened in a production repositry and we lost a few days before noticing I think restoring from a backup would be much more difficult (given active workflows, etc.) and would want to explore other options.

All things considered there *should* be enough tocids left to continue with the repository, I just want to understand what caused this so we can avoid it, especially in a production repository.

I don't see any indication of a runaway workflow on the server.

0 0

replied on December 8, 2014

I'm glad to hear this wasn't on a production server. Resetting the values for that identity column shouldn't require updating any of its foreign keys. You should be able to do it just on the column itself by changing the increment seed value, though I recall having to set identity to false, save it, and set identity to true before being able to modify the seed or increment values. It's good practice to stop the LFS service while make changes like this, but in this situation the worst case is that an INSERT fails.

0 0

replied on December 11, 2014

Hi Brian,

Simply reseeding (to 800000) resulted in "unreseeding" and SQL put back the high value seed. However, after I got rid of all the high tocid content (deleted through the LF client) I was able to reseed the toc table and new content respects the seed. This approach would not be an option on a prod box.

I am curious (ignoring workflow, audit trail, etc. implications for now) if I were to stop LFS, update all "high" tocids in all tables to lower values, reseed, and restart LFS if that would be an acceptable way to address this on a sever with real content. Hopefully I don't really need that answer but I would like to know how safe that approach is.

Rob

0 0

replied on December 11, 2014

I would rather briefcase out all the docs with high IDs, then delete them from the repository. Stop the server, reseed the TOC table identity value and restart the server. Then import the briefcase. That way I wouldn't have to worry about properly updating all tables that reference those high IDs, they documents would get new IDs on import.

0 0

replied on December 18, 2014

In case anyone is wondering I found what was causing the jump and it doesn't have anything to do with Laserfiche.

0 0

replied on December 18, 2014

What was it?

0 0

replied on December 19, 2014

There was a table for custom use that had been replaced by a view. The view uses the <SomeLFRepoDB>.toc table. There was also a job (batch, windows scheduler) bcp'ing data into this "table" on a daily basis that was still running. The import failed but in the process the identity got messed up.

0 0

You are not allowed to follow up in this post.

Question

Question

tocid mysterious value jump

Replies

Sign in to reply to this post.