Sqlserver推荐参数配置及日志收缩问题

      最近不定期有项目反馈周期性的系统整体性能下降情况，经分析存在因数据库环境、参数配置不佳造成的。比如，sqlserver日志文件缺省按百分比增长，当日志文件已经比较大时，每次扩展时耗时较长，系统整体卡顿；另外，如果没有专门做日志备份，收缩日志和数据库时不会显著的降低日志大小，造成每次完整备份很大、备份时间很长，等等。

推荐配置

简单整理一些比较基础、通用的配置如下：

1. 建议的sqlserver版本(x64)：sqlserver 2008 或更高版本

2. 最小内存和最大内存统一设置为物理内存的80%

3. 数据和日志文件的初始大小分别设置为10G和2G，均设置为按照固定200M大小增长，不限制最大值；

4. Tempdb数据库的恢复模式设置为简单，数据和日志文件的初始大小分别设置为2G和1G，均设置为按照固定200M大小增长，不限制最大值；

5. Tempdb的数据文件个数 = 数据库服务器的CPU数，所有数据文件的初始大小和增量必须一致，数据文件个数不要超过4个；

6. 最大并行度设置为1，或并行的开销阀值设置为100（酌情设置）

7. 数据库的完整备份后，应该再做一个日志备份，然后再做日志收缩。

 

日志收缩

正常情况下，完成完整备份后，应该执行日志备份，然后再做日志文件的收缩。只有做日志备份后记录才会被截断，仅做完整备份或差异备份，做日志收缩是没有效果的。

操作步骤如下：

USE [master]
GO

BACKUP DATABASE [DbName] TO DISK='xxx'
GO

BACKUP LOG [DbName] TO DISK='xxx'
GO

USE [DbName]
GO

-- 确定数据库日志文件的逻辑名称，收缩日志文件
DECLARE @logName NVARCHAR(100);
SELECT @logName = name FROM sys.database_files WHERE type_desc = 'LOG';  --Type = 1
DBCC SHRINKFILE (@logName, 1024);
GO

 

如果不备份日志，直接截断日志（不推荐使用），有以下两种变通方式：

1. 将日志写入nul虚拟文件（对 SQL Server而言，nul 与其他真实存在的文件一样， SQL SERVER会扫描所有活动日志，将该日志格式化后写入 nul文件）

2. 将数据库改为简单恢复模式后又改为完整恢复模式

    SQL2005 的WITH TRUNCATE_ONLY选项，起到相同的效果。运行在简单恢复模式下，所有活动日志在 checkpoint后会被丢弃；

-- 备份数据库日志到nul虚拟文件
BACKUP LOG [DbName] TO DISK='nul'

-- 备份数据库日志，截断日志（sqlserver2005支持）
BACKUP LOG [DbName] WITH TRUNCATE_ONLY


// 2008以后

-- 将数据库恢复模式改为简单（即截断日志），然后再恢复为完整模式
USE [master]
GO

ALTER DATABASE [DbName] SET RECOVERY SIMPLE WITH NO_WAIT
GO

ALTER DATABASE [DbName] SET RECOVERY SIMPLE --简单模式
GO

USE [DbName]
GO

-- 确定数据库日志文件的逻辑名称
DBCC SHRINKFILE (N'DbName_log' , 1024)
GO

USE [master]
GO

ALTER DATABASE [DbName] SET RECOVERY FULL WITH NO_WAIT
GO

ALTER DATABASE [DbName] SET RECOVERY FULL --还原为完全模式
GO