Use jobs to automatically clean up database log files

Use jobs to automatically clean up database log files

       In the previous article, I introduced how to delete the database log file, but it is not inconvenient to manually operate, so I want to combine the job to automatically clean the log file. When cleaning the log file, I added a condition, when the disk control space It will be cleared if it is less than M. Here is how to implement this function. If you haven't read the previous article, you can read it through the portal ( the method of deleting the database log file )!

Read the table of contents

back to the top

SQL query disk space size

   Use the built-in stored procedure, you can view the available space of each disk

exec master..xp_fixeddrives

back to the top

Stored procedure add job

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
/*
Function: Create SQL job
parameter:
    @jobname: job name
    @sql: command to be executed
    @freqtype: time period, month month, week week, day day
    @fsinterval: relative to the number of repetitions per day
    @time: start execution time, for repeated jobs, from 0 o'clock to 23:59
    @description: description of the job
*/
BEGIN
    DECLARE @dbname AS VARCHAR(500)
    SET @dbname=DB_NAME()
        
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    
    --Add category
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Add job' AND category_class=1)
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Add job'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END
    
    --Delete job  
    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   
      - Check if this job is a multi-server job  
      IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0)))  
      BEGIN  
        --Multiple server operations do not operate  
        SET @ErrMsg ='Cannot import job"' + @JobName +'", because there are already multiple server jobs with the same name. '  
        RAISERROR (@ErrMsg, 16, 1)   
        GOTO QuitWithRollback  
      END  
      ELSE  
       BEGIN  
        - Delete [local] job   
        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'Add Assignment', 
            @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   /****** Object: Step [Data Synchronization] 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
    
    --Create schedule
    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

       Combined with the usp_p_delDBLog of the previous article, make a transformation

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
)
/*
* Function: Shrink the current database log file
* Parameter @DriveLimit: shrink the database MB when the disk space where the current database is located reaches
* @DBLogSise: How much to shrink the log file M Default shrink to the smallest
*/
AS 
BEGIN
    IF @DBLogSise<0 OR @DriveLimit<0
    BEGIN
        RETURN
    END
    
    --The disk where the current database is located
    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),--disk
        Available BIGINT --Available size in MB
    )
    INSERT INTO #TempFile(Drive,Available)
    exec master..xp_fixeddrives

    --Query the remaining space of the disk where the current database is located
    SELECT @Available=Available FROM #TempFile
    WHERE Drive=@Drive

    -Shrink the log file if the conditions are met
    IF @Available<=@DriveLimit
    BEGIN
    
        -Query the name of the log file corresponding to the database
        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='
        --Set the database recovery model to simple
        ALTER DATABASE ['+@strDBName+'] SET RECOVERY SIMPLE;
        -Shrink the log file
        DBCC SHRINKFILE ('''+@strLogName+''','+CONVERT(VARCHAR(20),@DBLogSise)+');
        --Restore the database restore mode to complete
        ALTER DATABASE ['+@strDBName+'] SET RECOVERY FULL '

        exec(@strSQL)    
    END
    
    DROP TABLE #TempFile
END
GO

The function of querying the remaining size of the disk space where the current database is located is mainly added here

    --The disk where the current database is located
    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),--disk
        Available BIGINT --Available size in MB
    )
    INSERT INTO #TempFile(Drive,Available)
    exec master..xp_fixeddrives

    --Query the remaining space of the disk where the current database is located
    SELECT @Available=Available FROM #TempFile
    WHERE Drive=@Drive

Okay, after the above preparations are done, you can add auto-run jobs through the following SQL

--Add assignment
--The job is executed every two hours every day
--The execution condition is that the disk space is less than 5000MB, that is, @DriveLimit=5000 can be configured by yourself
DECLARE @@jobname AS VARCHAR(1000)
SELECT @@jobname=DB_NAME()+'_Automatically clean up the current database log file'
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 ='Automatically clean up the current database log file' - varchar(1000)

back to the top

Sample download

Sample sql

   Related reading: Attach a database method without log files

How to delete database log files

Reference: https://cloud.tencent.com/developer/article/1014595 Use jobs to automatically clean up database log files-Cloud + Community-Tencent Cloud