This should be the common issue faced by many of us where the database mail is used to send bulk mails on a regular basis. We have a database server which is specifically used for sending weekly newsletters to all the subscribed users. The number of subscriptions increased over a period of time and so as the number of newsletters. One day the disk drive, holding the system databases, started alerting for the low disk space. On investigation, I found that the msdb database has grown to 7 GB in size!
As we know that msdb maintains various types of history information like backup history, jobs history, sent mails and attachments history etc. Now the question is to find out the table(s) holding the largest data. I have found the following query from the Net which helped me to find out the name of the tables in descending order of space consumed on the disk.
(As the above issue had already been fixed, I can not show you the 7 GB grown msdb. For this demo I am running this query on a server having 1.5 GB grown msdb)
use msdb go sp_helpdb msdb
--Query 1 - Checking the size of tables in a database select object_name(i.object_id) as ObjectName, i.[name] as IndexName, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB from sys.indexes i inner join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id inner join sys.allocation_units a on p.partition_id = a.container_id group by i.object_id, i.index_id, i.[name] order by sum(a.total_pages) desc, object_name(i.object_id) go
We can see that sysmail_mailitems table is occupying most of the space on the disk, around 1.3 GB spanning across 171812 pages. This table stores the database mail messages which are sent, unsent, retrying, and failed. Microsoft recommends maintaining this table by periodically deleting the messages based on the organizations document retention program. The sysmail_delete_mailitems_sp procedure located in the msdb database permanently deletes e-mail messages from this table. The syntax of the procedure is shown below
sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ] [ , [ @sent_status = ] 'sent_status' ]
The date option allows deleting e-mails before the specified date and the status option allows deleting e-mails of a particular type i.e. sent, unsent, retrying, and failed.
As deleting a huge amount of records in one shot takes a good amount of time we can purge the data in small batches. The below query will identify since when the data is lying in the table. This is especially useful when the data is piled up over a long period of time.
--Query 2 - Checking the records by year, month select datepart(yy,sent_date) [Year], datepart(mm,sent_date) [Month], count(*) 'NoOfRows' from sysmail_mailitems group by datepart(yy,sent_date), datepart(mm,sent_date) order by 1,2
The table has data for two months. Let’s keep the data for March and deleted the February data. The following query will do the job,
--Query 3 – Purging the data before 1st March 2011 DECLARE @DelDate nvarchar(20) ; SET @DelDate = '03/01/2011' EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DelDate;
Now run the Query 1 and check the output. The total table space which was 1372 MB has come down to 623 MB and also the Total Pages from 171812 to 79836.
Let’s check the database size using,
sp_helpdb msdb
No luck???!!! In fact the log size has increased. Don’t worry, now we need to truncate the log and shrink the database so that the free space will be release to the operating system. Run the following code and recheck the database size.
--Query 4 - Truncate the log and shrink the database checkpoint backup log msdb with truncate_only dbcc shrinkdatabase ('msdb') sp_helpdb msdb
It worked...
Hi AmitK,
Great article. In addition to whatever written by you always keep in mind two main points.
Thanks,
Sarabpreet Singh
I would rather recommend keeping the msdb in Simple recovery mode which will keep log space in check .
Also isnt "backup log with truncate_only" no longer supported with the version SQL Server 2008 and ahead ?
Hi Sarab and Sachin, Thanks for the review.
Sarab - I had gone through both of your articles and what you said is correct. But can you please suggest a quick option to gain the empty space. Means suppose you have provision to for say only 2 GB space for msdb and it has grown to 7 gb due to lack of monitoring. Also if I take the log backup to disk then will it reduce the actual database size? One interesting issue I was reported by one of my colleagues is that the environment is having log shipping set and the backups are happening after every 15 min but still the log size grows when some activity is happening in the database. Ideally the size should reduce once the log backup is done.
Sachin - Yes, if we do not want any point-in-time recovery then simple recovery model should be ideal.
Is the log size getting big for msdb only ?
To keep the database size in check schedule a differential backup and then have a job that deletes the records from your BIG table.
Also I am not sure why would you want a point in time recovery for msdb ? Thats the reason I suggested you to keep it in Simple Recovery Model.
The log shipped databases which I was talking about are user databases on production and hence having Full Recovery model set. We are hainvg daily full backup and due to log shipping a log backup after every 15 min. Let me explain,
If the initial size of the transaction log is 20 MB. Due to some activity it has increatsed to 40 MB. Now once the log backup is done, will it again reduce to 20 MB...
The size wont be reduced because log truncation DOES NOT do a "shrinking" it does a "truncation".Understand the difference between these two.
The only thing that will reduce the size is always going to be a DBCC shrinkfile command.Truncation makes the state of reusable part of the VLF to active.
So in your case if your log size is growing then shrink the log file ONLY ONCE at the first then create a backup strategy which will backup only data say every 15 minutes.If shrinking reduces the log file to say 15MB then the log backup's will make sure that the physical size do remain under 15 MB.
Seems like I will have to post an article or a blog on this one...
Sachin.Nandanwar said:
Sarabpreet -> Yes, Sachin this is no longer supported but there are other ways also to truncate the log file.
AmitK said:
But can you please suggest a quick option to gain the empty space. Means suppose you have provision to for say only 2 GB space for msdb and it has grown to 7 gb due to lack of monitoring.
Sarabpreet -> If there were no monitoring checks on the log file and the log file has already taken so much disk space, then truncating and shrinking is the only option if you want to reclaim the log space. But the bottom line is, it should not be a regular practice. Avoid using it regularly rather schedule T.Log backup for such a DB. (applies to all DBs)
AmitK said:Also if I take the log backup to disk then will it reduce the actual database size?
Sarabpreet -> NO
AmitK said: One interesting issue I was reported by one of my colleagues is that the environment is having log shipping set and the backups are happening after every 15 min but still the log size grows when some activity is happening in the database. Ideally the size should reduce once the log backup is done.
Sarabpreet -> NO, it will never reduce the log file size, Infact it will only make the backedup VLFs reusable which prevents the log file growth for some time.
Sachin.Nandanwar said:To keep the database size in check schedule a differential backup and then have a job that deletes the records from your BIG table.
Sarabpreet -> WRONG, Differential backup can never restrict the growth of your DB or log file in specific. How can you suggest deleting records from a Table without knowing the retention policy of the Data. This should be done only and only through a Purging Policy duly approved by Business & tested in UAT.
Sachin.Nandanwar said: Also I am not sure why would you want a point in time recovery for msdb ? Thats the reason I suggested you to keep it in Simple Recovery Model.
Sarabpreet -> I don’t want to comment on this peace, this is on the sole discretion of the DBA\Business, there might be some Policy\SLA forcing an Individual(DBA) to change the recovery model to FULL.
AmitK said: The log shipped databases which I was talking about are user databases on production and hence having Full Recovery model set. We are having daily full backup and due to log shipping a log backup after every 15 min. Let me explain, If the initial size of the transaction log is 20 MB. Due to some activity it has increatsed to 40 MB. Now once the log backup is done, will it again reduce to 20 MB...
Sarabpreet -> No, Log backup will never shrink the Log File.
Sachin.Nandanwar said: So in your case if your log size is growing then shrink the log file ONLY ONCE at the first then create a backup strategy which will backup only data say every 15 minutes.If shrinking reduces the log file to say 15MB then the log backup's will make sure that the physical size do remain under 15 MB.
Sarabpreet -> I would suggest to schedule the log backups as per the Backup & Recovery Strategy to maintain the UpTime, and observe it for next 24Hrs Window, Log Backup will try to maintain the same size (over a long run) provided the workload remains the same on the DB and the Log backups are taking backup at a scheduled interval.
Sarab,Pls read properly the whole comment thread before commenting on my comments.Where did I say differential backup should be used to restrict growth of log or DB.Differential backup was the means by which the OP could have minimum data loss in case of point in time restore if he keeps the recovery model SIMPLE which I had suggested him earlier.Also didnt I say to take a differetial backup before deleting the records ?What's so BIG WRONG in that ? Please provide more insight on your claims with significant example.Also it is FOOLISH overall to keep any system databases EXCEPT master database in FULL Recovery Model.
Read this carefully.
I don't think i need to explain anything else after this.
Running a backup log with truncate_only feature can still be achieved by changing the recovery model to simple.
Changing the recovery model to simple is as good as running truncate_only/no_log options with log backup.
So, just a command has been discarded to ensure that nobody accidently break the log backup chain.
I have seen DBAs using such a bad command on routine basis to truncate the logs hence to reclaim disk space via dbcc shrinkfile().
But there is one another nasty way of breaking the chain which is still possible in sql server 2008.
Backup log <dbname> with disk ='NULL'
Realy Nasty !!
Leave a comment