Remove unwanted multiple ldf files in sql server

Question

Hi,

I have to create a procedure to remove unwanted ldf files in sql servers.

DBCC loginfo('DBName') provides the VLF status.
Based on this i need to select the log file name which are not in use and create a procedure to remove ldf file.

Thanks
Shashikala

Answer 1

See:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/delete-data-or-log-files-from-a-database?view=sql-server-ver15#TsqlProcedure

Answer 2

Hi Shashikala,

We can use Alter database statement and add the REMOVE FILE clause and specify the file that we want to remove. For example:

ALTER DATABASE [MultipleLogFiles] REMOVE FILE [MultipleLogFiles_log_1]
GO

Please refer to Drop unwanted (secondary) SQL Server transaction log files to get more detail steps.

If the response helped, do "Accept Answer" and up vote it.

Best regards.
Cathy

Answer 3

Assuming that you are on SQL server 2017 or higher, you can use the new dmv instead of DBCC LOGINFO. You can query that to find out which file(s) has no used VLFs. Based on that (using a cursor to loop the result from the query) you can generate the command to delete the file and execute that command using dynamic SQL. Not particular difficult T-SQL programming. Here's the query that your cursor will use, to get you started:

SELECT 
 file_id
,SUM(CAST(vlf_active AS tinyint)) AS status_of_file
FROM sys.dm_db_log_info(DB_ID()) 
GROUP BY file_id