使用作业自动清理数据库日志文件

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

阅读目录

回到顶部

SQL查询磁盘空间大小

   采用内置的存储过程,即可查看各个磁盘可用空间

exec master..xp_fixeddrives

回到顶部

存储过程添加作业

GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('usp_p_CreateJob'))
BEGIN
    DROP PROC dbo.usp_p_CreateJob
END
GO
CREATE PROCEDURE dbo.usp_p_CreateJob(
    @jobname varchar(100),         
    @sql VARCHAR(MAX),                      
    @freqtype varchar(6)='day',     
    @fsinterval int=1,                
    @time int=235959,                     
    @description VARCHAR(1000)=''           
)
AS
/*
功能:创建SQL作业
参数:
    @jobname:作业名称
    @sql:要执行的命令
    @freqtype:时间周期,month 月,week 周,day 日
    @fsinterval:相对于每日的重复次数
    @time:开始执行时间,对于重复执行的作业,将从0点到23:59分
    @description:作业的描述
*/ 
BEGIN
    DECLARE @dbname AS VARCHAR(500)
    SET @dbname=DB_NAME()
        
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    
    --添加类别
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='添加作业' AND category_class=1)
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'添加作业'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END
    
    --删除作业  
    DECLARE @JobID BINARY(16)   
    DECLARE @ErrMsg NVARCHAR(500)      
    SELECT  @JobID = job_id  FROM msdb.dbo.sysjobs WHERE name = @JobName  
    IF ( @JobID IS NOT NULL )  
    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  
      END  
      ELSE  
       BEGIN  
        -- 删除[本地]作业   
        EXECUTE msdb.dbo.sp_delete_job @job_name = @JobName  
        SELECT @JobID = NULL  
       END  
     END  

    SET @JobID = NULL    
    EXEC @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 <> 0 OR @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=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    --创建调度
    declare @ftype int,@fstype int,@ffactor int
    select @ftype=case @freqtype when 'day' then 4
                                            when 'week' then 8
                                            when 'month' then 16 end
            ,@fstype=case @fsinterval when 1 then 0 else 8 end
    if @fsinterval<>1 set @time=0
    set @ffactor=case @freqtype when 'day' then 0 else 1 end
    
    EXEC 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=@time                         
        
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
END
GO

       结合上一篇文章的usp_p_delDBLog,进行改造

GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('usp_p_delDBLog'))
BEGIN
    DROP PROC dbo.usp_p_delDBLog
END
GO
CREATE PROC usp_p_delDBLog(
    @DriveLimit AS BIGINT,
    @DBLogSise AS INT =0
)
/*
*    功能:收缩当前数据库日志文件
*    参数  @DriveLimit:当前数据库所在磁盘空间到达多少的时候进行收缩数据库  MB
*          @DBLogSise:日志文件收缩至多少M 默认收缩到最小
*/
AS 
BEGIN
    IF @DBLogSise<0 OR @DriveLimit<0
    BEGIN
        RETURN
    END
    
    --当前数据库所在磁盘
    DECLARE @Drive AS VARCHAR(10)
    DECLARE @Available AS BIGINT

    SELECT TOP 1  @Drive=SUBSTRING(filename,1,1)  from   sysfiles


    CREATE TABLE #TempFile(
        Drive VARCHAR(10),--磁盘
        Available BIGINT --可用大小MB
    )
    INSERT INTO #TempFile(Drive,Available)
    exec master..xp_fixeddrives

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

    --符合条件则进行收缩日志文件
    IF @Available<=@DriveLimit
    BEGIN
    
        --查询出数据库对应的日志文件名称
        DECLARE @strDBName AS NVARCHAR(500)
        DECLARE @strLogName AS NVARCHAR(500)
        DECLARE @strSQL AS VARCHAR(1000)
        
        SELECT 
            @strLogName=B.name,
            @strDBName=A.name
        FROM master.sys.databases AS A
        INNER JOIN 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)    
    END
    
    DROP TABLE #TempFile
END
GO

这里主要添加了查询当前数据库所在磁盘空间剩余大小的功能

    --当前数据库所在磁盘
    DECLARE @Drive AS VARCHAR(10)
    DECLARE @Available AS BIGINT

    SELECT TOP 1  @Drive=SUBSTRING(filename,1,1)  from   sysfiles


    CREATE TABLE #TempFile(
        Drive VARCHAR(10),--磁盘
        Available BIGINT --可用大小MB
    )
    INSERT INTO #TempFile(Drive,Available)
    exec master..xp_fixeddrives

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

好了上面的准备工作做完以后可以通过以下SQL进行添加自动运行的作业

--添加作业
--作业每天间隔两小时执行一次
--执行条件为磁盘空间不足 5000MB,即@DriveLimit=5000 可自行配置
DECLARE @@jobname AS VARCHAR(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)

回到顶部

示例下载

示例sql

   相关阅读:附加没有日志文件的数据库方法

删除数据库日志文件的方法

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 索引监控(monitor index)

      合理的为数据库表上创建战略性索引,可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引,恰恰是大量冗余或是根本没有用到的索引耗...

651
来自专栏芋道源码1024

数据库[分库分表]中间件 Sharding-JDBC 源码分析 —— SQL 解析(四)之插入SQL

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. InsertStatement 3. #parse() 3.1 #parseI...

3617
来自专栏杨建荣的学习笔记

10g升级至11g exp的问题解决(23天)

昨天升级数据库,从10.2.0.5.0升级到11.2.0.2.0.按照预定的步骤很快就操作完了。升级完成后,开始跑一些应用和Job.有一个Job开始报错,Job...

4446
来自专栏idba

死锁案例之三

一 前言 死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能...

572
来自专栏杨建荣的学习笔记

ORA-01113问题的简单分析(r6笔记第3天)

在启动数据库的时候,open阶段总是可能出现各种各样的问题, 比如让人胆战心惊的错误。 ORA-01113: file 1 needs media recov...

2485
来自专栏乐沙弥的世界

导入导出 Oracle 分区表数据

导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式,只不过导入导出需要考

925
来自专栏pangguoming

SpringBoot学习:整合shiro(身份认证和权限认证),使用EhCache缓存

项目下载地址:http://download.csdn.NET/detail/aqsunkai/9805821 (一)在pom.xml中添加依赖: <prope...

4255
来自专栏数据库

oracle常用命令大全

一、Oracle数据库实例、用户、目录及session会话查看: 1、ORACLE SID查看设置 查看SID、用户名 $ env|grep SID 、sele...

3277
来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

35813
来自专栏乐沙弥的世界

Oracle 重建索引脚本

      索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的D...

731

扫码关注云+社区