Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a new DBA trying to discover how a production Reports server is setup. I did find a Maintenance Plan. It does show to be scheduled to run in a Job at 1am each day.

Here's my dilemma: When I view the history on the Maintenance Plan the Maintenance log says it has run every day starting at various times between 7am and 8am and runs for around 4-5 hours. That is probably the worst time to be running for a production Reports server. But, when I view the Job in SQL Server Agent Activity Monitor the History shows it starts around 1am and runs for 6+ hours each day. At first I thought the Maintenance log was just showing the time it was logged when the Job completed but then the durations are not the same.

Is this maintenance Plan being run twice? I am new to SQL Server 2005 (I have 2000 experience) so is there a way for the Maintenance Plan to be scheduled outside of the Job and it is running twice? Can you tell me how to verify what is going on?

share|improve this question

migrated from stackoverflow.com Feb 22 '13 at 1:12

This question came from our site for professional and enthusiast programmers.

1  
Jobs can be executed manually/ad-hoc. In the log, it should show the user who executed the job -- see who executed the ones during the day to find out if it's different than the occurrences at 1 AM. –  Jon Seigel Feb 22 '13 at 2:07
    
There are two history logs. One under Job Activity Monitor shows: the user as the Scheduler (Nightly@1am) and a duration of 6:33:33. A right click on the Maintenance Plan (MP1) under Management>Maintenance Plans I get the Maintenance Log which does not show who executed it but has 5:21:00 duration and a date 2/22/13 7:33:26 which seems to be the Job’s start time 1am+Job duration, 6:33:33. I believe now the Maintenance log is just reporting the job activity rather than re-running MP1. But, my question now is why would the duration be different: Job log-6:33:33 and Maintenance log 5:21:00? –  SQLFoenix Feb 22 '13 at 20:16
    
how many steps does the job have? maybe it does some work before running the MP? –  Justicator Feb 24 '13 at 14:20
    
It has one step. The SSIS package that is the Maintenance Plan "MP1", which has four items. Are you thinking that the Maintenance log is only showing the durations for one or a few of the items? The four items are: 1. DBCC CHECKDB WITH NO_INFOMSGS includes Indexes; 2. DBCC SHRINKDATABASE(N'DB1, 10, TRUNCATEONLY) if over 50meg; 3. There is a sql statement to Reorganize each index. Compact Large Objects is checked. 4. UPDATE STATISTICS for each table. –  SQLFoenix Feb 26 '13 at 17:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.