Recently I was discussing about removing transaction log files on the #sqlhelp hashtag on Twitter. The question was whether we could remove an existing transaction log file after adding a new one. During the course of the discussion, I learnt that the file that was being removed was the primary transaction log file of the database.
When you attempt to remove the primary log file of a database from Management Studio or using ALTER DATABASE command, you will receive the following error:
Drop failed for LogFile ‘dbLogTest_log’. (Microsoft.SqlServer.Smo)
The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020)
This is by-design. We do not allow the removal of the primary transaction log file of user databases. This was first introduced in SQL Server 2000.
Tibor (Blog), a SQL Server MVP, has already blogged about how to remove transaction log files using T-SQL commands here. So, I am not going to duplicate the post here again by providing the same T-SQL commands.
How to identify primary log file?
Execute the query below under the context of the database that you are interested in finding the log file information for. The file with the lowest file_id value is the primary log file.
select * from sys.database_files
where type_desc = ‘LOG’
order by file_id