数据库事务日志文件-c 常用日志库

-----sqlserver备份

SELECT CONVERT(CHAR(100), Serverproperty('Servername')) AS ServerName

,bs.database_name AS 数据库名称

,bs.recovery_model AS Recovery_Model

,bs.is_damaged AS Is_Damaged

,bs.backup_start_date AS 备份开始日期

,bs.backup_finish_date AS 备份完成日期

,bs.expiration_date AS Expiration_Date

,CASE bs.type

当'D'然后'数据库'

当“L”然后“登录”

结束为 Backup_Type

,bs.backup_size/1024/1024 AS [备份大小(MB)]

,bs.compressed_backup_size/1024/1024 AS [Compressed_Backup_Size]

,bs.compressed_backup_size/bs.backup_size AS Compressed_Rate

，男朋友。 逻辑设备名称

,bf.physical_device_name

,bs.name AS 备份集名称

,bs.描述

来自 msdb.dbo.backupmediafamily-bf

内连接 msdb.dbo.backupset bs

ON bf.media_set_id = bs.media_set_id

按 bs 订购。 数据库名称，

bs.backup_finish_date

一、交易日志的作用

每个 SQL Server 数据库都有一个事务日志，记录所有事务以及每个事务对数据库所做的修改。 事务日志的主要作用如下：

清除内存中的脏页主要有两种方法：

二、事务日志的原理

如上图，SQL Server的日志是由多个虚拟日志文件（Vitual Log Files (VLF)）组成的。 事务日志被截断的基本单位是VLF，而不是数据页。 事务日志中的日志记录都有自己的逻辑序列号（LSN）来标记。

1.事务日志状态分类

2.事务日志截断

事务日志截断以 VLF 为单位执行。 日志截断可以释放日志文件中的空间以供事务日志重用。 如果事务日志长时间不被截断，事务日志会不断增长，直到磁盘空间被占满。 在生产环境中，我们必须监控事务日志并定期截断事务日志。

3、事务日志自动截断的场景：

事务日志的截断并没有减少事务日志物理文件的大小，而是清理了一些可重用的空间供新的事务日志写入重用。 如果要收缩事务日志的物理文件大小，需要执行特殊的日志收缩操作。

3. 交易日志管理 3.1 交易日志监控

1、查看当前数据库事务日志文件大小和日志使用率等。

SELECT a.name [文件名称] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件大小(MB)] ,
    CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [日志使用空间(MB)] ,
    CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [日志空间使用率%] ,
    CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 
    THEN '增量为固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示' ELSE '文件大小固定，不会增长' END AS [增量模式] ,
    CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB' 
    WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%' ELSE '文件大小固定，不会增长' end AS [增长值(%或MB)] ,
    a.physical_name AS [文件所在目录] ,a.type_desc AS [文件类型] 
FROM sys.database_files a 
INNER JOIN sys.sysfiles AS s  ON a.[file_id]=s.fileid 
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id] ORDER BY a.[type]

2）监控事务日志状态

select name,log_reuse_wait,log_reuse_wait_desc from  sys.databases;

log_reuse_waitlog_reuse_wait_desc 说明

没有什么

当前有一个或多个可以重复使用的虚拟日志文件 (VLF)。

1个

检查点

自上次日志截断以来，尚未采用检查点，或者日志标头未在虚拟日志 (VLF) 文件中移动。 （所有恢复模型）这是日志截断延迟的常见原因。

2个

日志备份

在截断事务日志之前，需要进行日志备份。 （仅限完整恢复模式或大容量日志恢复模式）下一次日志备份完成后，一些日志空间可能可供重用。

3个

ACTIVE_BACKUP_OR_RESTORE

正在进行数据备份或恢复（所有恢复模式）。

4个

ACTIVE_TRANSACTION 交易

事务处于活动状态（所有恢复模型）：长时间运行的事务将阻止所有恢复模型（包括简单）中的日志截断。 在这种模式下，事务日志通常会在每个自动检查点被截断。 这是日志截断延迟的常见原因。

5个

数据库镜像

数据库镜像暂停，或在高性能模式下数据库事务日志文件，镜像数据库明显落后于主体数据库。 （仅限完全恢复模型）

6个

复制

在事务复制期间，与发布相关的事务尚未传递到分发数据库。 （仅限完全恢复模型）

7

DATABASE_SNAPSHOT_CREATION

创建数据库快照。 （所有恢复模型）这是日志截断延迟的常见且通常是主要原因。

8个

日志扫描

发生日志扫描。 （所有恢复模型）这是日志截断延迟的常见且通常是主要原因。

9

可用性_REPLICA

可用性组的辅助副本正在将此数据库的事务日志记录应用到相应的辅助数据库。 （完全恢复模式）

10

-

仅限内部使用

11

-

仅限内部使用

12

-

仅限内部使用

13

OLDEST_PAGE

如果数据库配置为使用间接检查点，则数据库中最旧的页面可能早于检查点日志序列号 (LSN)。 在这种情况下，最旧的页面可以延迟日志截断。 （所有恢复模式）

14

OTHER_TRANSIENT

当前未使用此值。

16

XTP_检查点

需要内存中 OLTP 检查点。对于内存优化表，如果自上次检查点（基于磁盘和内存优化的表）以来事务日志文件变得大于 1.5 GB，则执行自动检查点

3.2 事务日志收缩

1.事务日志满是解决方案

在生产环境中，我们一般推荐使用“FULL”恢复模式，但是在FULL恢复模式下，我们需要定期备份数据库和事务日志，避免事务日志文件不断增长，因为事务日志不能被截断。 当事务日志文件已满时，我们可以有以下解决方案：

将新的日志文件添加到其他磁盘 如果事务日志文件没有设置自动增长，您可以手动增加事务日志文件的大小或手动设置自增长属性。 长时间运行的事务将导致事务日志填满。 您可以尝试完成或终止长时间运行的事务。 事物

2. 如何缩小事务日志文件？

FULL/大容量日志恢复模式下，只有事务日志备份后才能截断事务日志文件，截断后可以手动将事务日志文件缩小到一定程度； 事务日志将在每个检查点或日志备份后自动截断。 一般来说，事务日志文件不会太大。 同样对于日志收缩，事务日志文件不能小于一个 VFL 大小。

1）直接收缩事务日志文件

USE AdventureWorks2012;  
GO  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO

2）将数据库恢复模式设置为SIMPLE以截断文件

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  

GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO

-------------------------------------- 方法二

数据库的性能是DBA需要关注的。 日志文件的增加严重影响了数据库的性能。 本文将介绍SQL Server中删除日志文件的方法，供大家参考。 希望对您有所帮助。

在数据库的使用过程中，日志文件会不断增加，这会降低数据库的性能，占用大量的磁盘空间。 SQL Server数据库有一个日志文件数据库事务日志文件，日志文件记录了用户对数据库修改的操作。 数据库日志可以通过直接删除日志文件，清除日志来清除。

1.删​​除LOG

经过不懈的努力，终于找到了真正的终极方法：

1.Detach数据库（一定要在Detach前阻止所有对该数据库的写操作，这是血的教训）

1.1 分离数据库

在分离数据库之前，一定要对数据库进行完整备份，选择数据库-右键-任务-分离。

勾选删除连接

分离后，分离的数据库将不会出现在数据库列表中。

1.2 删除LOG文件

1.3 附加数据库

附加时会提示找不到日志文件。

删除数据库信息的ldf文件：

附加数据库后，会生成一个新的日志文件log，新日志文件的大小为504K。