SQL Server 2005 Maintain Plan and scheduled job

Question

Post reply

SQL Server 2005 Maintain Plan and scheduled job

Judy Why

Hall of Fame

Points: 3656
More actions
March 2, 2010 at 6:15 am

#156732

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736255

March 2, 2010 at 7:14 am

#1127062

Do you have the logs of the maintenance plan?

Judy Why Hall of Fame Points: 3656 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

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.

Judy Why Hall of Fame Points: 3656 More actions · Answer 4

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

Judy Why Hall of Fame Points: 3656 More actions · Answer 5

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?

Judy Why Hall of Fame Points: 3656 More actions · Answer 6

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

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