Msg 9002 The transaction log for database 'myDataBase' is full due to 'ACTIVE_TRANSACTION



  • There's a database of myDataBase in it, there's a table myTable in the table of about two million records. When attempting to add a column to this table

    ALTER TABLE dbo.myTable ADD myTable_id int identity(1,1) not null primary key
    

    I'm getting a mistake.

    Msg 9002 The transaction log for database 'myDataBase' is full due to 'ACTIVE_TRANSACTION'.**

    The report makes it clear that the transaction log magazine is overcrowded.

    After this, the log file increased:

    ALTER DATABASE myDataBase
    MODIFY FILE
        (NAME = myDataBase_log ,
         MAXSIZE = 170MB)
    GO
    

    and used the team:

    CHECKPOINT
        DBCC SHRINKFILE ('myDataBase_log')
    

    Before the team goes: ALTER TABLE dbo.myTable ADD myTable_id int identity(1,1) not null primary key log value 27MB after 150MB and still error Msg 9002. Indicate how to do this:

    • Increase the log file?
    • Is that the log file's behavior?


  • Yeah, it's normal behavior.

    SQLer immediately writes all changes you have made in the transaction log.

    Your ALTER requires a full recreation of the table - that is, you add a cluster index. Accordingly, the levy will record the removal of old data + the re-entry of them, with the concomitant re-establishment of all indices. So the volume of the log file should be increased to a minimum of 2x of the table (more than 4x).

    I'd put the size of the league in your place, not on the basis of the size of the base, but on the basis of an accessible spot on the disk (i.e. all free space minus the minimum reserve) or lifted the restriction at all.

    Table + Index size can be seen in View / Object Explorer Details in Management Studio.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2