SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc varchar (24)
--get info about transaction logs in each database.
IF ( OBJECT_id (N'tempdb..#dm_db_log_space_usage') is not null)
BEGIN
DROP TABLE #dm_db_log_space_usage
END
SELECT * INTO #dm_db_log_space_usage FROM sys.dm_db_log_space_usage where 1=0
DECLARE log_space CURSOR FOR SELECT NAME FROM sys.databases
OPEN log_space
FETCH NEXT FROM log_space into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = '
insert into #dm_db_log_space_usage (
database_id,
total_log_size_in_bytes,
used_log_space_in_bytes,
used_log_space_in_percent,
log_space_in_bytes_since_last_backup
)
select
database_id,
total_log_size_in_bytes,
used_log_space_in_bytes,
used_log_space_in_percent,
log_space_in_bytes_since_last_backup
from ' + @dbname +'.sys.dm_db_log_space_usage'
BEGIN TRY
exec (@SQL)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
FETCH NEXT FROM log_space into @dbname
END
CLOSE log_space
DEALLOCATE log_space
select a.database_id ,a.name ,
b.total_log_size_in_bytes/1024/1024.00 as total_log_size_in_mb,
b.used_log_space_in_bytes/1024/1024.00 used_log_space_in_mb,
b.used_log_space_in_percent ,
b.used_log_space_in_bytes/1024/1024.00 as used_log_space_in_mb
from sys.databases a
left join #dm_db_log_space_usage b on a.database_id=b.database_id
sql server 查询日志文件使用率
于 2024-08-19 13:41:41 首次发布