1.日志清理
1.日志状态
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'
1.0清理日志文件的方法
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
1.1查看数据库恢复类型
select NAME, recovery_model_desc from sys.databases
1.1.1修改FULL类型
/*如果是FULL类型，修改为SIMPLE类型*/
ALTER DATABASE 数据库名字 SET RECOVERY SIMPLE
GO
1.1.2修改SIMPLE类型
/*恢复成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'
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'
1.2.1查看所有数据库大小
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
- 或者
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)
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
--
1.3.3查看日志文件状态
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获取
*/
- 收缩前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
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')
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 [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
只有状态为0才开收缩
- 或者查看
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
2.查看日志状态
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
4.kill
杀掉一直未提交的最早的几个事务
KILL spid
