Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Clearing out msdb.dbo.sysmail_mailitems... Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 10:06 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 730, Visits: 5,333
Lowell,

I actually just stole your code, pretty much word for word. Below is the Agent Job step 1 I'm using:
use msdb
go

declare @DeleteToDate datetime()

set @DeleteToDate = DATEADD(d, -30, getdate())

DELETE FROM msdb.dbo.sysmail_allitems
where sent_date < @DeleteToDate

DELETE FROM msdb.dbo.sysmail_log
where log_date < @DeleteToDate

delete from msdb.dbo.sysmail_mailitems
where sent_date < @DeleteToDate

Then step 2:
use msdb
go

dbcc shrinkdatabase(N'MSDB')
go

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)
GO

As for the rest of the space, it's still in sysmail_mailitems and I suspect the reason for it is the body column. The manager who uses DBMail to send out stuff, is sending some HTML e-mails, so I suspect those are taking up a good bit of space in the table. Thankfully, the server isn't tight on disk space, so I can live with a 23-24GB DB.

Once more, thanks!
Jason
Post #1375572
Posted Tuesday, October 23, 2012 5:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 1,289, Visits: 2,844
If you have a massive amount of rows you are deleting rebuild the indexes after that. That will free up your space.


Post #1375984
Posted Thursday, November 8, 2012 4:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:57 PM
Points: 68, Visits: 408
Sorry to hijack this thread but I am trying to clear out the sysmail_mailitems table myself on a SQL 2005 SP4 instance using the sysmail_delete_mailitems_sp procedure and leaving 30 days history. The command I am executing is as follows:

DECLARE @DelDate nvarchar(20) ;
SET @DelDate = DATEADD(d,-30, GETDATE())
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DelDate;

This runs successfully and has removed the rows from the table as expected however I can not shrink the database using the command below. It still thinks that there is over 6 GB of data in the sysmail_mailitems table.

DBCC ShrinkFile (MSDBData, 1536)
GO
DBCC ShrinkFile (MSDBLog, 10)

I have restored a copy of msdb before I ran the procedure and that did the same delete leaving 30 days history and then successfully shrunk.

Below are sizes of the sysmail_mailitems table in msdb after the following scenarios:

1) Before anything is run
2) After running sysmail_delete_mailitems_sp and shrink
3) Restoring a copy of msdb called msdb2 and running sysmail_delete_mailitems_sp and shrink
4) Taking a backup of the table by doing select * into bk_sysmail_mailitems from sysmail_mailitems on the msdb after scenario 2.

ObjectName TotalRows TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB
sysmail_mailitems 849 781644 781526 255 6106 6105 1
sysmail_mailitems 42 804902 804811 15 6288 6287 0
sysmail_mailitems 42 76362 76337 11 596 596 0
bk_sysmail_mailitems 42 99459 99454 13 777 776 0

It looks like for whatever reason the pages are not releasing but i'm not sure why, I have backed up the database with truncating the log, ran "sp_spaceused @updateusage = true" incase there is anything wrong with the stats, rebuilt the clustered index on the table and nothing has helped.

I have found a few posts similar to this issue but no one seems to have a resolution, do you guys have any ideas?

Thanks in advance.

Dave
Post #1382412
Posted Thursday, November 8, 2012 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 1,289, Visits: 2,844
There are several reasons for it. Typically the rows that are active are at the end of the file so it isn't shrinking. Rebuilding indexes on the tables will move the data to a different part of the file and then you should be able to shrink.


Post #1382421
Posted Thursday, November 8, 2012 5:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
Markus (11/8/2012)
There are several reasons for it. Typically the rows that are active are at the end of the file so it isn't shrinking. Rebuilding indexes on the tables will move the data to a different part of the file and then you should be able to shrink.

good point Markus!

here's what i think is everything we are talking about here...delete items, check the fragmentation, reindex, and then shrink...I added a script to show the fragmentation of the index on ;
then if you do the dbcc reindex, it'll probably go to zero (it did on mine)
then when you shrink, it would return that space back.
FYI, my mailitems index was only 14% fragmentation before the delete,56% after the delete, and zero after the reindex.
so the deleted rows are still taking up space in the indexes.
use msdb
go

declare @DeleteToDate datetime

set @DeleteToDate = DATEADD(d, -30, getdate())

DELETE FROM msdb.dbo.sysmail_allitems
where sent_date < @DeleteToDate

DELETE FROM msdb.dbo.sysmail_log
where log_date < @DeleteToDate

delete from msdb.dbo.sysmail_mailitems
where sent_date < @DeleteToDate

--just how fragmented is our index after deleting stuff?
--limited to the PK indexes on sysmail_mailitems and sysmail_log
SELECT
ps.database_id,
ps.OBJECT_ID,
ps.index_id,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b
ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
and b.name IN('sysmail_mailitems_id_MustBeUnique','sysmail_log_id_MustBeUnique')
--ORDER BY ps.OBJECT_ID
ORDER BY ps.avg_fragmentation_in_percent DESC




--reindex so the empty space is not in the middle of the pages of the index
DBCC DBREINDEX(sysmail_mailitems,sysmail_mailitems_id_MustBeUnique)
DBCC DBREINDEX(sysmail_log,sysmail_log_id_MustBeUnique)

dbcc shrinkdatabase(N'MSDB')
go

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)
GO



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1382439
Posted Thursday, November 8, 2012 7:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:57 PM
Points: 68, Visits: 408
Thank you both for your replies. I had already reindexed the primary key on this table to no avail.

I have run the reindex again though, below is the results which are both at 0% but the space still has not freed up.

name avg_fragmentation_in_percent
sysmail_mailitems_id_MustBeUnique 0
sysmail_mailitems_id_MustBeUnique 0
sysmail_log_id_MustBeUnique 0

As can be seen below after running sp_spaceused the unused space is only 728KB still...totally baffled

name rows reserved data index_size unused
sysmail_mailitems 52 6439200 KB 6438456 KB 16 KB 728 KB

Post #1382471
Posted Thursday, November 8, 2012 7:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 1,289, Visits: 2,844
Run a report on the MSDB data with Standard Reports, Disk usage by table. Find the largest tables and rebuild the indexes on them. You still have active data towards the end of the data file.


Post #1382475
Posted Thursday, November 8, 2012 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:57 PM
Points: 68, Visits: 408
Hi Markus,

I have rebuilt all indexes on msdb database and still no change. symail_mailitems is still telling me it is the largest table at 6,439,200 KB the next largest coming in at just 264,344KB.
Post #1382477
Posted Thursday, November 8, 2012 7:16 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 730, Visits: 5,333
Have you tried running DBCC SHRINKDATABASE on it?

Reading the MSDN article on it, it sounds like it will move all the allocated pages to the beginning of the file, thus allowing you to release the space at the end of the file to the OS.

Also, I just read a bit closer your post about the space still in mailitems. What I noticed is that the stored procedure didn't seem to clear that out, so I'm not entirely sure on what it really is doing. I poked around in it, and it looks like it actually removes entries from a view. Which is why I instead deleted directly from the three tables in my code.
Post #1382478
Posted Thursday, November 8, 2012 7:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 1,289, Visits: 2,844
Hum... I don't know what to tell you... that table is quite large though.

Try doing a backup of msdb and restoring it as a different name and see if you can shrink that one.



Post #1382482
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse