在SQL Server中,日志文件会一直记录。
所以我们需要使用Log truncation来释放日志文件中的空间,供日志文件空间重复使用。
为避免空间不足,除非出于某些原因延迟日志截断,否则在发生以下事件后会自动发生截断:
在简单恢复模式下,经过一个检查点。
在完全恢复模型或大容量日志记录的恢复模型下,如果自上次备份以来已出现检查点,则在日志备份后会发生截断(除非它是仅复制日志备份)。
在Log truncation之外,我们还可以使用shrink功能针对log file进行截断。
--查询日志文件号
select file_id from sys.database_files where type_desc = 'LOG';
--对日志文件进行shrink。
USE testdb;
GO
DBCC SHRINKFILE (X); -- X为上一步查出的file_id。
GO
当然我们可以做一个job,定期执行。
USE testdb;
IF EXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Shrinktestdb_JOB')
EXEC msdb.dbo.sp_delete_job @job_name=N'Shrinktestdb_JOB'
GO
DECLARE @jobname SYSNAME, @file_id SYSNAME, @servername SYSNAME, @dbname SYSNAME
DECLARE @SQL VARCHAR(100)
SELECT @jobname='Shrinktestdb_JOB', @dbname=db_name(), @file_id=file_id from sys.database_files where type_desc = 'LOG';
SELECT @servername=name from sys.servers;
SET @SQL ='DBCC SHRINKFILE (' + @file_id +');'
--1.创建作业
exec msdb.dbo.sp_add_job @job_name=@jobname
--2.创建作业步骤
exec msdb.dbo.sp_add_jobstep
@job_name=@jobname,
@step_name = '收缩数据库',
@subsystem = 'TSQL',
@database_name = @dbname,
@command = @SQL,
--@command = 'exec DateTableProc',
@retry_attempts = 1, --重试次数
@retry_interval = 1 --重试间隔
--3 创建调度,创建作业计划,每小时执行一次
EXEC msdb.dbo.sp_add_jobschedule @job_name = @jobname,
@name = 'Shrinktestdb_JOB',
@freq_type = 4, --freq_type指定作业执行时间的值,4代表间隔为每天,8代表每周,16代表每月
@freq_interval = 2, --freq_interval执行作业的日期,依赖于freq_type 的值,1代表每天执行一次
@freq_subday_type=0x8, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次
@freq_subday_interval=1, --重复周期数,这里每小时执行一次
@active_start_date = NULL, --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD
@active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD
@active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS
@active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS
--4 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_name = @jobname ,
@server_name = @servername
--5 开始作业
exec msdb.dbo.sp_start_job
@job_name = @jobname--,@server_name='(local)'
将整段代码一起执行,就会生成一个job,每小时执行一次,针对log文件进行shrink。