在上一篇文章中介绍了如何删除数据库日志文件，但是想想还是不是不方便需要手工操作，于是想结合作业实现自动清理日志文件，在清理日志文件时我加上了条件，当磁盘控空间不足多少M才会清理，下面介绍如何实现该功能。没有阅读上一篇文章的，可以通过传送门阅读(删除数据库日志文件的方法)！

阅读目录

• SQL查询磁盘空间大小
• 存储过程添加作业
• 示例下载

exec master..xp_fixeddrives

GOIFEXISTS(SELECT1FROM sysobjects WHERE id=OBJECT_ID(‘usp_p_CreateJob‘))
BEGINDROPPROC dbo.usp_p_CreateJob
ENDGOCREATEPROCEDURE dbo.usp_p_CreateJob(
    @jobnamevarchar(100),         
    @sqlVARCHAR(MAX),                      
    @freqtypevarchar(6)=‘day‘,     
    @fsintervalint=1,                
    @timeint=235959,                     
    @descriptionVARCHAR(1000)=‘‘           
)
AS/*
功能：创建SQL作业
参数：
    @jobname:作业名称
    @sql:要执行的命令
    @freqtype:时间周期,month 月,week 周,day 日
    @fsinterval:相对于每日的重复次数
    @time:开始执行时间,对于重复执行的作业,将从0点到23:59分
    @description:作业的描述
*/BEGINDECLARE@dbnameASVARCHAR(500)
    SET@dbname=DB_NAME()
        
    BEGINTRANSACTIONDECLARE@ReturnCodeINTSELECT@ReturnCode=0--添加类别IFNOTEXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=‘添加作业‘AND category_class=1)
    BEGINEXEC@ReturnCode= msdb.dbo.sp_add_category @class=N‘JOB‘, @type=N‘LOCAL‘, @name=N‘添加作业‘IF (@@ERROR<>0OR@ReturnCode<>0) GOTO QuitWithRollback
    END--删除作业  DECLARE@JobIDBINARY(16)   
    DECLARE@ErrMsgNVARCHAR(500)      
    SELECT@JobID= job_id  FROM msdb.dbo.sysjobs WHERE name =@JobNameIF ( @JobIDISNOTNULL )  
    BEGIN-- 检查此作业是否为多重服务器作业  IF ( EXISTS ( SELECT*FROM msdb.dbo.sysjobservers WHERE ( job_id =@JobID ) AND ( server_id <>0 ) ) )  
      BEGIN--多重服务器作业不操作  SET@ErrMsg=‘无法导入作业"‘+@JobName+‘"，因为已经有相同名称的多重服务器作业。‘RAISERROR (@ErrMsg, 16, 1)   
        GOTO QuitWithRollback  
      ENDELSEBEGIN-- 删除［本地］作业   EXECUTE msdb.dbo.sp_delete_job @job_name=@JobNameSELECT@JobID=NULLENDENDSET@JobID=NULLEXEC@ReturnCode=  msdb.dbo.sp_add_job @job_name=@jobname, 
            @enabled=1, 
            @notify_level_eventlog=2, 
            @notify_level_email=0, 
            @notify_level_netsend=0, 
            @notify_level_page=0, 
            @delete_level=0, 
            @description=@description, 
            @category_name=N‘添加作业‘, 
            @owner_login_name=N‘sa‘, @job_id=@jobId OUTPUT
    IF (@@ERROR<>0OR@ReturnCode<>0) GOTO QuitWithRollback
    /****** Object:  Step [数据同步]    Script Date: 01/25/2014 23:00:36 ******/EXEC@ReturnCode= msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobname, 
            @step_id=1, 
            @cmdexec_success_code=0, 
            @on_success_action=1, 
            @on_success_step_id=0, 
            @on_fail_action=2, 
            @on_fail_step_id=0, 
            @retry_attempts=5, 
            @retry_interval=5, 
            @os_run_priority=0, @subsystem=N‘TSQL‘, 
            @command=@sql, 
            @database_name=@dbname, 
            @flags=0IF (@@ERROR<>0OR@ReturnCode<>0) GOTO QuitWithRollback
    EXEC@ReturnCode= msdb.dbo.sp_update_job @job_id=@jobId, @start_step_id=1IF (@@ERROR<>0OR@ReturnCode<>0) GOTO QuitWithRollback
    
    --创建调度declare@ftypeint,@fstypeint,@ffactorintselect@ftype=case@freqtypewhen‘day‘then4when‘week‘then8when‘month‘then16end
            ,@fstype=case@fsintervalwhen1then0else8endif@fsinterval<>1set@time=0set@ffactor=case@freqtypewhen‘day‘then0else1endEXEC msdb..sp_add_jobschedule @job_name=@jobname, 
        @name=@jobname,
        @freq_type=@ftype ,                                        
        @freq_interval=1,                                       
        @freq_subday_type=@fstype,                       
        @freq_subday_interval=@fsinterval,        
        @freq_recurrence_factor=@ffactor,
        @active_start_time=@timeIF (@@ERROR<>0OR@ReturnCode<>0) GOTO QuitWithRollback
    EXEC@ReturnCode= msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name= N‘(local)‘IF (@@ERROR<>0OR@ReturnCode<>0) GOTO QuitWithRollback
    COMMITTRANSACTIONGOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT>0) ROLLBACKTRANSACTION
    EndSave:
ENDGO

GOIFEXISTS(SELECT1FROM sysobjects WHERE id=OBJECT_ID(‘usp_p_delDBLog‘))
BEGINDROPPROC dbo.usp_p_delDBLog
ENDGOCREATEPROC usp_p_delDBLog(
    @DriveLimitASBIGINT,
    @DBLogSiseASINT=0
)
/*
*    功能：收缩当前数据库日志文件
*    参数  @DriveLimit:当前数据库所在磁盘空间到达多少的时候进行收缩数据库  MB
*          @DBLogSise：日志文件收缩至多少M 默认收缩到最小
*/ASBEGINIF@DBLogSise<0OR@DriveLimit<0BEGINRETURNEND--当前数据库所在磁盘DECLARE@DriveASVARCHAR(10)
    DECLARE@AvailableASBIGINTSELECTTOP1@Drive=SUBSTRING(filename,1,1)  from   sysfiles


    CREATETABLE #TempFile(
        Drive VARCHAR(10),--磁盘
        Available BIGINT--可用大小MB    )
    INSERTINTO #TempFile(Drive,Available)
    exec master..xp_fixeddrives

    --查询当前数据库所在磁盘剩余空间大小SELECT@Available=Available FROM #TempFile
    WHERE Drive=@Drive--符合条件则进行收缩日志文件IF@Available<=@DriveLimitBEGIN--查询出数据库对应的日志文件名称DECLARE@strDBNameASNVARCHAR(500)
        DECLARE@strLogNameASNVARCHAR(500)
        DECLARE@strSQLASVARCHAR(1000)
        
        SELECT@strLogName=B.name,
            @strDBName=A.name
        FROM master.sys.databases AS A
        INNERJOIN sys.master_files AS B
        ON A.database_id = B.database_id
        WHERE A.database_id=DB_ID() 
            
        SET@strSQL=‘
        --设置数据库恢复模式为简单
        ALTER DATABASE [‘+@strDBName+‘] SET RECOVERY SIMPLE;
        --收缩日志文件
        DBCC SHRINKFILE (‘‘‘+@strLogName+‘‘‘ , ‘+CONVERT(VARCHAR(20),@DBLogSise)+‘);
        --恢复数据库还原模式为完整
        ALTER DATABASE [‘+@strDBName+‘] SET RECOVERY FULL ‘exec(@strSQL)    
    ENDDROPTABLE #TempFile
ENDGO

--当前数据库所在磁盘DECLARE@DriveASVARCHAR(10)
    DECLARE@AvailableASBIGINTSELECTTOP1@Drive=SUBSTRING(filename,1,1)  from   sysfiles


    CREATETABLE #TempFile(
        Drive VARCHAR(10),--磁盘
        Available BIGINT--可用大小MB    )
    INSERTINTO #TempFile(Drive,Available)
    exec master..xp_fixeddrives

    --查询当前数据库所在磁盘剩余空间大小SELECT@Available=Available FROM #TempFile
    WHERE Drive=@Drive

--添加作业
--作业每天间隔两小时执行一次
--执行条件为磁盘空间不足 5000MB，即@DriveLimit=5000 可自行配置DECLARE@@jobnameASVARCHAR(1000)
SELECT@@jobname=DB_NAME()+‘_自动清理当前数据库日志文件‘EXEC dbo.usp_p_CreateJob @jobname=@@jobname, -- varchar(100)@sql=‘EXEC usp_p_delDBLog @DriveLimit=5000,@DBLogSise=0‘, -- varchar(max)@freqtype=‘day‘, -- varchar(6)@fsinterval=2, -- int@time=235959, -- int@description=‘自动清理当前数据库日志文件‘-- varchar(1000)