Post reply

Clearing out msdb.dbo.sysmail_mailitems...

  • November 8, 2012 at 7:01 am

    #1557157

    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_MustBeUnique0

    sysmail_mailitems_id_MustBeUnique0

    sysmail_log_id_MustBeUnique0

    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_mailitems52 6439200 KB6438456 KB16 KB728 KB

  • November 8, 2012 at 7:11 am

    #1557161

    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.

  • November 8, 2012 at 7:15 am

    #1557163

    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.

  • November 8, 2012 at 7:16 am

    #1557164

    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.

  • November 8, 2012 at 7:18 am

    #1557168

    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.

  • November 8, 2012 at 7:30 am

    #1557180

    dhechle (11/8/2012)

    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.

    what is the number of rows left on this table?.

    Regards
    Durai Nagarajan

  • November 8, 2012 at 8:52 am

    #1557220

    Jason, done a DBCC ShrinkDatabase and it still thinks there's just over 6GB to the database. The view that the stored proc uses just sits ontop of the table, if I check the table itself the records are being removed from there but the space not freed up 🙁 Just to eliminate it though i ran what your doing at the table level and still doesn't release the space.

    Markus, if I backup and restore to a different name the problem still persists.

    Durai, there are only 51 rows left in this table now.

    Thanks for your suggestions so far guys.

  • November 8, 2012 at 8:59 am

    #1557223

    Man... I am at a loss.

  • November 9, 2012 at 12:31 am

    #1557476

    dhechle (11/8/2012)

    Durai, there are only 51 rows left in this table now.

    Can you post "Disk usage by top tables " top 5 tables complete data from the report.

    Try shrinking the DB when agent service is stopped but definetely not in production.

    Regards
    Durai Nagarajan

  • November 9, 2012 at 3:11 am

    #1557518

    Durai,

    Here is the list of top 5 tables from the report, i have exported to excel and pasted in here so hope you can make sense of it. I have stopped the SQL Server Agent and shrunk, again to no avail, it still thinks the data is there. This is all on a test box, glad i did it on there first before approaching this on production!

    Table Name# RecordsReserved (KB)Data (KB)Indexes (KB)Unused (KB)

    dbo.sysmail_mailitems436,500,5766,499,81624736

    dbo.DTA_tuninglog60,771264,352261,240403,072

    dbo.DTA_reports_query8,45050,08849,76024304

    dbo.DTA_reports_querycolumn217,22511,1686,1684,96832

    dbo.DTA_reports_queryindex42,9962,2721,21696096

  • November 9, 2012 at 4:14 am

    #1557541

    try truncate table option after your necessary records backup

    Regards
    Durai Nagarajan

  • November 9, 2012 at 5:51 am

    #1557580

    AH... THE DTA tables are from the database tuning advisor. Log into that and you will see saved tuning profiles.... delete them if you don't care to have them anymore. Those are quite large when it comes to consuming space in MSDB as I found out.

  • November 9, 2012 at 10:04 am

    #1557703

    Really strange.....the server (test server) was restored to a point in time from a cpl of weeks ago for another reason. I then decided to take the folllowing steps to try again:

    1) Rebuild all indexes on msdb

    2) Turn off SQL SErver Agent

    3) Delete everything apart from last 30 days mails

    The space this time was released and can shrink the msdb database.

    thank god that is over, now for production 🙂

    Thanks for everyone's advice!

  • August 21, 2013 at 9:43 am

    #1643516

    Been having the same problem myself, the reason why the restored db worked is explained here:

    http://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/

    Now to find a suitable maintenance window!

    Gaz

  • March 29, 2015 at 7:20 pm

    #1787199

    SQL Maintenance Plans have an option "History Cleanup Task"."SQL Server Agent job history" that I assume cleans up job history in a supplied fashion.

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply