InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:46:08 GMT20http://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxReally 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 msdb2) Turn off SQL SErver Agent3) Delete everything apart from last 30 days mailsThe space this time was released and can shrink the msdb database. thank god that is over, now for production :)Thanks for everyone's advice!Fri, 09 Nov 2012 10:04:38 GMTdhechlehttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxAH... 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.Fri, 09 Nov 2012 05:51:31 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxtry truncate table option after your necessary records backupFri, 09 Nov 2012 04:14:35 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxDurai,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 # Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB) dbo.sysmail_mailitems 43 6,500,576 6,499,816 24 736 dbo.DTA_tuninglog 60,771 264,352 261,240 40 3,072 dbo.DTA_reports_query 8,450 50,088 49,760 24 304 dbo.DTA_reports_querycolumn 217,225 11,168 6,168 4,968 32 dbo.DTA_reports_queryindex 42,996 2,272 1,216 960 96 Fri, 09 Nov 2012 03:11:39 GMTdhechlehttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspx[quote][b]dhechle (11/8/2012)[/b][hr]Durai, there are only 51 rows left in this table now.[/quote]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.Fri, 09 Nov 2012 00:31:56 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxMan... I am at a loss.Thu, 08 Nov 2012 08:59:05 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxJason, 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.Thu, 08 Nov 2012 08:52:05 GMTdhechlehttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspx[quote][b]dhechle (11/8/2012)[/b][hr]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.[/quote]what is the number of rows left on this table?.Thu, 08 Nov 2012 07:30:16 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxHum... 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.Thu, 08 Nov 2012 07:18:56 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxHave you tried running DBCC SHRINKDATABASE on it?Reading the [url=http://msdn.microsoft.com/en-us/library/ms190488.aspx]MSDN article[/url] 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.Thu, 08 Nov 2012 07:16:47 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxHi 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.Thu, 08 Nov 2012 07:15:34 GMTdhechlehttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxRun 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.Thu, 08 Nov 2012 07:11:17 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxThank 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_percentsysmail_mailitems_id_MustBeUnique 0sysmail_mailitems_id_MustBeUnique 0sysmail_log_id_MustBeUnique 0As can be seen below after running sp_spaceused the unused space is only 728KB still...totally baffled name rows reserved data index_size unusedsysmail_mailitems 52 6439200 KB 6438456 KB 16 KB 728 KBThu, 08 Nov 2012 07:01:14 GMTdhechlehttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspx[quote][b]Markus (11/8/2012)[/b][hr]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.[/quote]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.[code]use msdbgodeclare @DeleteToDate datetimeset @DeleteToDate = DATEADD(d, -30, getdate())DELETE FROM msdb.dbo.sysmail_allitems where sent_date < @DeleteToDateDELETE FROM msdb.dbo.sysmail_log where log_date < @DeleteToDatedelete 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_logSELECT ps.database_id, ps.OBJECT_ID, ps.index_id, b.name, ps.avg_fragmentation_in_percentFROM 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_idWHERE ps.database_id = DB_ID()and b.name IN('sysmail_mailitems_id_MustBeUnique','sysmail_log_id_MustBeUnique')--ORDER BY ps.OBJECT_IDORDER BY ps.avg_fragmentation_in_percent DESC--reindex so the empty space is not in the middle of the pages of the indexDBCC DBREINDEX(sysmail_mailitems,sysmail_mailitems_id_MustBeUnique)DBCC DBREINDEX(sysmail_log,sysmail_log_id_MustBeUnique)dbcc shrinkdatabase(N'MSDB')goUSE [msdb]GODBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)GO[/code]Thu, 08 Nov 2012 05:45:55 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxThere 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.Thu, 08 Nov 2012 05:00:36 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxSorry 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)GODBCC 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 run2) After running sysmail_delete_mailitems_sp and shrink 3) Restoring a copy of msdb called msdb2 and running sysmail_delete_mailitems_sp and shrink4) 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 DataSpaceMBsysmail_mailitems 849 781644 781526 255 6106 6105 1sysmail_mailitems 42 804902 804811 15 6288 6287 0sysmail_mailitems 42 76362 76337 11 596 596 0bk_sysmail_mailitems 42 99459 99454 13 777 776 0It 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.DaveThu, 08 Nov 2012 04:38:13 GMTdhechlehttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxIf you have a massive amount of rows you are deleting rebuild the indexes after that. That will free up your space.Tue, 23 Oct 2012 05:53:24 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxLowell,I actually just stole your code, pretty much word for word. Below is the Agent Job step 1 I'm using:[code="sql"]use msdbgodeclare @DeleteToDate datetime()set @DeleteToDate = DATEADD(d, -30, getdate())DELETE FROM msdb.dbo.sysmail_allitems where sent_date < @DeleteToDateDELETE FROM msdb.dbo.sysmail_log where log_date < @DeleteToDatedelete from msdb.dbo.sysmail_mailitems where sent_date < @DeleteToDate[/code]Then step 2:[code="sql"]use msdbgodbcc shrinkdatabase(N'MSDB')goUSE [msdb]GODBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)GO[/code]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!JasonMon, 22 Oct 2012 10:06:50 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspx[quote][b]jasona.work (10/22/2012)[/b][hr]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.[/quote]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 sysjobhistoryhow big is your table right now? is it most of that remaining gigs of space?[code]exec sp_spaceused 'msdb.dbo.sysjobhistory'[/code]again, from my snippets for maint of msdb, i have this:[code]DECLARE @oldestdate DATETIMESET @oldest = GETDATE() - 30EXEC SP_PURGE_JOBHISTORY @oldest_date = @oldestdate[/code]Mon, 22 Oct 2012 09:54:00 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxMy 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.Mon, 22 Oct 2012 09:39:08 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxthe 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.[code]DECLARE @DeleteBeforeDate DateTime SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDateEXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate[/code]Thu, 18 Oct 2012 07:52:33 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxWhile 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.JasonThu, 18 Oct 2012 07:48:33 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxfunny and thanks but is there no other option?Thu, 18 Oct 2012 07:44:14 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspx[quote][b]durai nagarajan (10/18/2012)[/b][hr]Is there a way to delete it automatically?[/quote] Scehdule a JOB :-DThu, 18 Oct 2012 05:26:47 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxHello,Is there a way to delete it automatically?Thu, 18 Oct 2012 00:04:54 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxLowell 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!JasonWed, 17 Oct 2012 11:13:32 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxjason 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:[code] 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))[/code]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.[code]USE MSDB;GODECLARE @DeleteBeforeDate DateTime SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDateEXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate[/code]so you could do a simple DELETE:[code] DELETE FROM msdb.dbo.sysmail_allitems DELETE FROM msdb.dbo.sysmail_log [/code]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!Wed, 17 Oct 2012 11:09:37 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1373949-146-1.aspxOK, 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.Wed, 17 Oct 2012 10:54:44 GMTjasona.work