Sql Server 2005 - Fast growing msdb 2

[EriRobert]

Hello
Our SQL server 2005 msdb database is growing at rate of 100 Meg per day and is currently 4844 Meg.

Having looked at thread962-1142670 I can discount dts packages (none in use), and backupset (1325 records). Job history is 999. I'm struggling to find a table with a large number of records.

Any ideas

Thanks

 

[EriRobert]

.... I should add a reference to my other thread which may or may not be relevant (thread962-1446671). This was to do with email attachments that were dumped in a temporary folder, which I manually deleted. Are these attachments also stored in msdb? If so, where?

Thanks

 

[mrdenny]

They are probably stored in the message queue which database mail uses to send the message.

Check the sys.transmission_queue DMV and see what's sitting in there.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[EriRobert]

Thanks for the reply Denny.

The sys.transmission_queue was empty, but snuffling around the other system views I notice that

select * from msdb.dbo.sysmail_mailattachments

seems to indicate that my attachments are being retained of successfully sent items.

How do I go about purging these sent items from the system?

Thanks

Robert

 

[EriRobert]

The answer is:

DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = @GETDATE

to purge them all.

Thanks Denny - you set me on the right track.

 

[EriRobert]

Sorry to come back again, but a related question. Having run above mail delete I am left with table properties as below:

table Data Space Index Space Row Count
sysmail_attachments 2566 MB 0.016 MB 70
sysmail_attachments_transfer 2131 MB 0.008 MB 4

I understand that this (large amount of) data space will get reused, but I don't seem to be able to find a method of reclaiming this space. I thought DBCC UPDATEUSAGE would help, but it doesn't.

Thanks in advance

 

[mrdenny]

Stupid question here, were you logged in with sysadmin rights when you ran the sysmail_delete_mailitems_sp procedure?

Do you have any records left in the sysmail_mailitems table?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[EriRobert]

I took these statistics 24 hours after running the mail_delete routines. When I look 48 hours later at the properties of msdb the Space Available is now 4000 meg (it was small before); this has allowed be to shrink the database regaining the space in the two tables in question.

I'm surprised how long SQL Server can take to adjust itself. Is this amount of time normal?

Thanks for the input though.

 

[mrdenny]

It probably is normal. I've never had to setup SQL Mail for a system with large amounts of email being sent.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[donoghuem]

I had the exact issue.
With a 0 rowcount I have 2GB of data in the sysmail_attachments and about 1GB in sysmail_attachments_transfer. I archived all my sysmail_attachments and sysmail_attachments_transfer to an archive database.

The ONLY way I was able to resolve this issue was by truncating the 2 tables using the following commands:

use msdb
go
truncate table dbo.sysmail_attachments
truncate table dbo.sysmail_attachments_transfer

Keep in mind, this will clear out the tables so run this at your own risk. As I said before, I archived the two tables to another DB before I truncated.
Good luck!

Regards,
Michael Donoghue
Senior Systems Engineer
NBC Universal

 

[EriRobert]

Michael

Lovely - that works a treat. I'm puzzled though as to what's going on behind the scenes. I suspect my resolution '48 hours later' (see above) was due to a restart of SQL Server.

Thanks