Início > DiskSpace Management > SQL Server: Controlling the growth of msdb database caused by Database Mail

SQL Server: Controlling the growth of msdb database caused by Database Mail

Pingback from : http://www.sqlservergeeks.com/articles/sql-server-bi/18/sql-server-controlling-the-growth-of-msdb-database-caused-by-database-mail

This should be the common issue faced by many of us where the database mail is used to send bulk mails on a regular basis. We have a database server which is specifically used for sending weekly newsletters to all the subscribed users. The number of subscriptions increased over a period of time and so as the number of newsletters. One day the disk drive, holding the system databases, started alerting for the low disk space. On investigation, I found that the msdb database has grown to 7 GB in size!

As we know that msdb maintains various types of history information like backup history, jobs history, sent mails and attachments history etc. Now the question is to find out the table(s) holding the largest data. I have found the following query from the Net which helped me to find out the name of the tables in descending order of space consumed on the disk.

(As the above issue had already been fixed, I can not show you the 7 GB grown msdb. For this demo I am running this query on a server having 1.5 GB grown msdb)

use msdb
go
sp_helpdb msdb

msdb database report
--Query 1 - Checking the size of tables in a database
select 
	object_name(i.object_id) as ObjectName,
	i.[name] as IndexName,
	sum(a.total_pages) as TotalPages,
	sum(a.used_pages) as UsedPages,
	sum(a.data_pages) as DataPages,
	(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
	(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
	(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from 
	sys.indexes i
	inner join sys.partitions p 
		on i.object_id = p.object_id and i.index_id = p.index_id
	inner join sys.allocation_units a
		on p.partition_id = a.container_id
group by 
	i.object_id, 
	i.index_id, 
	i.[name]
order by 
	sum(a.total_pages) desc, 
	object_name(i.object_id)
go

Checking the size of tables in a database

There are also  other types of script to get the audit job done (find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database):

USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

 

 

We can see that sysmail_mailitems table is occupying most of the space on the disk, around 1.3 GB spanning across 171812 pages. This table stores the database mail messages which are sent, unsent, retrying, and failed. Microsoft recommends maintaining this table by periodically deleting the messages based on the organizations document retention program. The sysmail_delete_mailitems_sp procedure located in the msdb database permanently deletes e-mail messages from this table. The syntax of the procedure is shown below

sysmail_delete_mailitems_sp
[ [ @sent_before = ] ‘sent_before’ ]
[ , [ @sent_status = ] ‘sent_status’

The date option allows deleting e-mails before the specified date and the status option allows deleting e-mails of a particular type i.e. sent, unsent, retrying, and failed.

As deleting a huge amount of records in one shot takes a good amount of time we can purge the data in small batches. The below query will identify since when the data is lying in the table. This is especially useful when the data is piled up over a long period of time.

--Query 2 - Checking the records by year, month
select 
	datepart(yy,sent_date) [Year], 
	datepart(mm,sent_date) [Month], 
	count(*) 'NoOfRows'
from 
	sysmail_mailitems
group by 
	datepart(yy,sent_date), 
	datepart(mm,sent_date)
order by 
	1,2

Checking the records by year and month

The table has data for two months. Let’s keep the data for March and deleted the February data. The following query will do the job,

--Query 3 – Purging the data before 1st March 2011
DECLARE @DelDate nvarchar(20) ;
SET @DelDate = '03/01/2011'
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DelDate;

Purging the data

Now run the Query 1 and check the output. The total table space which was 1372 MB has come down to 623 MB and also the Total Pages from 171812 to 79836.

Checking the size of tables in a database

Let’s check the database size using,

sp_helpdb msdb

msdb database reporting

No luck???!!! In fact the log size has increased. Don’t worry, now we need to truncate the log and shrink the database so that the free space will be release to the operating system. Run the following code and recheck the database size.

--Query 4 - Truncate the log and shrink the database 
checkpoint

backup log msdb with truncate_only

dbcc shrinkdatabase ('msdb')

sp_helpdb msdb

msdb database report

It worked…Smile

  1. Ainda sem comentários.
  1. No trackbacks yet.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: