1.日志状态

use master;
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases

use master;
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases

name	log_reuse_wait	log_reuse_wait_desc	解释	备注
xxx	0	NOTHING	无
xxx	1	checkpoint	检查点
BF_POS	2	LOG_BACKUP	日志备份	说明日志待备份
xxx	3	Active backup or restore	活动备份或还原
WSS_Content	4	ACTIVE_TRANSACTION	活动事务	说明有事务没有提交 使用 USE abc; DBCC OPENTRAN 查看最早未提交的事务(可尝试杀掉最早未提交的事务).
xxx	5	Database mirroring	数据库镜像
xxx	6	Replication	复制	2021.03.24.启用cdc若agent服务停止也会造成Replication状态
xxx	7	Database snapshot creation	数据库快照创建
xxx	8	Log scan	日志扫描
xxx	9	AVAILABILITY_REPLICA	辅助副本正将事务日志应用到数据库	若配置了ag请确认所有节点和db状态都正常

日志无法进行transaction

• 以下都不行

CHECKPOINT

LOG_BACKUP

ACTIVE_BACKUP_OR_RESTORE

ACTIVE_TRANSACTION

DATABASE_MIRRORING

REPLICATION

DATABASE_SNAPSHOT_CREATION

LOG_SCAN

AVAILABILITY_REPLICA

OLDEST_PAGE

XTP_CHECKPOINT

OTHER TRANSIEN

• 查看数据库日志状态

SELECT [name] ,[log_reuse_wait_desc] FROM master.sys.databases WHERE [name]='hantest'

SELECT [name] ,[log_reuse_wait_desc] FROM master.sys.databases WHERE [name]='hantest'

1.0清理日志文件的方法

官方文档，https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

USE [storm]  
GO  
ALTER DATABASE storm SET RECOVERY SIMPLE WITH NO_WAIT  
 GO  
ALTER DATABASE storm SET RECOVERY SIMPLE   --简单模式  
GO  
 USE storm
GO  
DBCC SHRINKFILE (N'storm_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定  
GO  
USE storm  
GO  
ALTER DATABASE storm SET RECOVERY FULL WITH NO_WAIT  
GO  
ALTER DATABASE storm SET RECOVERY FULL  --还原为完全模式  
GO

USE [storm]  
GO  
ALTER DATABASE storm SET RECOVERY SIMPLE WITH NO_WAIT  
 GO  
ALTER DATABASE storm SET RECOVERY SIMPLE   --简单模式  
GO  
 USE storm
GO  
DBCC SHRINKFILE (N'storm_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定  
GO  
USE storm  
GO  
ALTER DATABASE storm SET RECOVERY FULL WITH NO_WAIT  
GO  
ALTER DATABASE storm SET RECOVERY FULL  --还原为完全模式  
GO

DBCC SHRINKFILE (DBName_log, 1); --File name SELECT * FROM sys.database_files; query to get the file name

DBCC SHRINKFILE (DBName_log, 1); --File name SELECT * FROM sys.database_files; query to get the file name

1.1查看数据库恢复类型

select NAME, recovery_model_desc from sys.databases

select NAME, recovery_model_desc from sys.databases

1.1.1修改FULL类型

/*如果是FULL类型，修改为SIMPLE类型*/
ALTER DATABASE 数据库名字 SET RECOVERY SIMPLE
GO

/*如果是FULL类型，修改为SIMPLE类型*/
ALTER DATABASE 数据库名字 SET RECOVERY SIMPLE
GO

1.1.2修改SIMPLE类型

/*恢复成FULL类型*/
ALTER DATABASE 数据库名字 SET RECOVERY FULL
GO

/*恢复成FULL类型*/
ALTER DATABASE 数据库名字 SET RECOVERY FULL
GO

1.2查看实例文件位置

SELECT name, physical_name AS current_file_location FROM sys.master_files WHERE DB_NAME(database_id)  = 'data_name'

SELECT name, physical_name AS current_file_location FROM sys.master_files WHERE DB_NAME(database_id)  = 'data_name'

1.2.1查看文件大小

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'data_name'

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'data_name'

1.2.1查看所有数据库大小

select * from sys.master_files

select * from sys.master_files

1.3数据文件收缩和查看收缩进度

1.3.1查看日志文件大小

-- 查看所有数据库日志文件大小
SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC

-- 查看所有数据库日志文件大小
SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC

• 或者

use hantest
select name AS [File Name],
	physical_name AS [Physical Name],
	size/128.0 AS [Total Size in MB],
	size/128.0 - CAST(FILEPROPERTY(name, 'SaceUsed') AS int)/128.0 AS [Available Space In MB],
	[growth], [file_id] from sys.database_files where type_desc = 'LOG'

use hantest
select name AS [File Name],
	physical_name AS [Physical Name],
	size/128.0 AS [Total Size in MB],
	size/128.0 - CAST(FILEPROPERTY(name, 'SaceUsed') AS int)/128.0 AS [Available Space In MB],
	[growth], [file_id] from sys.database_files where type_desc = 'LOG'

• 或者

select * from sys.dm_db_log_info ( db_id('DBName') ) 
DBCC SQLPERF(LOGSPACE)

select * from sys.dm_db_log_info ( db_id('DBName') ) 
DBCC SQLPERF(LOGSPACE)

1.3.2查看单独数据库文件大小

-- 查看数据库大小
use data_name
SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(兆)],
    FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(兆)],
    size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(兆)],
    FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]
FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on  from sys.databases where name=DB_NAME())b
WHERE type=0
--

-- 查看数据库大小
use data_name
SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(兆)],
    FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(兆)],
    size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(兆)],
    FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]
FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on  from sys.databases where name=DB_NAME())b
WHERE type=0
--

1.3.3查看日志文件状态

SELECT [name] ,[log_reuse_wait_desc] FROM master.sys.databases WHERE [name]='data_name'

SELECT [name] ,[log_reuse_wait_desc] FROM master.sys.databases WHERE [name]='data_name'

==只有log_reuse_wait_desc是NOTHING状态才可回收==

常见的日志等待类型是 LOG_BACKUP，日志还没有备份，所以不能截断

解决方案： ACTIVE_TRANSACTION，有活跃事务阻塞了日志截断

解决方案： 执行 DBCC OPENTRAN ，获取下长时间的活跃事务的SPID 然后执行 DBCC INPUTBUFFER(SPID) 查看下这个请求SQL，考虑是否可以kill阻塞源，kill后再查下log_reuse_wait，尝试shrink

1.3.4收缩日志文件

• ==仅简单模式下操纵==

-- 完整步骤

-- 查看日志状态
SELECT [name] ,[log_reuse_wait_desc] FROM master.sys.databases WHERE [name]='DBName'

BACKUP LOG DBName TO DISK = 'D:\database.bak'
GO

-- 更改类型
USE [master]
ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE DBName SET RECOVERY SIMPLE

use DBName
declare @log_File_Name varchar(200)
select @log_File_Name = name from sysfiles where filename like '%LDF'
declare @i int = FILE_IDEX ( @log_File_Name)
dbcc shrinkfile ( @i , 30)     -- sql server默认文件单位为M，所以这里是30m

-- 重新恢复成FULL类型
USE [master]
ALTER DATABASE [数据库名称] SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [database] SET RECOVERY FULL



--查看收缩完之后文件大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'DBName'





/* 有问题
    declare @usedspace int ,@totalspace int
     
    select @usedspace= xxx,@totalspace =yyy
     
    while @totalspace> @usedspace
     
    begin
     
    set @totalspace= @totalspace-5 *1024
     
    DBCC SHRINKFILE( 逻辑文件名,@totalspace )
     
    end
 通过1.3.2获取
 
*/

-- 完整步骤

-- 查看日志状态
SELECT [name] ,[log_reuse_wait_desc] FROM master.sys.databases WHERE [name]='DBName'

BACKUP LOG DBName TO DISK = 'D:\database.bak'
GO

-- 更改类型
USE [master]
ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE DBName SET RECOVERY SIMPLE

use DBName
declare @log_File_Name varchar(200)
select @log_File_Name = name from sysfiles where filename like '%LDF'
declare @i int = FILE_IDEX ( @log_File_Name)
dbcc shrinkfile ( @i , 30)     -- sql server默认文件单位为M，所以这里是30m

-- 重新恢复成FULL类型
USE [master]
ALTER DATABASE [数据库名称] SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [database] SET RECOVERY FULL



--查看收缩完之后文件大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'DBName'





/* 有问题
    declare @usedspace int ,@totalspace int
     
    select @usedspace= xxx,@totalspace =yyy
     
    while @totalspace> @usedspace
     
    begin
     
    set @totalspace= @totalspace-5 *1024
     
    DBCC SHRINKFILE( 逻辑文件名,@totalspace )
     
    end
 通过1.3.2获取
 
*/

• 收缩前50M

• 收缩后大小10M

1.3.5查询当前数据库备份进度

--查询当前数据库备份进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')  --DB_NAME(er.[database_id]) in ('ky2011') and

--查询当前数据库备份进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')  --DB_NAME(er.[database_id]) in ('ky2011') and

1.3.6查看数据库收缩进度

use DBName
SELECT DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m] 
FROM sys.dm_exec_requests as er WHERE command in ('DbccFilesCompact','AUTOSHRINK')

use DBName
SELECT DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m] 
FROM sys.dm_exec_requests as er WHERE command in ('DbccFilesCompact','AUTOSHRINK')

1.4收缩日志文件的影响

日志文件收缩， 回收磁盘空间。

数据文件 根据设计 设计的大小，一般情况不用收缩，收缩可能带来性能的问题。

日志

如果 选择的日志恢复模型是完全，如果没有日志截断，日志增长的很大，

建议

备份日志。

backup log db to disk='备份设备'

截断日志

backup log db with no_log

然后收缩。

dbcc shrinkfile(2,10)

1.5收缩数据文件的影响

数据库使用数据文件（扩展名是mdf 或 ndf)来存储数据，使用日志文件（扩展名是ldf）来存储事务日志，通常情况下，数据文件会持续增长，不会自动释放空闲空间，这样会导致硬盘空间耗尽。

如果一个数据库的文件有很多空闲空间，收缩数据库文件是一种解决硬盘空间紧张的直接方式。

原理与影响：

在SQL Server中，我们可以使用 DBCC ShrinkFile命令收缩数据文件，该命令首先将文件尾部的区（extent）移动到文件的开头，文件结尾的空闲的硬盘空间被释放给操作系统。

这种操作就像截断将文件的尾部一样，这种方式不需要消耗很多IO就能释放空间；

但是，如果空闲部分不在文件末尾时，收缩操作必须扫描数据文件，并对正在读取的页面加锁，把文件末尾的区移动到文件开头，这是一个IO密集型的操作，影响数据库的性能；

1、收缩操作不是一个独占行为，在做文件收缩时，其他用户仍然可以对数据库进行读写操作。

2、收缩会锁表，会阻塞

3、在任意一个时间点停止dbcc shrinkfile命令，任何已经完成的工作都将保留。

建议：

收缩 数据库 是非常耗费server性能的操作，如果没有必要不要收缩

收缩 无非就是把已经分配给数据库文件空间收回来，但是，收缩的时候 要移动数据页，而且可能造成大量的碎片，影响性能。

日志收缩和数据文件收缩不一样，日志中的虚拟文件状态 只有 在可复用 时候 才能收缩。

而且 凡是有活动的日志的日志虚拟文件都是活动的不能收缩

1.6always on 日志清理

SQL Server的日志只要备份后就会自动释放原有占用的空间，例如每周产生的日志为10GB，每周做一次备份，在磁盘上的日志永远都会保持在10GB左右。

因此有效的收缩办法为，针对SQL Server做事务日志的定期备份，此操作直接在SQL Server管理工具中制定维护备份计划即可。

在一般部署中，我们制定两个备份计划，一个完整备份、一个事务日志备份，并将备份按照安全要求保留一个月或更长时间，自动删除旧的备份

1.6.0 alwaysOn状态下

为了做数据库读写分离，做了故障转移群集，以及通过Alwayson做数据库同步，但也造成了数据库日志文件不断增加，通过以前的方法处理也会出错

通过截断日志可以解决此问题

USE [StarDB]
DECLARE @bakfile nvarchar(100)
SET @bakfile='E:\DbLogs\log_bak_'+CONVERT(nvarchar(8),GETDATE(),112)+'.log'
BACKUP LOG [StarDB]  TO DISK= @bakfile WITH RETAINDAYS= 1,COMPRESSION
DBCC SHRINKFILE (N'StarDB_log', 0)
GO

--第一次执行之后，日志大小还是不变，多执行几次就可以了

USE [StarDB]
DECLARE @bakfile nvarchar(100)
SET @bakfile='E:\DbLogs\log_bak_'+CONVERT(nvarchar(8),GETDATE(),112)+'.log'
BACKUP LOG [StarDB]  TO DISK= @bakfile WITH RETAINDAYS= 1,COMPRESSION
DBCC SHRINKFILE (N'StarDB_log', 0)
GO

--第一次执行之后，日志大小还是不变，多执行几次就可以了

-- 若执行一次没有达到预期的收缩效果，可以多执行几次 
USE [master]; 
GO
BACKUP LOG [TestDB] TO DISK='NUL:' -- 备份事务日志，备份成NUL，就不用占硬盘 空间，完全模式下，有过完全备份下执行
GO
USE [TestDB]; 
GO
DBCC SHRINKFILE (N'TestDB_log' , 12, TRUNCATEONLY) --收缩数据库日志文件，收 到10M; 
GO

-- 若执行一次没有达到预期的收缩效果，可以多执行几次 
USE [master]; 
GO
BACKUP LOG [TestDB] TO DISK='NUL:' -- 备份事务日志，备份成NUL，就不用占硬盘 空间，完全模式下，有过完全备份下执行
GO
USE [TestDB]; 
GO
DBCC SHRINKFILE (N'TestDB_log' , 12, TRUNCATEONLY) --收缩数据库日志文件，收 到10M; 
GO

1.6.1查看日志状态

use hantest
dbcc loginfo

use hantest
dbcc loginfo

==只有状态为0才开收缩==

• 或者查看

SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc
FROM sys.databases where name='DBName'

SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc
FROM sys.databases where name='DBName'

==检查数据库日志状态，处于NOTHING状态 的的才能截断日志==

1.7事务日志无法收缩

由于事务未提交而导致日志无法收缩

1.查看进程

使用 exec sp_who2 查看进程

exec sp_who2

exec sp_who2

2.查看日志状态

use hantest
dbcc loginfo

use hantest
dbcc loginfo

3.查看是否有未提交的事务

SELECT 
   'kill ' + CAST(a.session_id AS NVARCHAR(100))
  ,a.session_id,connect_time,host_name,c.[text]program_name,host_process_id,client_version 
  ,client_interface_name,a.login_name,a.status,cpu_time,total_elapsed_time,total_scheduled_time 
  ,last_request_start_time,reads,writes,logical_reads,original_login_name,client_net_address,client_tcp_port,most_recent_sql_handle 
FROM sys.dm_exec_sessions a inner join SYS.DM_EXEC_CONNECTIONS b ON a.session_id = b.session_id CROSS APPLY SYS.DM_EXEC_SQL_TEXT(most_recent_sql_handle) c 
WHERE a.session_id <> @@SPID 
ORDER BY connect_time

SELECT 
   'kill ' + CAST(a.session_id AS NVARCHAR(100))
  ,a.session_id,connect_time,host_name,c.[text]program_name,host_process_id,client_version 
  ,client_interface_name,a.login_name,a.status,cpu_time,total_elapsed_time,total_scheduled_time 
  ,last_request_start_time,reads,writes,logical_reads,original_login_name,client_net_address,client_tcp_port,most_recent_sql_handle 
FROM sys.dm_exec_sessions a inner join SYS.DM_EXEC_CONNECTIONS b ON a.session_id = b.session_id CROSS APPLY SYS.DM_EXEC_SQL_TEXT(most_recent_sql_handle) c 
WHERE a.session_id <> @@SPID 
ORDER BY connect_time

4.kill

杀掉一直未提交的最早的几个事务

KILL xxx

KILL xxx

2.事务日志

https://docs.microsoft.com/zh-cn/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017

https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

3.日志原理

https://www.cnblogs.com/gallen-n/p/6555283.html