SQL Query/JOB Error

Question

Post reply

SQL Query/JOB Error

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Leopard Ten Centuries Points: 1084 More actions · Answer 1

Hi Eirikur,

The result is,

TABLE_NAME MAX_VAL

dbo.MAP_MONTHNULL

dbo.MAP_HOUR 19606721

dbo.MAP_DAY 5997571

dbo.MAP 28265300

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 2

The maximum numbers are not near the thresholds, but rows that have been deleted and rolled back also increment the identity. Please run this query and post the results:

SELECT IDENT_CURRENT('MAP_MONTH') AS MAP_MONTH,

IDENT_CURRENT('MAP_HOUR') AS MAP_HOUR,

IDENT_CURRENT('MAP_DAY') AS MAP_DAY,

IDENT_CURRENT('MAP') AS MAP;

Leopard Ten Centuries Points: 1084 More actions · Answer 3

Leopard

Ten Centuries

Points: 1084

November 15, 2016 at 8:25 am

#1912541

Copy that... :Wow:

let me check first

Leopard Ten Centuries Points: 1084 More actions · Answer 4

Hi Stephanie,

A temporary solution so that the job running, our identity will be reset to '0' in the column on the table MAP_MONTH sys_id.

Please confirm if there are any impact on the Running Server?

Leopard Ten Centuries Points: 1084 More actions · Answer 5

Hi Stephanie,

I Attached result from : SELECT IDENT_CURRENT('MAP_MONTH') AS MAP_MONTH,

IDENT_CURRENT('MAP_HOUR') AS MAP_HOUR,

IDENT_CURRENT('MAP_DAY') AS MAP_DAY,

IDENT_CURRENT('MAP') AS MAP;

Rgds,

Gunadi

Leopard Ten Centuries Points: 1084 More actions · Answer 6

Hi All,

A temporary solution so that the job running, our identity will be reset to '0' in the column on the table MAP_MONTH sys_id.

Please confirm if there are any impact on the Running Server?

It's fine??

Rgds,

Gunadi

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

gunadi.arunanto (11/15/2016)
Hi Stephanie,
I Attached result from : SELECT IDENT_CURRENT('MAP_MONTH') AS MAP_MONTH,
IDENT_CURRENT('MAP_HOUR') AS MAP_HOUR,
IDENT_CURRENT('MAP_DAY') AS MAP_DAY,
IDENT_CURRENT('MAP') AS MAP;
Rgds,
Gunadi

The table MAP_MONTH has reach the maximum value of an integer (2147483647) and further inserts will therefore fail if the identity property isn't reset.

😎

TABLE_NAMEMAX_VAL
dbo.MAP_MONTHNULL
dbo.MAP_HOUR19606721
dbo.MAP_DAY5997571
dbo.MAP28265300

According to the counting query, that table is empty, is that correct? If that is the case then the simplest thing is to run

TRUNCATE TABLE dbo.MAP_MONTH

Leopard Ten Centuries Points: 1084 More actions · Answer 8

Hi Eirikur,

Yes Correct MAP_MONTH is Empty.

Thank you my friend,

1. So do you have step by step what can i to do the this case?

2. How to explain waht is causing this to happen?

Thank you Eirikur.

Regards,

Gunadi

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

gunadi.arunanto (11/16/2016)
Hi Eirikur,
Yes Correct MAP_MONTH is Empty.
Thank you my friend,

You are very welcome.

😎

1. So do you have step by step what can i to do the this case?

You need to run either of these commands, both will reset the identity value. The table should be empty!

Option 1

DBCC CHECKIDENT ( 'dbo.MAP_MONTH', RESEED , 1 ) ;

Option 2

TRUNCATE TABLE dbo.MAP_MONTH;

2. How to explain what is causing this to happen?

Part of the script is removing entries from the table without resetting the counter. When the counter reaches the maximum value of the INT data type, then any inserts will fail. How long it takes to happen is dependent on the number of rows inserted each time.

You could either change the data type from INT to BIGINT or add a step in the scripts that resets the identity counter if the table is empty

IF (SELECT COUNT(*) FROM dbo.MAP_MONTH) = 0

BEGIN

DBCC CHECKIDENT ( 'dbo.MAP_MONTH', RESEED , 1 );

END

Leopard Ten Centuries Points: 1084 More actions · Answer 10

Thank you so much my Friend.

So no impact in Production Server if I running both Script. it's correct??

Rgds,

Gunadi.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 11

gunadi.arunanto (11/16/2016)
Thank you so much my Friend.
So no impact in Production Server if I running both Script. it's correct??
Rgds,
Gunadi.

It is sufficient to run either of the scripts, no need to run both. There is no impact on the server.

😎

Run this in the right database

IF (SELECT COUNT(*) FROM dbo.MAP_MONTH) = 0

BEGIN

DBCC CHECKIDENT ( 'dbo.MAP_MONTH', RESEED , 1 );

END

and post the output here.

Leopard Ten Centuries Points: 1084 More actions · Answer 12

Noted!!! Thank you my Friend....!!!

Six Star i give for you.

:w00t::cool::rolleyes:

Leopard Ten Centuries Points: 1084 More actions · Answer 13

Hi Eirikur,

quick question, if i try running :

reseed 0 what happen??

reseed 1 what happen??

Thank you my Friend.

Rgds,

GNA.

Leopard Ten Centuries Points: 1084 More actions · Answer 14

Hi Eirikur,

For JOb Query it's done, Thank you very much my Friend but i have another question can we manual input about JOB Queary Failed east Nov 11 until 22 Nov 2016.

Please help and share How to and step for Insert data ( can i get the script for running manual insert the data ).

Rgds,

Gunadi Arunanto

Leopard Ten Centuries Points: 1084 More actions · Answer 15

Hi Eirikur,

For JOb Query it's done, Thank you very much my Friend but i have another question can we manual input about JOB Queary Failed since Nov 11 until 22 Nov 2016.

Please help and share How to and step for Insert data ( can i get the script for running manual insert the data ).