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

  • Artículo

Las tablas msdb mantienen copias de los mensajes del Correo electrónico de base de datos y sus archivos adjuntos, además del registro de eventos del 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.

  1. En el primer procedimiento se crea con cuatro pasos un trabajo denominado Archivar mensajes del Correo electrónico de base de datos.
  2. En el primer paso se copian todos los mensajes de las tablas del Correo electrónico de base de datos en una nueva tabla con el nombre del mes anterior en el formato DBMailArchive_<año_mes>.
  3. En el segundo paso se copian todos los archivos adjuntos relacionados con los mensajes copiados en el primer paso, de las tablas del Correo electrónico de base de datos a una nueva tabla con el nombre del mes anterior en el formato DBMailArchive_Attachments_<año_mes>.
  4. En el tercer paso se copian todos los eventos del registro de eventos del Correo electrónico de base de datos relacionados con los mensajes copiados en el primer paso, de las tablas del Correo electrónico de base de datos a una nueva tabla con el nombre del mes anterior en el formato DBMailArchive_Log_<año_mes>.
  5. En el cuarto paso se eliminan los registros de los elementos de correo transferidos de las tablas del Correo electrónico de base de datos.
  6. En el quinto paso se eliminan los eventos relacionados con los elementos de correo transferidos del registro de eventos del Correo electrónico de base de datos.
  7. En el procedimiento final se programa el trabajo para que se ejecute al principio de cada mes.

Para este ejemplo, las tablas de archivado se crean en la base de datos msdb. Con el fin de reducir el tamaño de la base de datos msdb, las nuevas tablas se pueden colocar en una base de datos de archivado especial o bien se pueden exportar las filas a un archivo de texto, o simplemente eliminarlas. En este ejemplo sólo se mueven las filas a las nuevas tablas de la base de datos msdb. En un entorno de producción, puede agregar otros procedimientos de comprobación de errores y enviar un mensaje de correo electrónico a los operadores si el trabajo provoca un error.

Crear un trabajo del Agente SQL Server

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

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

  3. En el cuadro Propietario, confirme que el propietario es miembro de la función fija de servidor sysadmin.

  4. En el cuadro Categoría, haga clic en Mantenimiento de bases de datos.

  5. En el cuadro Descripción, escriba Archivar mensajes del Correo electrónico de base de datos y, a continuación, haga clic en Pasos.

Crear un paso para archivar los mensajes del Correo electrónico de base de datos

  1. En la página Pasos, haga clic en 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 Secuencia de comandos 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 con el nombre del mes anterior y 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. Haga clic en Aceptar para guardar el paso.

Crear un paso para archivar los archivos adjuntos del Correo electrónico de base de datos

  1. En la página Pasos, haga clic en Nuevo.

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

  3. En el cuadro Tipo, seleccione Secuencia de comandos 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 archivos 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. Haga clic en Aceptar para guardar el paso.

Crear un paso para archivar el registro del Correo electrónico de base de datos

  1. En la página Pasos, haga clic en 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 Secuencia de comandos 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. Haga clic en Aceptar para guardar el paso.

Crear un paso para eliminar las filas archivadas del Correo electrónico de base de datos

  1. En la página Pasos, haga clic en 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 Secuencia de comandos Transact-SQL (T-SQL).

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

  5. En el cuadro Comando, escriba la instrucción siguiente para eliminar 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. Haga clic en Aceptar para guardar el paso.

Crear un paso para eliminar los elementos archivados del registro de eventos del Correo electrónico de base de datos

  1. En la página Pasos, haga clic en Nuevo.

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

  3. En el cuadro Tipo, seleccione Secuencia de comandos Transact-SQL (T-SQL).

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

  5. En el cuadro Comando, escriba la instrucción siguiente para eliminar 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 ;

  6. Haga clic en Aceptar para guardar el paso.

Programar el trabajo para que se ejecute al principio de cada mes

  1. En el cuadro de diálogo Nuevo trabajo, haga clic en Programaciones.

  2. En la página Programaciones, haga clic en 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 el primer día de cada mes.

  6. En el área Frecuencia diaria, seleccione Sucede una vez a las 3:00:00 a.m..

  7. Compruebe que las demás opciones están configuradas tal como desea y, a continuación, haga clic en Aceptar para guardar la programación.

  8. Haga clic en Aceptar para guardar el trabajo.

Seguridad

Debe ser miembro de la función fija de servidor sysadmin para ejecutar los procedimientos almacenados que se describen en este tema.

Vea también

Conceptos

Correo electrónico de base de datos

Otros recursos

Vistas del Correo electrónico de base de datos (Transact-SQL)
Procedimientos almacenados de Correo electrónico de base de datos y SQL Mail (Transact-SQL)
Creating SQL Server Agent Jobs

Ayuda e información

Obtener ayuda sobre SQL Server 2005