SQL Server 2005 Maintain Plan and scheduled job

  • Hi,

    here is my problem. I have one backup database maintain plan, scheduled to run 200am. Because of bussiness requirement, we need to disable this backup. So I went to Job Activity Monitor to find this maintain plan job and disabled it.

    But weird thing happend. when I checked jobs next day, from Job Activity Monitor, it shows it didn't run. but from Maintenance Plans--> View History, it did run and backed database at 200AM.

    Could anybody explain this to me?

    Judy

  • Do you have the logs of the maintenance plan?

  • I don't know. I just viewed the maintainence history and found that maintainance plan still backed up database.

  • If you check the package, you can see if it's set to log to a text file (it should be).

    The other thing I'd be sure of is if you really disabled the job. It's possible that a mistake was made there.

  • I didn't set up to save txt file logs.

    I have deleted backup Database Maintainence Plan and can't reproduce it.

    But I have another example. When I disabled backup job, I also disabled Integrity Check job using the same way.

    Integrity Check Maintainence Plan was scheduled at 100am every day.

    Yesterday, I disabled scheduled job from job activity monitor around 200PM 2010-03-01 in my time.

    You can check the following output to see if I am wrong.

    select * from dbo.sysmaintplan_subplans

    Subplan_name: 70DF4CA4-B40E-45C2-8CFB-5CD692A9B33A

    Subplan_ Description: Integritycheck

    Plan_ID: 3633172C-BD21-4C9D-A0E6-19C031312CD8

    Job_Id: C90F371F-C6D8-483E-82B7-B012F301889D

    select * from dbo.sysmaintplan_log

    where plan_ID='3633172C-BD21-4C9D-A0E6-19C031312CD8'

    order by start_time desc

    Plan_id: 3633172C-BD21-4C9D-A0E6-19C031312CD8

    Subplan_ID: 70DF4CA4-B40E-45C2-8CFB-5CD692A9B33A

    Start_time: 2010-03-02 01:00:02.637

    End_time:2010-03-02 01:01:01.010

    select * from dbo.sysjobactivity where job_id='C90F371F-C6D8-483E-82B7-B012F301889D'

    job_id: C90F371F-C6D8-483E-82B7-B012F301889D

    run_requested_date: 2010-03-01 01:00:00.000

    run_requested_source:1

    start_execution_date: 2010-03-01 01:00:00.000

    last_executed_step_id:1

    last_executed_step_date: 2010-03-01 01:00:01.000

    stop_executed_date:2010-03-01 01:00:08.000

    job_history_id:492

    next_scheduled_run_date:2010-03-02 01:00:00.000

  • Hi,

    Can anybody shed a light for me?

    Yesterday, I deleted Maintainence plan for backup database including scheduled job.

    And I disabled Integrity Check in scheduled job and removed Scheduled time on maintainence plan for it.

    From maintainence history and job history, I can't find log for these two.

    Today, 1:00am DBCC checkDB ran again. 2:00am backup database ran again.

    Here is from SQL Log

    Date3/03/2010 2:00:29 AM

    LogSQL Server (Current - 3/03/2010 7:47:00 AM)

    SourceBackup

    Message

    Database backed up. Database: TestInstance, creation date(time): 2009/12/07(16:05:25), pages dumped: 88620, first LSN: 4200:23589:37, last LSN: 4200:23614:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TestInstance_backup_201003030200.bak'}). This is an informational message only. No user action is required.

    Who issue backup?

  • I found the reason.

    Our IT people cloned this server and built another brand new server. So all the server setting on new server is pointing to this server, including maintainence plan and scheduled job.

    All the scheduled jobs run at the new server but against old sql server.

    Judy

  • Thanks for the update. That is an interesting behavior, makes me wonder how they are building connection strings in the plans.

  • Steve Jones - Editor (3/3/2010)


    Thanks for the update. That is an interesting behavior, makes me wonder how they are building connection strings in the plans.

    Since maintenance plans are really just SSIS packages, they use the same connection manager as a normal SSIS package. When you create the package - the instance name is saved as the Local Connection (note: you can't change this in SSMS and you can't modify all tasks to use a different connection).

    So, when you clone a system or restore MSDB or export/import the maintenance plan the Local Connection information is copied also which means that plan will now try to connect to your old instance.

    It is interesting that you can build maintenance plans on ServerA that will actually run on ServerB without setting up ServerA as a master - or ServerB as a target. I haven't tried it, but you probably could setup a new connection and use that new connection instead of the default local connection for all tasks. Not sure it would work though...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply