How to identify the Primary Transaction Log file on a SQL Server

Question: How can I  identify the Primary Transaction Log file on a SQL Server database ?

Answer:  To identify the Primary Transaction Log file on a SQL Server database use this sql statement

Use DB_NAME
GO
select fileid,filename from sys.sysfiles where fileid = 2

By design, you can’t drop the Primary Transaction Log file, this message appears :

The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020) You can add Transaction Log files

To add extra Transaction Log files  - read this post - Add an extra SQL Transaction log file – ALTER DATABASE ADD LOG

Read More

Modify sql transaction log file size - increase or decrease

SQL Server - Find Default Data location and Default Log Location

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues