Forum Discussion
wsunarko
Mar 07, 2025Copper Contributor
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 ...
- 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!
- Check log file usage
petevern
Mar 08, 2025Brass Contributor
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!