数据库的自动作业清理日志脚本

数据库在运行后会产生非常大的日志，在每天自动备份后就会是硬盘的空间呈指数增长，这是后清理数据库日志是很必要的。一下是建立如何自动清理数据库日志的作业的脚本

/***运行后会在"SQL Server 代理/作业"中添加相应的作业项***/
/***可能通过双击来修改其中的"步骤"和"计划"***/
/* 本文原创于 葫芦之家 www.huluhome.com 转载请注明出处 **/
USE [msdb]
GO
DECLARE @KeduDBName VARCHAR(20), @DatabaseID int, @JobName VARCHAR(100)
SET @KeduDBName = N'Kedu4s_Test'
set @JobName = N'清理数据库日志(Kedu4s_Test)'
-- 运行后还需要修改配置中的数据库(步骤中的数据库)
-- 还需要核对日志文件名(步骤中的代码部分)

/****** 对象:  Job [清理mydb_test数据库日志]     ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@JobName,
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'无描述。',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [清理业务数据库日志] 
本文原创于：  www.huluhome.com 转载请注明   ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'清理数据库日志',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=3,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'SET NOCOUNT ON
Begin Try
    DROP TABLE DummyTrans
End Try
Begin Catch
End Catch

DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT

SELECT @LogicalFileName = ''mydbLog'', -- 日志文件名
       @MaxMinutes = 10, -- Limit on time allowed to wrap log.
       @NewSize = 10 -- 你想设定的日志文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName

SELECT ''Original Size of '' + db_name() + '' LOG is '' +
       CONVERT(VARCHAR(30),@OriginalSize) + '' 8K pages or '' +
       CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ''MB''
FROM sysfiles
WHERE name = @LogicalFileName

CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

DECLARE @Counter INT,
        @StartTime DATETIME,
        @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
       @TruncLog = ''BACKUP LOG '' + db_name() + '' WITH TRUNCATE_ONLY''

EXEC (@TruncLog)
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF(mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
    SELECT @Counter = 0
    WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
    BEGIN -- update
        INSERT DummyTrans VALUES (''Fill Log'')
        DELETE DummyTrans
        SELECT @Counter = @Counter + 1
    END
    EXEC (@TruncLog)
END

SELECT ''Final Size of '' + db_name() + '' LOG is '' +
       CONVERT(VARCHAR(30),size) + '' 8K pages or '' +
       CONVERT(VARCHAR(30),(size*8/1024)) + ''MB''
FROM sysfiles
WHERE name = @LogicalFileName

DROP TABLE DummyTrans

SET NOCOUNT OFF',
  @database_name=@KeduDBName,
  @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
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'每周一次',
  @enabled=1,
  @freq_type=8,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=1,
  @active_start_date=20090806,
  @active_end_date=99991231,
  @active_start_time=150000,
  @active_end_time=235959
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: