31

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.

skaffman
  • 398,947
  • 96
  • 818
  • 769
DanM
  • 323
  • 1
  • 3
  • 6

4 Answers4

48

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.

Jonathan Kehayias
  • 3,402
  • 1
  • 23
  • 23
  • 2
    ps. Note that different databases on the same instance may use the same log name. As a result in addition to putting `DBCC SHRINKFILE('logical log name', size)` you should first put `use [MyDatabase]` to ensure you're working in the right DB. – JohnLBevan Jan 03 '13 at 15:49
15

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).

gbn
  • 422,506
  • 82
  • 585
  • 676
6
select Name
from sys.database_files

Generates,

SomeDb_Data  
SomeDb_Log

SqlServer 2012

SAm
  • 2,154
  • 28
  • 28
2
DECLARE @command varchar(1000) 
SELECT @command = 'USE [?] DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)' 
EXEC sp_MSforeachdb @command

--OR simply

EXEC sp_MSforeachdb 'USE [?] DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)'
dbc
  • 104,963
  • 20
  • 228
  • 340
Vinit Mundhra
  • 21
  • 1
  • 2
    Welcome to Stack Overflow! While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch May 11 '21 at 06:03