如何以编程方式获取SQL 2005事务日志的大小？

Question

我们的数据库使用固定的事务日志大小，我想编写一个应用程序来监控日志大小，以便在空间不足时，我们可以增加固定的事务日志。

有没有TSQL命令可以告诉我当前事务日志的大小和固定的限制？

- Greylurk

Answer 1

我使用了你的代码，但是在转换为整数时出现错误。 "Msg 8115，Level 16，State 2，Line 1 算术溢出错误将表达式转换为数据类型int." 所以无论何时出现 "*8" 我都将它改为 *8.0，现在代码完美运行。

SELECT (size * 8.0)/1024.0 AS size_in_mb
     , CASE
  WHEN max_size                                 = -1 
  THEN 9999999                  -- Unlimited growth, so handle this how you want
  ELSE (max_size * 8.0)/1024.0                  END AS max_size_in_mb
  FROM YOURDBNAMEHERE.sys.database_files
 WHERE data_space_id                            = 0           

- Myles Yamada

Answer 2

快速的谷歌搜索显示如下：

DBCC SQLPERF ( LOGSPACE )

为什么不在事务日志上使用自动增长？这似乎是一个更可靠的解决方案。

- Eric Z Beard

---

不是我的选择。DBA说静态大小的事务日志更高效。 - Adam Ness

我更多地是一个SQL开发人员，而不是严格的数据库管理员，但如果日志文件大小不正确并且需要经常增长，则性能应该会受到影响。增长的实际行为会消耗一些性能，但自动增长通常应该用于意外情况。 - Tom H

DBCC SQLPERF 需要在服务器上获得额外的权限，这就是为什么我选择了 sys.database 文件的答案。DBCC SQLPerf 的优点是可以同时提供所有数据库的信息... - Adam Ness

Answer 3

使用sys.database_files只能获得日志文件的大小，而无法获取其中日志的大小。如果您的文件已经达到固定大小，那么这并没有什么用处。DBCC SQLPERF（LOGSPACE）有点老派，但如果需要支持旧版本的SQL Server，则可以很好地使用它。

相反，您可以像这样使用dm_os_performance_counters表：

SELECT
    RTRIM(instance_name) [database], 
    cntr_value log_size_kb
FROM 
    sys.dm_os_performance_counters 
WHERE 
    object_name = 'SQLServer:Databases'
    AND counter_name = 'Log File(s) Used Size (KB)'
    AND instance_name <> '_Total'

- Martin Brown

Answer 4

这只是我凭记忆写的，所以你可能需要重新检查一下数学计算...

SELECT
     (size * 8)/1024.0 AS size_in_mb,
     CASE
        WHEN max_size = -1 THEN 9999999   -- Unlimited growth, so handle this how you want
        ELSE (max_size * 8)/1024.0
     END AS max_size_in_mb
FROM
     MyDB.sys.database_files
WHERE
     data_space_id = 0   -- Log file

从系统视图中，您可以获得更多信息，例如增长增量、日志是否设置为自动增长以及是按特定数量还是按百分比增长。

希望对您有所帮助！

- Tom H.

Answer 5

对于 SQL 2008 及更高版本，FILEPROPERTY 还公开了文件内使用的空间量，并且比其他答案简单得多:

select [Name], physical_name [Path], CAST(size AS BIGINT)*8192 [TotalBytes], CAST(FILEPROPERTY(name,'SpaceUsed') AS BIGINT)*8192 [UsedBytes], (case when max_size<0 then -1 else CAST(max_size AS BIGINT)*8192 end) [MaxBytes]
from sys.database_files

- James

Answer 6

如果您真的需要坚持使用固定大小的事务日志，我建议将其设置为合理的大小，留有一些余地，然后执行以下两个操作之一：

• 如果您不需要实现时间点恢复，请将数据库恢复模式设置为简单模式。简单来说，它将允许事务日志“自我回收”空间。

或者

• 如果您必须保持完整恢复模式，请安排一个定期备份事务日志的作业。这将释放事务日志中的空间，并在需要时允许您进行时间点恢复。

此外，也许您会发现以下文章有用：如何防止 SQL Server 数据库的事务日志意外增长。

- Diego