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

script: List the permissions of a user

I had a  task where I had to list all the permission of a specific user on the database . The following script came to the rescue


SELECT dppriper USER_NAME(dppriper.grantee_principal_id) AS [UserName], .type_desc AS principal_type_desc, .class_desc, OBJECT_NAME (dppriper.major_id) AS object_name, .permission_name, .state_desc AS permission_state_desc
FROM sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE
--dppriper.permission_name = 'EXECUTE'
USER_NAME(dppriper.grantee_principal_id) = 'myuser'

 --**replace the myuser with the user we are looking for
--**If we want to find only the stored procedures that the user has perission on comment out the  clause in the above query.

The size of the message body in dbmail

SQL 2008 can take more than 8000 characters in the meassage body of dbmail . While declaring the message body variable just set the size of the varchar to max.
For .e.g.

declare @msg_body varchar(max),           
 @v_subject


This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)