MSDB too large

C drive was growing very fast and then saw that the msdb  data file and log file was huge.

I shrank the msdb log file first.
I ran the following query to see the top higly populated table

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

Once running the query on MSDB , I found the follwing large tables

I ran the follwing to clear the history

use msdb
GO
exec sp_maintplan_delete_log @oldest_time = '05/12/2017'


This expands the log file so make sure you shrink file


DBCC SHRINKFILE(MSDBLog, 50) --50MB

GO


Then the following maintenance plan was scheduled to run every week.

To check the last successful run date and time of the sql job

Declare @jobId as uniqueidentifier

select

j.job_id,j.Name as "Job Name", j.description as "Job Description", CONVERT(DATETIME, RTRIM(h.run_date))

+ ((h.run_time / 10000 * 3600)

+ ((h.run_time % 10000) / 100 * 60)

+ (h.run_time % 10000) % 100) / (86399.9964) AS run_datetime,

case h.run_status

when 0 then 'Failed'

when 1 then 'Successful'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as JobStatus

from sysJobHistory h, sysJobs j

where j.job_id = h.job_id and h.run_date =

(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id ) and h.run_status= 1

and cast(cast(h.run_date as varchar(8)) as date) = cast('06/27/2016' as date)

order by 2

SQL Agent Job: Update the notification operator

I found many SQL Agent job were enabled but no any  notifications setup in case of failure.

I ran the following

use msdb
GO
select * from dbo.sysjobs

where notify_level_email = 0

and enabled = 1

--- gives the list of jobs enabled but no notification set up.

SELECT [ID], [Name], [Enabled]

FROM MSDB.dbo.sysoperators

WHERE [Enabled] = 1

ORDER BY [Name];
Go

-- Gives the list of operators. Pick the Id of the one you want to notify to use in the below script








UPDATE S

SET S.[notify_level_email] = 2,

S.[notify_email_operator_id] = 2   ---my operator

FROM MSDB.dbo.sysjobs S

WHERE S.[Notify_Level_Email] = 0

AND S.[Enabled] = 1;




GO

script: Change the owner of the sql jobs

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs 

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END 

CLOSE My_Cursor

DEALLOCATE My_Cursor

restore msdb database

USE master
GO
--just to verify if the below command succeeds the restore should succeed too
RESTORE VERIFYONLY
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
--restore the database
RESTORE DATABASE [msdb]
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
WITH REPLACE
GO
--msdb database was curripted and when to suspect mode. So fixed it by restoring a copy of a backup

Permission to run a specific SQL job to a user

I added a user to the msdb database and gave him the SQLAgentUserRole but that gave him access to all the jobs, which is not what I wanted. This is the work around I followed....


USE msdb
GO
CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
-- Executes as the database owner, for msdb that is 'sa'
WITH EXECUTE AS OWNER
AS
-- Hardcoding the job name limits this procedure's control
EXEC dbo.sp_start_job @job_name = 'MyProductionJobName'
GO
-- Grant Execute rights to your selected users
GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
GO


This way the user will not have direct access to the job MyProductionJobName but through this wrapper sp it can run the job.

Resending the failed mails in DBmail

We had several failed mail items that were supposed to be sent out overnight by some SQL Jobs.
The following query was run to find the failed items

use msdb
GO

declare @StartDate datetime,
            @EndDate datetime


select * from dbo.sysmail_faileditems where last_mod_date >=@StartDate  and last_mod_date < @EndDate

--all the returned rows had sent_status as failed.

Here is a very cool script that will resend the failed items that I found in sqlservercentral.com. It worked like a charm!!!

declare @mailitem_id int, @sendmailxml
varchar(max), @rc
int,@StartDate datetime, @EndDate
datetime-- get the dates to use in the queryset @StartDate = convert(datetime, convert(char(8), GetDate(), 112))set @EndDate = @StartDate + 1declare cFailedMail cursor forSELECT
mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDateopen cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0
begin
-- Create the primary SSB xml maessage
SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'-- Send the send request on queue.EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc <> 0BEGIN
 RAISERROR(14627, 16, 1, @rc, 'send mail')END

fetch next from cFailedMail into @mailitem_idend
close cFailedMail
deallocate cFailedMail

Database Mail Executable Minimum Lifetime

We are facing some issue with Sql Server 2008 R2 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time and we get dead locks alert

I adjusted the database mail executable minimum lifetime ( in seconds ) from 600 to 10 and this problem seems to have dissapear.

Databasemail object on SSMS --------> Configure databasemail ----------------->View or change system parameters--------->Database mail executable minimum lifetime change it to 10 seconds





.

Cleaning up of msdb database

Cleaning up msdb database is one of the neglected yet most important task of a sql administrator. We have some SSIS packages, full back up once a day and transactional backup in every 15 minutes. We use databaseMail extensively therefore cleaning up msdb database is a good practice.

The msdb database was 241 MB and after cleaning up ( running the following scripts) I am able to bring it down to 153 MB. I am keeping a log of the database growth in a table in every 4 hours. I do not think that the size of the msdb has to be this big. So I am leaving it the way it is and then looking at table where the file growth is captured  befor I make the decision if I need to rsize the db to a smaller size.

The scripts that I ran

--removing anything before a month

DECLARE
SET @date = DATEADD(day, -30, CURRENT_TIMESTAMP)
 --delete backup history

--delete old mail items
--especially, if you are sending attachements
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date
--delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date
--delete the SQL Server agent job history log
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date
EXEC msdb.dbo.sp_delete_backuphistory @date @date datetime