如何在SQL Server 2005中获取事务日志的逻辑名 [英] How to get the logical name of the transaction log in SQL Server 2005

问题描述

我正在尝试编写一个T-SQL例程，该例程根据数据库的逻辑名称使用 DBCC SHRINKFILE 收缩事务日志文件. DB_NAME() 函数为您提供数据库的逻辑名称.事务日志是否有一个等效的日志?如果没有，还有其他方法可以获取此信息吗?事务日志的默认名称为<<Database Name>>_log，但我宁愿不依赖于此.

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.

推荐答案

您可以使用:

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

对于任何database_id，日志文件的类型均为1，并且可以在sys.master_files中找到所有数据库的所有文件.

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

我应该指出，您不应该例行收缩日志.您的事务日志的大小应适当调整，以防止其增长，然后保持该大小.无法对事务日志进行即时文件初始化，并且在向其添加空间时必须将其清零，这是一种缓慢的顺序操作，会降低性能.

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.

这篇关于如何在SQL Server 2005中获取事务日志的逻辑名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！