March 2, 2010 at 6:15 am
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
March 2, 2010 at 7:14 am
Do you have the logs of the maintenance plan?
March 2, 2010 at 7:19 am
I don't know. I just viewed the maintainence history and found that maintainance plan still backed up database.
March 2, 2010 at 8:11 am
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.
March 2, 2010 at 9:36 am
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
March 3, 2010 at 6:08 am
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?
March 3, 2010 at 7:46 am
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
March 3, 2010 at 8:13 am
Thanks for the update. That is an interesting behavior, makes me wonder how they are building connection strings in the plans.
March 3, 2010 at 9:49 am
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