How to get the logical name of the transaction log in SQL Server 2005
Solution 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.
Solution 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).
Solution 3
select Name
from sys.database_files
Generates,
SomeDb_Data
SomeDb_Log
SqlServer 2012
Solution 4
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)'
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on May 12, 2021Comments
-
Admin about 3 years
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. -
JohnLBevan over 11 yearsps. 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 putuse [MyDatabase]
to ensure you're working in the right DB. -
Yunnosch about 3 yearsWelcome to Stack Overflow! While this code may solve the question, including an explanation 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.