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 not empty.
I tried emptying it by shrinking , but because it is not part of the filegroup, it failed.

When I tried to add the files to a newly created filegroup, using

ALTER DATABASE 
ADD FILE( name= 'logfile1', filename='A:\logfile1.ldf')
TO FILEGROUP LOGFILE
I got an error message
The logical file name "logfile1" is already in use. Choose a different name.

So, how can I add the log files to filegroup 'LOGFILE'?

Thank you.

 

 

filegroup
Logs
  • petevern's avatar
    petevern
    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!

3 Replies

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Mar 10, 2025

    When I tried to add the files to a newly created filegroup

    Data and log files are different; log files are not assoziated to a file group; remove "TO FILEGROUP LOGFILE"

     

     

     

  • petevern's avatar
    petevern
    Brass Contributor
    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