We recently did a few P2V moves of SQL Servers. which resulted in some MSDB problems.

Here’s what BOL says about MSDB

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

http://technet.microsoft.com/en-us/library/ms187112.aspx

So when moving MSDB you’ll want to check that all these still work!
 
SQL Server Agent:

After restarting your machine, check the SQL Server Agent Error Log

We had a few with this

MSDBmove1

The subsystem dll are pointing to the wrong place. You can check where they are pointing via

SELECT * FROM msdb.dbo.syssubsystems

If this happens to your machine, you will see errors on any Agent job that runs using anything but T-SQL.

So we had errors like:

MSDBmove3

Showing a CMDEXEC job failed

MSDBmove2

Showing a SSIS job failed

There’s a few articles out there on how to fix this, but the easiest and my favorite is to delete the current settings in msdb.dbo.syssubsystems and then run the sp_verify_subsystems, which will auto correct all your settings.

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems

REMEMBER TO RESTART YOUR SQL SERVER AGENT AFTER RUNNING!

The other option is update them yourself via:
http://support.microsoft.com/kb/903205
http://www.mssqltips.com/sqlservertip/2488/sql-agent-error-the-cmdexec-subsystem-failed-to-load/
 
Database Mail & Service Broker

These go hand in hand, as if you don’t have Service broker enabled for MSDB, you wont have Databse Mail!

First check to see if Service Broker is enabled for MSDB.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

If you get a 0 Service Broker isn’t enabled and Database Mail wont work.

We had jobs that tried to use Database Mail and throw out errors that looked like the database that the agent job ran against had a Service Broker issue when it was actually MSDB!

 

MSDBmove4

 

If you look at overall error of the job you got something a little more helpful, (It at least points you to check database mail and not service broker

 

MSDBmove5

 

To enable Service broker on MSDB:

alter database msdb
set single_user
WITH ROLLBACK IMMEDIATE;
alter database msdb
SET ENABLE_BROKER;
alter database msdb
set multi_user
WITH ROLLBACK IMMEDIATE;

Test your email, if it fails follow the normal database mail troubleshooting methods:
http://technet.microsoft.com/en-us/library/ms189959(v=sql.105).aspx

Advertisements