SQL Server邮件相关SQL语句出现严重的ASYNC_NETWORK_IO等待事件案例

SELECT 

      mi.mailitem_id,

      mi.profile_id,

      (SELECT name FROM msdb.dbo.sysmail_profile p WHERE p.profile_id = mi.profile_id) as 'profile_name',

      mi.recipients,

      mi.copy_recipients,

      mi.blind_copy_recipients,

      mi.subject,

      mi.body, 

      mi.body_format, 

      mi.importance,

      mi.sensitivity,

      ISNULL(sr.send_attempts, 0) as retry_attempt,

      ISNULL(mi.from_address, '') as from_address,

      ISNULL(mi.reply_to, '')     as reply_to

   FROM sysmail_mailitems as mi

      LEFT JOIN sysmail_send_retries as sr

         ON sr.mailitem_id = mi.mailitem_id 

   WHERE mi.mailitem_id = @mailitem_id

SELECT * FROM msdb.dbo.sysmail_allitems WITH(NOLOCK)

WHERE sent_status != 'sent' 

ORDER BY sent_date DESC;

USE msdb;

GO

IF object_id('sp_GetRowSize') is not null

drop procedure sp_GetRowSize

GO

CREATE procedure sp_GetRowSize(@Tablename varchar(100),@pkcol varchar(100))

AS 

BEGIN

declare @dynamicsql varchar(MAX)

-- A @pkcol can be used to identify max/min length row

set @dynamicsql = 'select ' + @PkCol +' , (0'

-- traverse each record and calculate the datalength

select @dynamicsql = @dynamicsql + ' + isnull(datalength(' + name + '), 1)' 

    from syscolumns where id = object_id(@Tablename)

set @dynamicsql = @dynamicsql + ') as rowsize from ' + @Tablename + ' order by 2 desc'

print (@dynamicsql)

END

/******************************************************************************************************

    Script Function        ：    以下示例按条件删除数据库邮件系统中的电子邮件

*******************************************************************************************************/

DECLARE @GETDATE datetime  

SET @GETDATE = GETDATE()-2;  

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;  

GO  

DELETE 

FROM msdb.dbo.sysmail_allitems 

WHERE ((@sent_before IS NULL) 

OR ( send_request_date < @sent_before)) 

AND ((@sent_status IS NULL) 

OR (sent_status = @sent_status))

CREATE PROCEDURE  

sysmail_delete_mailitems_sp  

@sent_before DATETIME = NULL,  -- sent before

@sent_status varchar(8) = NULL -- sent status

AS  

   BEGIN  

      SET @sent_status = LTRIM(RTRIM(@sent_status))  

      IF @sent_status = ''  

      SET @sent_status = NULL  

      IF ( (@sent_status IS NOT NULL) AND  

      (LOWER(@sent_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'unsent', 'sent', 'failed' 

      , 'retrying') ) )  

      BEGIN  

         RAISERROR(14266, -1, -1, '@sent_status', 'unsent, sent, failed, retrying')  

         RETURN(1) -- Failure

      END  

      IF ( @sent_before IS NULL AND @sent_status IS NULL )  

      BEGIN  

         RAISERROR(14608, -1, -1, '@sent_before', '@sent_status')  

         RETURN(1) -- Failure

      END 

/* BEGIN ACTIVE SECTION (comment inserted by DPA) */  

      DELETE  

      FROM msdb.dbo.sysmail_allitems  

      WHERE ((@sent_before IS NULL)  

      OR ( send_request_date < @sent_before))  

     AND ((@sent_status IS NULL)  

      OR (sent_status = @sent_status)) 

/* END ACTIVE SECTION (comment inserted by DPA) */  

         DECLARE @localmessage nvarchar(255)  

         SET @localmessage = FORMATMESSAGE(14665, SUSER_SNAME(), @@ROWCOUNT) exec  

         msdb.dbo.sysmail_logmailevent_sp @event_type=1,  

         @description=@localmessage  

      END