msdb DB의 sysmail_mailitems 테이블 내용 삭제하기

 

mssql에서 제공하는 메일보내는 프로시저를 사용하면,

msdb 데이터베이스 내에 sysmail_mailitems 테이블 이 커진다.

 

sysmail_log 테이블은 보낸 로그내용이고,

sysmail_mailitems 테이블은 실제 어떤 메일이 보내졌는지 (발신/수신/메일제목/내용) 등이 적혀 있는 테이블이다.

 

 

http://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspx

 

jason if you sp_helptext sysmail_delete_mailitems_sp in the msdb database,
you'll see it's using a catch-all query to delete fromt eht able, and only one table;
after a bunch of error checking, this is the core of the proc:

   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))

i have this saved in my snippets for deleting old mail jobs, which deletes from two tables in msdb if you peek at the proc code.

 

------------

30일 전 로그 지우는 스크립트.

sp 붙은 프로시저가 따로 있음.

 

USE MSDB;
GO

DECLARE @DeleteBeforeDate DateTime 
SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

------------------------

so you could do a simple DELETE:

   DELETE FROM msdb.dbo.sysmail_allitems 
   DELETE FROM msdb.dbo.sysmail_log 

as a proof of concept, i just did exactly that on my dev machine; cleared out 38K rows of stuff. no foreign key errors, just clean deletes.

hope that helps!

Lowell

 

-----------------------------------------------------------------------------------------------------------------------------

 

http://nirav.extreme-advice.com/2013/07/18/script-to-remove-sysmail_mailitems-history/

 

Script to Remove Sysmail_mailitems History

By nirav on July 18th, 2013

Script to Remove Sysmail_mailitems History.

I am getting a space issue with my system data file drive, meanwhile I have checked the size of database and I found MSDB database contains 32 GB of data. I am shocked by this information and I have eagerly checked the size of particular table and I found ”dbo.sysmail_mailitems” table contains 31 GB of data and “dbo.sysmail_attachments” contains 1 GB of data.

I had ran the query on MSDB Database to get table size.

view source

print?

1

USE MSDB;

2

GO

3

4

SELECT

5	Schema_name(T.Schema_id) + '.' + T.Name AS Tablename,

6	Sum(P.Rows) AS Rowcounts,

7	Cast(( Sum(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS Totalspacegb

8

FROM

9	SYS.Tables T

10	INNER JOIN SYS.Indexes I

11	ON T.Object_id = I.Object_id

12	INNER JOIN SYS.Partitions P

13	ON I.Object_id = P.Object_id

14	AND I.Index_id = P.Index_id

15	INNER JOIN SYS.Allocation_units A

16	ON P.Partition_id = A.Container_id

17	INNER JOIN SYS.Filegroups Fg

18	ON I.Data_space_id = Fg.Data_space_id

19	INNER JOIN SYS.Database_files Df

20	ON Df.Data_space_id = Fg.Data_space_id

21

GROUP BY

22	Schema_name(T.Schema_id) + '.' + T.Name

23

ORDER BY

24	Totalspacegb DESC

See the Result

I have created a script to remove last 30 days mail log.
First i have tried to remove all logs older than 30 days but the situation is i have a mail log of last 6 months and deleting all records at a time also creates a space issue while deleting.
So, i have tried to delete last 30 days data and than shrink the file and than remove the last 30 days data again and this way i have completed disk space issue and purge mail history.

view source

print?

1

Use MSDB

2

Go

3	DECLARE @Deletelastmonth DATETIME

4

5	-- Remove History of last 30 Days

6

SELECT

7	@Deletelastmonth = Min(Sent_date)

8

FROM

9	DBO.Sysmail_mailitems

10	SET @Deletelastmonth = @Deletelastmonth + 30

11

12	--Sysmail_attachments and Sysmail_send_retries table has a foreign key from Sysmail_mailitems so need to remove data from both the tables first.

13

14	--Need to remove attachements otherwise you can't purge mailitem history.

15	DELETE FROM DBO.Sysmail_attachments

16	WHERE Last_mod_date <= @Deletelastmonth

17

18	--Need to remove entries from retries table too.

19	DELETE FROM DBO.Sysmail_send_retries

20	WHERE Last_send_attempt_date <= @Deletelastmonth

21

22	EXEC Sysmail_delete_mailitems_sp

23	@Sent_before = @Deletelastmonth

24

25	EXEC Sysmail_delete_log_sp

26	@Logged_before = @Deletelastmonth

Regards,
Nirav Gajjar

 

 

-------------------------------------------------------------------------------------------------------------------

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/84984a3f-2982-43c0-a8a2-832cc82b6bd3/sysmailmailitems-table-cleanup

 

Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

SQL Server 2014

Other Versions

• SQL Server 2012
• SQL Server 2008 R2
• SQL Server 2008
• SQL Server 2005

Copies of Database Mail messages and their attachments are retained in msdb tables along with the Database Mail event log. Periodically you might want to reduce the size of the tables and archive messages and events that are no longer needed. The following procedures create a SQL Server Agent job to automate the process.

• Before you begin: , Prerequisites, Recommendations, Permissions

• To Archive Database Mail messages and logs using : SQL Server Agent

Before You Begin

---

Prerequisites

The new tables to store the archive data might be located in a special archive database. Alternatively the rows could be exported to a text file.

Top

Recommendations

In your production environment, you might want to add additional error checking and send an e-mail message to operators if the job fails.

Top

Permissions

You must be a member of the sysadmin fixed server role to execute the stored procedures described in this topic.

Top

Overview of the Process

 

• The first procedure creates a job named Archive Database Mail with the following steps.

  1. Copy all messages from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_<year_month>.

  2. Copy the attachments related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Attachments_<year_month>.

  3. Copy the events from the Database Mail event log that are related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Log_<year_month>.

  4. Delete the records of the transferred mail items from the Database Mail tables.

  5. Delete the events related to the transferred mail items from the Database Mail event log.

• Schedule the job to run periodically.

Top

To create a SQL Server Agent job

---

1. In Object Explorer, expand SQL Server Agent, right-click Jobs, and then click New Job.

2. In the New Job dialog box, in the Name box, type Archive Database Mail.

3. In the Owner box, confirm that the owner is a member of the sysadmin fixed server role.

4. In the Category box, click the Database Maintenance.

5. In the Description box, type Archive Database Mail messages, and then click Steps.

Overview

To create a step to archive the Database Mail messages

---

1. On the Steps page, click New.

2. In the Step name box, type Copy Database Mail Items.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to create a table named after the previous month, containing rows older than the start of the current month:

   Copy

   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. Click OK to save the step.

Overview

To create a step to archive the Database Mail attachments

---

1. On the Steps page, click New.

2. In the Step name box, type Copy Database Mail Attachments.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to create an attachments table named after the previous month, containing the attachments that correspond to the messages transferred in the previous step:

   Copy

   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. Click OK to save the step.

Overview

To create a step to archive the Database Mail log

---

1. On the Steps page, click New.

2. In the Step name box, type Copy Database Mail Log.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to create a log table named after the previous month, containing the log entries that correspond to the messages transferred in the earlier step:

   Copy

   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. Click OK to save the step.

Overview

To create a step to remove the archived rows from Database Mail

---

1. On the Steps page, click New.

2. In the Step name box, type Remove rows from Database Mail.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to remove rows older than the current month from the Database Mail tables:

   Copy

   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. Click OK to save the step.

Overview

To create a step to remove the archived items from Database Mail event log

---

1. On the Steps page, click New.

2. In the Step Name box type Remove rows from Database Mail event log.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Command box, type the following statement to remove rows older than the current month from the Database Mail event log:

   Copy

   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. Click OK to save the step.

Overview

To schedule the job to run periodically

---

1. In the New Job dialog box, click Schedules.

2. On the Schedules page, click New.

3. In the Name box, type Archive Database Mail.

4. In the Schedule type box, select Recurring.

5. In the Frequency area, select the options to run the job periodically, for example once every month.

6. In the Daily frequency area, select Occurs once at <time>.

7. Verify that the other options are configured as you wish, and then click OK to save the schedule.

8. Click OK to save the job.

Overview