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

Question

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.

Leave a Comment Cancel reply