Just started getting the following error; can anyone please advise

System Error Detected: Database error #### SQLExecDirect failed with [[ODBC Error: state=22003, nativeErr=8115, err=[Microsoft][SQL Native Client][SQL Server]Arithmetic overflow error converting IDENTITY to data type int.] [ODBC Error: state=01000, nativeErr=3606, err=[Microsoft][SQL Native Client][SQL Server]Arithmetic overflow occurred.] ] for SQL=[SET NOCOUNT ON; BEGIN TRAN; INSERT INTO StatData (StatID, Value, Date) VALUES (19897, 19.51836, {ts N'2014-07-15 14:54:37'}); INSERT INTO StatData (StatID, Value, Date) VALUES (19870, 3.214874, {ts N'2014-07-15 14:54:37'}); INSERT INTO StatData (StatID, Value, Date) VALUES (19852, 1.33248, {ts N'2014-07-15 14:54:37'}); INSERT INTO StatData (StatID, Value, Date) VALUES (19899, 15.163592, {ts N'2014-07-15 14:54:40'}); INSERT INTO StatData (StatID, Value, Date) VALUES (19869, 0.022553, {ts N'[Trimmed]]

asked 15 Jul '14, 11:12

Tired_Techie's gravatar image

Tired_Techie
33114
accept rate: 0%

edited 04 Aug '16, 17:01

Doug's gravatar image

Doug ♦♦
10.1k112037


12next »

Hi --

Here is link to another support question that has the fix for the issue.

http://support.poweradmin.com/osqa/questions/1123/system-error-detected-database-errors-see-service-log

Thanks
Quinn

Please make sure to mark your questions accepted when you have your answer by clicking the gray check mark to the left of the answer.

link

answered 15 Jul '14, 14:16

Quinn's gravatar image

Quinn ♦♦
14.1k3925
accept rate: 37%

Hi Quinn

SQL command run successfully. We ran the SQL you advised in the other thread

Complete Steps were

  1. Stop main PA Mon Service
  2. Backup SQL Database
  3. Confirm amount of disk space required for this process. Which in my case was 230GB. So disk provisioning was initially 250GB and moved expand the disk on the fly during the SQL process to 300GB.
  4. run SQL command, (10hr long process with PA mon service stopped)

I cant upload any screen shots as I don't have enough Karma......??

link

answered 22 Jul '14, 06:38

Tired_Techie's gravatar image

Tired_Techie
33114
accept rate: 0%

Hi Quinn

The SQL Command is still running after 2 hours

My original SQL DB was 60gig and is now currently 80gig and growing. The SQL log file has grown from 10gig to 47gig and also is still growing...

link

answered 17 Jul '14, 12:24

Tired_Techie's gravatar image

Tired_Techie
33114
accept rate: 0%

Hi

Yes, the database will grow as the column "rowid" is increasing in size. The change in size is needed because the size of the IDs being generated are larger than the field "rowid" can handle. It has grown past what an int can hold and the field needs to be increased to the size of a bigint to handle the size of an id.

Thanks
Quinn

Please make sure to mark your questions accepted when you have your answer by clicking the gray check mark to the left of the answer.

link

answered 17 Jul '14, 14:28

Quinn's gravatar image

Quinn ♦♦
14.1k3925
accept rate: 37%

the SQL command failed due to lack of disk space on the T-log drive, can I re-run this command and it will pick up where it failed?

link

answered 18 Jul '14, 06:51

Tired_Techie's gravatar image

Tired_Techie
33114
accept rate: 0%

I'm not a database expert but what I understand when a command has failed everything is rolled back. Because in this case the table can't have some of the values at one size and others at a different size. The process will need to start from the beginning again, sorry about that!

One thought, you may want to run the database cleanup to remove older data that you may not need before running the command again. In the PASM console go to Setting -> Database Settings -> Database Cleanup. You can change the amount of days to keep the information. This process runs once a day but you could kick it off by restarting your service. It may still take a while to remove the older data as this is not a high priority process.

You will be able to regain space back from the database log file after your changes if you a backup.

Thanks
Quinn

Please make sure to mark your questions accepted when you have your answer by clicking the gray check mark to the left of the answer.

link

answered 18 Jul '14, 09:13

Quinn's gravatar image

Quinn ♦♦
14.1k3925
accept rate: 37%

Thanks for the reply Quinn, The log file ballooned by 130GB to 138GB in total when after 10 hours it smashed into the end of the disk which had thicked out. I rebooted the SQL server and the Database is now currently in recovery mode.

Fortunately I have a backup taken from before running the command. I may restore once its recovered.

I've been talking to our DBA team and we may approach this slightly differently, we are considering adding a new table in as BIGINT and the copying to this. Will see what happens.

link

answered 18 Jul '14, 09:18

Tired_Techie's gravatar image

Tired_Techie
33114
accept rate: 0%

Please let me know how this turns out and if your steps are good maybe it would be something to pass on to others.

Thanks
Quinn

Please make sure to mark your questions accepted when you have your answer by clicking the gray check mark to the left of the answer.

link

answered 18 Jul '14, 10:21

Quinn's gravatar image

Quinn ♦♦
14.1k3925
accept rate: 37%

Hi Quinn

What information does PA mon write to the StatData table?

link

answered 21 Jul '14, 06:13

Tired_Techie's gravatar image

Tired_Techie
33114
accept rate: 0%

There are two tables, a Statistic table that describes a statistic (and has a StatID), and a StatData table, where the actual data is stored (and refers to the StatID in the Statistic table).

Thanks
Quinn

Please make sure to mark your questions accepted when you have your answer by clicking the gray check mark to the left of the answer.

link

answered 21 Jul '14, 09:15

Quinn's gravatar image

Quinn ♦♦
14.1k3925
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×22
×3

Asked: 15 Jul '14, 11:12

Seen: 13,704 times

Last updated: 04 Aug '16, 17:01