SQL Query/JOB Error

  • Hi Eirikur,

    The result is,

    TABLE_NAME MAX_VAL

    dbo.MAP_MONTHNULL

    dbo.MAP_HOUR 19606721

    dbo.MAP_DAY 5997571

    dbo.MAP 28265300

  • 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;

  • Copy that... :Wow:

    let me check first

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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

  • Thank you so much my Friend.

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

    Rgds,

    Gunadi.

  • 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.

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

    Six Star i give for you.

    :w00t::cool::rolleyes:

  • Hi Eirikur,

    quick question, if i try running :

    reseed 0 what happen??

    reseed 1 what happen??

    Thank you my Friend.

    Rgds,

    GNA.

  • 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

  • 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 ).

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

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