sysmail_event_log
  SELECTlog_id ,
        CASE event_type
          WHEN 0 THEN 'success'
          WHEN 1 THEN 'information'
          WHEN 2 THEN 'warning'
          ELSE 'error'
        END AS event_type ,
        log_date ,
        description ,
        process_id ,
        sl.mailitem_id ,
        account_id ,
        sl.last_mod_date ,
        sl.last_mod_user
    FROM[dbo].[sysmail_log] sl
WHERE   ( ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 )
        OR( EXISTS( SELECT    mailitem_id
                      FROM      [dbo].[sysmail_allitems] ai
                      WHERE     sl.mailitem_id = ai.mailitem_id ) )

0 = 失败

1 = 成功

2 = 重试

3 = 已取消

4= 正在进行

 USE msdb ;
 
GO
 
EXEC dbo.sp_help_jobhistory
 
@job_name = N'ServerDiskCapacityCheck' ;
 
GO

 
  SELECT      j.name AS  [JOB_NAME] ,
 
            h.step_id AS  [Step] ,  
 
            h.step_name AS  [STEP_NAM] ,  
 
            h.MESSAGE AS  [Message] ,  
 
            [Status]    = CASE WHEN h.run_status = 0 THEN 'Failed'  
 
            WHEN h.run_status = 1 THEN 'Succeeded'  
 
            WHEN h.run_status = 2 THEN 'Retry'  
 
            WHEN h.run_status = 3 THEN 'Canceled'  
 
            END,  
 
            h.run_date AS  [RunDate] ,  
 
            h.run_time AS  [RunTime] ,  
 
            h.run_duration  AS  [RunDuration]
 
  FROM        sysjobs j
 
    INNER JOIN  sysjobhistory h ON h.job_id = j.job_id
 
WHERE h.run_date>=CONVERT(CHAR(8),GETDATE()-1,112) AND h.run_status<>1   
 
  /* WHERE    j.name = 'Job_Name' */
 
    ORDER BYh.run_date, h.run_time

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'NumErrorLogs', REG_DWORD, 10
GO

sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ]

[, [ @event_type = ] 'event_type' ]

sp_delete_jobsteplog { [ @job_id = ] 'job_id' | [ @job_name = ] 'job_name' }

[ , [ @step_id = ] step_id | [ @step_name = ] 'step_name' ]

[ , [ @older_than = ] 'date' ]

[ , [ @larger_than = ] 'size_in_bytes' ]

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory

@job_name = N'YourSQLDba_LogBackups' ;

GO

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory ;

GO

USE [YourSQLDba]
GO
 
/****** Object:  StoredProcedure [yMaint].[LogCleanup]    Script Date: 05/28/2013 18:36:21 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-----------------------------------------------------------------------------
-- yMaint.LogCleanup (for entries older than 30 days)
-- Mail logs
-- Backup history logs
-- Job history
-- Cycle SQL Server error log
-----------------------------------------------------------------------------
create proc [yMaint].[LogCleanup]
  @jobNo Int
as
Begin
  declare @d nvarchar(8)
  declare @lockResult int
  declare @sql nvarchar(max)
 
  Begin try
 
  exec yMaint.LockMaintDb@jobNo=@jobNo, @lockType='C', @DbName = 'LogCleanUpStep', @Result = @lockResult output
  If @lockResult > 0
    Return;
 
  Set @sql = 'Exec msdb.dbo.sysmail_delete_log_sp @logged_before = "";'
  Set @sql = replace(@sql, '', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
  Set @sql = replace(@sql, '"', '''')
  Exec yExecNLog.LogAndOrExec
    @context = 'yMaint.LogCleanup'
  , @info = 'Cleanup log entries older than 30 days, begins with mail'
  , @sql = @sql
  , @JobNo = @JobNo
  
  Set @sql = 'EXECUTE msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = "";'
  Set @sql = replace(@sql, '', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
  Set @sql = replace(@sql, '"', '''')
  Exec yExecNLog.LogAndOrExec
    @context = 'yMaint.LogCleanup'
  , @info = 'Cleanup log entries older than 30 days, for mailitems'
  , @sql = @sql
  , @JobNo = @JobNo
 
  -- clean backup history
  Set @sql = 'exec  Msdb.dbo.sp_delete_backuphistory   @oldest_date = "" '
  Set @sql = replace(@sql, '', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
  Set @sql = replace(@sql, '"', '''')
  Exec yExecNLog.LogAndOrExec
    @context = 'yMaint.LogCleanup'
  , @info = 'Cleanup log entries older than 30 days, for backup history'
  , @sql = @sql
  , @JobNo = @JobNo
  
  -- clean sql agent job history
  Set @sql = 'EXECUTE  Msdb.dbo.sp_purge_jobhistory  @oldest_date = ""'
  Set @sql = replace(@sql, '', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
  Set @sql = replace(@sql, '"', '''')
  Exec yExecNLog.LogAndOrExec
    @context = 'yMaint.LogCleanup'
  , @info = 'Cleanup log entries older than 30 days, for job history'
  , @sql = @sql
  , @JobNo = @JobNo
  
  -- clean job maintenance job history (SQL Server own maintenance)
  Set @sql = 'EXECUTE  Msdb.dbo.sp_maintplan_delete_log null,null,""'
  Set @sql = replace(@sql, '', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
  Set @sql = replace(@sql, '"', '''')
  Exec yExecNLog.LogAndOrExec
    @context = 'yMaint.LogCleanup'
  , @info = 'Cleanup log entries older than 30 days, for SQL Server job maintenace plans'
  , @sql = @sql
  , @JobNo = @JobNo
  
  -- archive current log, and start a new one
  Set @sql = 'Execute sp_cycle_errorlog'
  Set @sql = replace(@sql, '', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
  Set @sql = replace(@sql, '"', '''')
  Exec yExecNLog.LogAndOrExec
    @context = 'yMaint.LogCleanup'
  , @info = 'Recycle Sql Server error log, start a new one'
  , @sql = @sql
  , @JobNo = @JobNo
 
  Delete H
  From
    (
    Select distinct JobNo --
    From  Maint.JobHistory
    Where JobStart < dateadd(dd, -30, getdate())
    ) as T
    join
    Maint.JobHistory H  
    On H.JobNo = T.JobNo
 
  End try
  Begin catch
    exec yMaint.UnLockMaintDb@jobNo=@jobNo, @DbName = 'LogCleanUpStep'
    Exec yExecNLog.LogAndOrExec
        @context = 'yMaint.LogCleanup'
      , @Info = 'Error caught in proc'  
      , @err = '?'
      , @JobNo = @JobNo
  End Catch
 
  exec yMaint.UnLockMaintDb@jobNo=@jobNo, @DbName = 'LogCleanUpStep'
 
End -- yMaint.LogCleanup
 
GO