Forum Discussion

wsunarko's avatar
wsunarko
Copper Contributor
Mar 07, 2025
Solved

How to add existing log files to a newly created filegroup ?

I have two log files which are on different drives A: \logfile1.ldf & B:\logfile2.ldf which are not part of a file group. I want to remove logfile2.ldf , but I am unable to remove it because it is ...
  • petevern's avatar
    Mar 08, 2025

    Transaction log files are not part of a filegroup, only MDF and NDFs.  Transaction log is managed separately.

    You should properly remove logfile2.ldf instead of trying to add it to a filegroup.

    • Check log file usage
      DBCC LOGINFO
      If there are active VLFs in logfile2, means it is still in use

    • Make logfile1 the active (primary) one and force SQL Server to stop using the logfile2
      (adapt the size and filesize if needed)
      USE [YourDatabase];
      GO
      ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = 'logfile1', SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50MB);
      GO

    • Shrink logfile2
      DBCC SHRINKFILE ('logfile2', EMPTYFILE);

    • Force SQL Server to switch log files to logfile1
      CHECKPOINT GO BACKUP LOG [YourDatabase] TO DISK = 'NUL';
      GO
      DBCC SHRINKFILE ('logfile2', EMPTYFILE);

    • Try to remove logfile2
      ALTER DATABASE [YourDatabase] REMOVE FILE logfile2;

    Make sure you have a full database and log backup before making these changes!

Resources