Share via

SQL Server Database log file max size setting problem

Balasubramaniyam G 21 Reputation points
2020-10-17T22:34:31.63+00:00

Hi,

Database was created with initial log file size 50 MB and max size set to 2 GB.
While restoring database from pre production environment to test environment, it failed due to transaction log file full due to "ACTIVE_TRANSACTION".
I increased max size to 100 GB. But After restore database, the max size is set to 2 GB again automatically.

Can you please help out how this max size changes automatically. Please advise if any settings modifies this max size of log file after restore happens.

Thanks
Bala

SQL Server | Other
0 comments
Accepted answer
  1. Ben Miller (DBAduck) 966 Reputation points
    2020-10-18T02:47:36.4+00:00

    On a restore of a database, the max size will always be what it was in the backup. You can have the restore command and the alter database modify file command right after so that transactions do not fill the transaction logs. 

    -- First do the restore
RESTORE DATABASE databasename FROM DISK='filename';
GO
USE master;
GO

ALTER DATABASE databasename
MODIFY FILE
(NAME = internal_log_name_from_db,
MAXSIZE = 100GB);
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.