Crear un trabajo del Agente SQL Server para archivar mensajes y registros de eventos del Correo electrónico de base de datos

Se aplica a:SQL ServerAzure SQL Managed Instance

Las copias de los mensajes de Correo electrónico de base de datos y sus datos adjuntos se conservan en msdb tablas junto con el registro de eventos de Correo electrónico de base de datos. Puede reducir el tamaño de las tablas y eliminar los mensajes y eventos que ya no sean necesarios periódicamente.

Los procedimientos siguientes permiten crear un trabajo del Agente SQL Server para automatizar el proceso.

Requisitos previos

Para ejecutar comandos T-SQL en la instancia de SQL Server, use SQL Server Management Studio (SSMS), la extensión MSSQL para Visual Studio Code, sqlcmd o la herramienta de consulta T-SQL favorita.

Recomendaciones

Considere la posibilidad de comprobar errores y supervisar este trabajo para enviar un mensaje de correo electrónico a los operadores si se produce un error en este trabajo de archivo.

Opcionalmente, puede mover datos de Correo electrónico de base de datos archivados a una base de datos de archivo personalizada fuera de msdbo exportarlos desde SQL Server.

Permisos

Debe ser miembro del rol fijo de servidor sysadmin para ejecutar los procedimientos almacenados que se describen en este tema.

Creación de un trabajo de Correo electrónico de base de datos de archivo

En el primer procedimiento se crea con cuatro pasos un trabajo denominado Archivar mensajes del Correo electrónico de base de datos.

1. Copie todos los mensajes de las tablas de Correo de base de datos a una nueva tabla nombrada con el mes anterior, con el formato DBMailArchive__<year_month>.

2. Copie los datos adjuntos relacionados con los mensajes copiados en el primer paso, de las tablas correo electrónico de base de datos a una nueva tabla denominada después del mes anterior en el formato DBMailArchive_Attachments_<year_month>.

3. Copie los eventos del registro de eventos de Correo de base de datos que están relacionados con los mensajes copiados en el primer paso, de las tablas de Correo de base de datos a una nueva tabla nombrada con el mes anterior en el formato DBMailArchive_Log_<year_month>.

4. Eliminar los registros de los elementos de correo transferidos de las tablas del Correo electrónico de base de datos.

5. Eliminar los eventos relacionados con los elementos de correo transferidos del registro de eventos del Correo electrónico de base de datos.

6. Programar el trabajo para ejecutar periódicamente.

Crear un trabajo del Agente SQL Server

Para los pasos siguientes, se usa SQL Server Management Studio (SSMS). Descargue la versión más reciente de SSMS en aka.ms/ssms.

1. Conéctese a la instancia de SQL Server.

2. En el Explorador de objetos, expanda Agente SQL Server, haga clic con el botón derecho en Trabajos y, a continuación, seleccione Nuevo trabajo.

3. En el cuadro Nombre del cuadro de diálogo Nuevo trabajo , escriba Archivar mensajes del Correo electrónico de base de datos.

4. En el cuadro Propietario , confirme que el propietario es miembro del rol fijo de servidor sysadmin .

5. En el cuadro Categoría , seleccione El mantenimiento de la base de datos.

6. En el cuadro Descripción , escriba Archivar mensajes de Correo electrónico de base de datos y, a continuación, seleccione Pasos.

Creación de un paso de trabajo para archivar los mensajes de Correo electrónico de base de datos

1. En la página Pasos , seleccione Nuevo.

2. En el cuadro Nombre del paso , escriba Copiar elementos del Correo electrónico de base de datos.

3. En el cuadro Tipo , seleccione Script Transact-SQL (T-SQL).

4. En el cuadro Base de datos , seleccione msdb.

5. En el cuadro Comando , escriba la siguiente instrucción T-SQL para crear una tabla denominada después del mes anterior, que contiene filas anteriores al inicio del mes actual.

   DECLARE @LastMonth nvarchar(12);  
   DECLARE @CopyDate nvarchar(20) ;  
   DECLARE @CreateTable nvarchar(250) ;  
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;  
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))  
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate +'''';  
   EXEC sp_executesql @CreateTable ;  
   

6. Seleccione Aceptar para guardar el paso.

Crear un paso de trabajo para archivar los archivos adjuntos de Database Mail

1. En la página Pasos , seleccione Nuevo.

2. En el cuadro Nombre del paso , escriba Copiar datos adjuntos del Correo electrónico de base de datos.

3. En el cuadro Tipo , seleccione Script Transact-SQL (T-SQL).

4. En el cuadro Base de datos , seleccione msdb.

5. En el cuadro Comando , escriba la instrucción siguiente para crear una tabla de datos adjuntos con el nombre del mes anterior y los archivos adjuntos correspondientes a los mensajes transferidos en el paso anterior:

   DECLARE @LastMonth nvarchar(12);  
   DECLARE @CopyDate nvarchar(20) ;  
   DECLARE @CreateTable nvarchar(250) ;  
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;  
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))  
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Attachments_' + @LastMonth + '] FROM sysmail_attachments   
    WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';  
   EXEC sp_executesql @CreateTable ;  
   

6. Seleccione Aceptar para guardar el paso.

Crear un paso de trabajo para archivar el registro de Correo de la base de datos

1. En la página Pasos , seleccione Nuevo.

2. En el cuadro Nombre del paso , escriba Copiar el registro del Correo electrónico de base de datos.

3. En el cuadro Tipo , seleccione Script Transact-SQL (T-SQL).

4. En el cuadro Base de datos , seleccione msdb.

5. En el cuadro Comando , escriba la instrucción siguiente para crear una tabla de registros con el nombre del mes anterior y las entradas de registro correspondientes a los mensajes transferidos en el paso anterior:

   DECLARE @LastMonth nvarchar(12);  
   DECLARE @CopyDate nvarchar(20) ;  
   DECLARE @CreateTable nvarchar(250) ;  
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;  
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))  
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Log_' + @LastMonth + '] FROM sysmail_Event_Log   
    WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';  
   EXEC sp_executesql @CreateTable ;  
   

6. Seleccione Aceptar para guardar el paso.

Crear un paso de trabajo para eliminar las filas archivadas de Database Mail

1. En la página Pasos , seleccione Nuevo.

2. En el cuadro Nombre del paso , escriba Eliminar filas del Correo electrónico de base de datos.

3. En el cuadro Tipo , seleccione Script Transact-SQL (T-SQL).

4. En el cuadro Base de datos , seleccione msdb.

5. En el cuadro Comando , escriba la instrucción siguiente para quitar las filas anteriores al mes actual de las tablas del Correo electrónico de base de datos:

   DECLARE @CopyDate nvarchar(20) ;  
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;  
   EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate ;  
   

6. Seleccione Aceptar para guardar el paso.

Crear una tarea para eliminar los elementos archivados del registro de eventos de Database Mail

1. En la página Pasos , seleccione Nuevo.

2. En el cuadro Nombre del paso, escriba Quitar filas del registro de eventos de Correo de Base de Datos.

3. En el cuadro Tipo , seleccione Script Transact-SQL (T-SQL).

4. En el cuadro Comando , escriba la instrucción siguiente para quitar las filas anteriores al mes actual del registro de eventos del Correo electrónico de base de datos:

   DECLARE @CopyDate nvarchar(20) ;  
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;  
   EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate ;  
   

5. Seleccione Aceptar para guardar el paso.

Programar el trabajo para que se ejecute periódicamente

1. En el cuadro de diálogo Nuevo trabajo , seleccione Programaciones.

2. En la página Programaciones , seleccione Nuevo.

3. En el cuadro Nombre , escriba Archivar mensajes del Correo electrónico de base de datos.

4. En el cuadro Tipo de programación , seleccione Periódica.

5. En el área Frecuencia , seleccione las opciones para ejecutar el trabajo periódicamente, por ejemplo una vez al mes.

6. En el área Frecuencia diaria, seleccione Sucede una vez a las <hora>.

7. Compruebe que las otras opciones están configuradas como desee y, a continuación, seleccione Aceptar para guardar la programación.

8. Seleccione Aceptar para guardar el trabajo.

Contenido relacionado

• Pasos generales de solución de problemas de correo electrónico de base de datos