locked
After resume from suspect RRS feed

  • Question

  • When i resume my DB from Suspect with this query:

    EXEC sp_resetstatus 'Performance Test';
    ALTER DATABASE PerformanceTest SET EMERGENCY
    DBCC checkdb('PerformanceTest')
    ALTER DATABASE PerformanceTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB ('PerformanceTest', REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE PerformanceTest SET MULTI_USER

    i can't add any record in my Table

    how i can fix it?

    i create this database for test and i have more than 100,000,000 record on the table. my table was partitioned on 6 Drive


    Unfortunately, I can not speak English well I am a beginner programmer. I love the improvements

    Monday, August 12, 2013 5:49 AM

Answers

  • AS I said, you need to RESEED your identity value.

    Declare @Max Bigint

    Set @MAX = (Select MAX(identitycolumnname)+1 From tablFake1)

    DBCC CHECKIDENT ('tblFake1',  RESEED,  @MAX); --Find the max value and give the value.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Dalir Bajlany Monday, August 12, 2013 8:03 AM
    Monday, August 12, 2013 7:12 AM
    Answerer

All replies

  • With very limitted info you provided, this is unlikely an issue with database checkdb. 

    This is an indication that .NET provider is trying to insert a data into a table where you have a primary key on a column which has already the data. 

    You many need to provide the table name and the query you are trying. Also, you can check the value that it tries to insert is already there in the table or not.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 12, 2013 5:57 AM
    Answerer
  • My key field is automatic. I guess after the repair,key fields counter is reset!!!!!!!!

    Unfortunately, I can not speak English well I am a beginner programmer. I love the improvements

    Monday, August 12, 2013 6:02 AM
  • Try the below:

    DBCC CHECKIDENT ('<table name>', NORESEED);  --Check the current identity value
    DBCC CHECKIDENT ('<table name>',  RESEED, <your max value>); --Find the max value and give the value.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 12, 2013 6:07 AM
    Answerer


  • Unfortunately, I can not speak English well I am a beginner programmer. I love the improvements

    Monday, August 12, 2013 6:17 AM


  • Unfortunately, I can not speak English well I am a beginner programmer. I love the improvements

    I could not see any question or any clarification from your previous thread. 

    Please let us know if the issue has got resolved, if not we will be able to help you further.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, August 12, 2013 6:21 AM
    Answerer
  • i have one table with autonumber key field, and have a over 100,0000,000 record in this table(For test only)

    After a power failure and  shut down the server (table was on a test(inserting)), the table was suspect. After repairing the table, i can't insert any row. gives an error message (first photo).

    when i try insert data with StoreProcedure

    ALTER PROCEDURE [dbo].[usp_tblFake1_Insert]
    @FakeData1 ntext,
    @FakeData2	bigint,
    @FakeData3	int,
    @FakeData4 datetime,
    @FakeData5 nvarchar(50),
    @FakeData6 float
    
    AS
    
    BEGIN
    insert into tblFake1(FakeData1,FakeData2,FakeData3,FakeData4,FakeData5,FakeData6) values(@FakeData1,@FakeData2,@FakeData3,@FakeData4,@FakeData5,@FakeData6)
    select @@IDENTITY 
    END

    i give a 0 (i must give a new Key filed)


    Unfortunately, I can not speak English well I am a beginner programmer. I love the improvements

    Monday, August 12, 2013 6:39 AM
  • AS I said, you need to RESEED your identity value.

    Declare @Max Bigint

    Set @MAX = (Select MAX(identitycolumnname)+1 From tablFake1)

    DBCC CHECKIDENT ('tblFake1',  RESEED,  @MAX); --Find the max value and give the value.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Dalir Bajlany Monday, August 12, 2013 8:03 AM
    Monday, August 12, 2013 7:12 AM
    Answerer
  • God bless you!!!!!!!

    Very very very thanks


    Unfortunately, I can not speak English well I am a beginner programmer. I love the improvements

    Monday, August 12, 2013 8:03 AM