Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

If MSDB is in Recovery, will SQL Agent Jobs still execute? Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 8:16 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 752, Visits: 5,531
This morning I came in, and it seems that my boss had power cycled one of our SQL Servers this morning because certain applications with SQL backends "weren't working." Since then, MSDB has been in recovery, and according to the SQL error.log, it's going to be around another 12hrs before it finishes (MSDB is about 170GB, mostly because of a never cleaned out sysmail_Mailitems table.)

My question is, while MSDB is in recovery, will any scheduled Agent Jobs run, or is the Agent down until MSDB finishes recovery?

As for why sysmail_mailitems was never cleaned out (it's got entries dating back to 2007 or so,) it's because we didn't realize DB Mail kept entries like that (accidental DBA) and just found out last week (when I was trying to figure out WHY MSDB was so large.) I had started a Job to purge sysmail (using the built-in SP,) which may have been consuming enough resources to slow the whole thing down, and caused this (I'm going to resetup the Job when I can, and have it do one year, then re-run the next night doing the next year, and so on.)

Thanks,
Jason A.
Post #1363511
Posted Monday, September 24, 2012 8:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
Agent will be down until MSDB is back online. All the job definitions, schedules, history, etc are in MSDB.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363542
Posted Monday, September 24, 2012 8:49 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 752, Visits: 5,531
Thank you Gail! That's what I was thinking would be the case, but couldn't find anything to confirm it with a quick Google...

Jason
Post #1363545
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse