October 17, 2012 at 10:54 am
OK, so we (I) finally noticed that the MSDB backup was rather large. In looking into the problem I found that the table dbo.sysmail_mailitems is taking about about 140,000,000KB (133GB if you prefer) It got this large for a couple reasons:
1. When we set up Database Mail, we didn't realize it would store the messages here...
2. Our previous backup method was more "automated" so we never noticed the backup getting as large as it did.
3. OK, yes, I also never thought to test restore MSDB... :Whistling:
So, I tried clearing it out using the stored procedure "sysmail_delete_mailitems" which did not help. Even setting it to delete a relatively small date range (@sent_before,) it runs so long we need to cancel it.
I'm working on a copy of the DB now, and I thought I'd try just truncating the table (we don't really need to store all these old messages,) but that failed due to some foreign key constraints. So I'm coming to you folks for help. What's going to be the best way to clear this thing out?
Can I do a "delete from..." to blow out the table? Or is this going to run into the problem of the FKs also? Will a straight "delete from..." take a long time as well (in which case I could put a "where" clause on the end, and just keep running it, blowing out a handful of records every time...)
The DB *IS* in Simple Recovery, although based on what I've read I would expect the LDF to grow quite a bit during a delete operation. That I could then control by backing up the DB and checkpointing, correct? Or would I need to also do a "DBCC shrinkfile" on it?
Thanks,
Jason A.
October 17, 2012 at 11:09 am
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.
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
October 17, 2012 at 11:13 am
Lowell I'll give those a try once my re-restore of the DB on my test box completes...
It only takes about an hour-and-a-half or so to restore...
😉
Thanks!
Jason
October 18, 2012 at 12:04 am
Hello,
Is there a way to delete it automatically?
Regards
Durai Nagarajan
October 18, 2012 at 5:26 am
durai nagarajan (10/18/2012)
Is there a way to delete it automatically?
Scehdule a JOB 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 18, 2012 at 7:44 am
funny and thanks but is there no other option?
Regards
Durai Nagarajan
October 18, 2012 at 7:48 am
While it was a bit of snarky reply, it's also true. If you want to automate something like this, it's going to have to be scheduled somehow. Either using SQL Agent Jobs, Windows Task Scheduler and batch files, or a reminder on someone's calendar.
Jason
October 18, 2012 at 7:52 am
the scriptlet i posted is what i've put in a scheduled job before;
just letting it run once a day deletes anything older than 30 days;
that's what would be my simple sugestion to keep the data under control, especially if you have a fairly busy usage of database mail.
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
Lowell
October 22, 2012 at 9:39 am
My thanks to Lowell!
Using your Delete commands, plus adding one to delete from dbo.sysmail_mailitems, the DB is down to about 23GB from 133GB!
Set up an Agent job to run once a week and keep the last 30 days of records in all three tables, so I shouldn't have to worry about that one again...
Once more, thank you!
Jason A.
October 22, 2012 at 9:54 am
jasona.work (10/22/2012)
My thanks to Lowell!Using your Delete commands, plus adding one to delete from dbo.sysmail_mailitems, the DB is down to about 23GB from 133GB!
Set up an Agent job to run once a week and keep the last 30 days of records in all three tables, so I shouldn't have to worry about that one again...
Once more, thank you!
Jason A.
Glad to hear you've got it handled, Jason!
Maybe post your final solution so others can benefit from your research?
i think the other peice of the puzzle is deleting old job histories, right? i think the view is sysjobhistory
how big is your table right now? is it most of that remaining gigs of space?
exec sp_spaceused 'msdb.dbo.sysjobhistory'
again, from my snippets for maint of msdb, i have this:
DECLARE @oldestdate DATETIME
SET @oldest = GETDATE() - 30
EXEC SP_PURGE_JOBHISTORY @oldest_date = @oldestdate
Lowell
October 22, 2012 at 10:06 am
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
October 23, 2012 at 5:53 am
If you have a massive amount of rows you are deleting rebuild the indexes after that. That will free up your space.
November 8, 2012 at 4:38 am
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_mailitems849 781644 781526 255 6106 6105 1
sysmail_mailitems42 804902 804811 15 6288 6287 0
sysmail_mailitems42 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
November 8, 2012 at 5:00 am
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.
November 8, 2012 at 5:45 am
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
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply