How to get the logical name of the transaction log in SQL Server 2005

Question

I am trying to write a T-SQL routine that shrink the transaction log file using DBCC SHRINKFILE based on the logical name of the database. The DB_NAME() function gives you the logical name of the database. Is there an equivalent one for the transaction log? If not, is there some other way to get this information? The default name for the transaction logs is <<Database Name>>_log, but I would rather not rely on this.

Answer 1

You can use:

SELECT name
FROM sys.master_files
WHERE database_id = db_id()
  AND type = 1

Log files have type = 1 for any database_id and all files for all databases can be found in sys.master_files.

EDIT:

I should point out that you shouldn't be shrinking your log on a routine basis. Your transaction log should be sized appropriately to keep it from ever having to grow, and then left at that size. The transaction log can not be instant file initialized and has to be zero'd out when space is added to it, which is a slow sequential operation that degrades performance.

Answer 2

Assuming a standard database (eg only one log file), the log file is always file_id = 2. This applies even if you have multiple data files (id = 3+ for NDFs).

The DBCC also takes the file id too. So, DBCC SHRINKFILE (2...) will always work. You can't parameterise inside the DBCC so this avoids dynanmic SQL. If you want the name, use FILE_NAME(2).