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

Maintenance Plan Anomaly Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 1:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 17, 2011 1:56 AM
Points: 52, Visits: 59
I have an interesting anomaly in a SQL 2005 maintenance plan that I was hoping forum members might be able to provide some insight into. This job was set up by another DBA, whom I am current relieving while he is on short-term disability. The plan itself backs up the system databases (master, msdb and model) on a nightly basis at 12:30AM, with a sub-directory for each database.

The steps of the maintenance plan are as follows:

• Cleanup Report – Files (older than two weeks)
• History Cleanup Task (Backup, Job, Maintenance Plan older than four weeks)
• Back Up Database Task
• Maintenance Cleanup (delete any backup files on master, model or msdb more than three days old)

The maintenance plan appears to run without any errors but I am not seeing any of these backups in any of the sub-directories. The only backup there is the original one that was initially made.

The job “View History” reports that these jobs are running successfully each night. The database properties for each database report only the initial backup date for these databases, not the most recent reported in the history log. Running the job manually reports success, the history log is updated to report this, but no change is made in the database properties, nor does it appear that any backup is made (no backup file is created in the sub-directory). Manually backing up the system database will update the properties as well as creating a backup file.

I’m assuming that this history log is deriving this data from the event log, which is recording these transactions. There are no records being made to the msdb.backupset table.

A separate maintenance plan backing up a production database is working correctly, creating backup files, writing to msdb.backupset, as well as the log and updating the database properties.

The simplest thing would be to just drop and recreate the maintenance plan but I’m curious whether anyone can throw any light on what might be happening here.

Thanks in advance.

USKiwi



Post #993962
Posted Monday, September 27, 2010 3:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 PM
Points: 2,834, Visits: 8,544
USKiwi (9/27/2010)

The job “View History” reports that these jobs are running successfully each night. The database properties for each database report only the initial backup date for these databases, not the most recent reported in the history log. Running the job manually reports success, the history log is updated to report this, but no change is made in the database properties, nor does it appear that any backup is made (no backup file is created in the sub-directory). Manually backing up the system database will update the properties as well as creating a backup file.


Just because a job says it completed normally, doesn't mean it did.

I just found that a vendor's maintenance plan they set up for backups was actually failing.
The job was set to "QUIT THE JOB REPORTING SUCCESS" when it failed ....
So there were no backups !

So take a closer look at everything. Screen by screen, tab by tab.



Post #994062
Posted Tuesday, September 28, 2010 12:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 17, 2011 1:56 AM
Points: 52, Visits: 59
There is only one step which launches an SSIS maintenance plan. There not any steps beyond this. The job reports an error on failure and success upon success. I can only assume that the job thinks it succeeds however the absence of database backup files indicates otherwise. I guess the next step would be to start digging into the operation of the maintenance plan itself. Might just be simpler to drop and create the plan.


Post #994735
Posted Tuesday, September 28, 2010 1:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 PM
Points: 2,834, Visits: 8,544
Is logging enabled in the Maint Plan. If so, what's in the .txt file after it runs ?

Edit: The .txt should list the steps it actually performed.



Post #994775
Posted Tuesday, September 28, 2010 7:46 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 3:14 PM
Points: 4,363, Visits: 9,545
Verify the connection string for the maintenance plan - do you have another instance of SQL Server that this plan could be accessing and running the backups on?



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #994889
Posted Monday, October 4, 2010 1:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 17, 2011 1:56 AM
Points: 52, Visits: 59
Greetings Homebrew01:

This is what the log states for the job:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4285
Report was generated on "CHIVMC01".
Maintenance Plan: Full backup of system databases
Duration: 00:00:00
Status: Succeeded.
Details:

Interestingly, there are no details provided.

USKiwi



Post #997823
Posted Monday, October 4, 2010 1:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 17, 2011 1:56 AM
Points: 52, Visits: 59
Greetings Jeffrey:

Checked the connection string. It appears correct. Also checked the services. There are not any other instances of SQL Server on this box.

Thanks.

USKiwi



Post #997833
Posted Monday, October 4, 2010 1:40 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 3:14 PM
Points: 4,363, Visits: 9,545
Okay - how was the agent job created? Did you create the agent job manually, or did you setup the schedule in the maintenance plan and set it up that way?

If you created the agent job manually, the command string is probably not correct. By default, the maintenance plans in the SSIS package are disabled - and have to be enabled to run.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #997839
Posted Wednesday, October 6, 2010 8:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 17, 2011 1:56 AM
Points: 52, Visits: 59
Greetings Jeffery:

The job in question was created through a maintenance plan generated through the SSIS. The “generated” T-SQL for the backup process from SSIS is:

EXECUTE master.dbo.xp_create_subdir N'F:\MSSQL\MSSQL.1\MSSQL\Backup\master'
GO
EXECUTE master.dbo.xp_create_subdir N'F:\MSSQL\MSSQL.1\MSSQL\Backup\model'
GO
EXECUTE master.dbo.xp_create_subdir N'F:\MSSQL\MSSQL.1\MSSQL\Backup\msdb'
GO
BACKUP DATABASE [master] TO DISK = N'F:\MSSQL\MSSQL.1\MSSQL\Backup\master\master_backup_2010_10_06_092757_4477669.bak' WITH NOFORMAT, NOINIT, NAME = N'master_backup_2010_10_06_092757_4477669', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N'F:\MSSQL\MSSQL.1\MSSQL\Backup\model\model_backup_2010_10_06_092757_4477669.bak' WITH NOFORMAT, NOINIT, NAME = N'model_backup_2010_10_06_092757_4477669', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N'F:\MSSQL\MSSQL.1\MSSQL\Backup\msdb\msdb_backup_2010_10_06_092757_4633920.bak' WITH NOFORMAT, NOINIT, NAME = N'msdb_backup_2010_10_06_092757_4633920', SKIP, REWIND, NOUNLOAD, STATS = 10

I am deriving the connection string from the “Data sources” tab from the “Edit” function of the “Steps” selection from the Job Properties. I don’t know if there is a better way to determine this information. The connection string provides as follows:

“server=’CHIVMC01’;Trusted_Connection=true;Application Name=’Microsoft SQL Server Management Studio’;Packet Size=4096;multipleactiveresultsets=false;

The logs report that the job is completing successfully. Nothing is being written to msdb.dbo.backupset table.

The simplest thing would be to drop and recreate the maintenance plan and see if that corrects the problem. The daily backing up the system dbs for this box is not critical, however, I would like to understand what might be happening here because if it can happen here it can happen elsewhere and the fact the job reports success when the job is not succeeding could be a cause for problems. Fortunately the problem can be detected by querying the backupset table, however I’d rather not have to rely upon that to detect this problem.

Regards,

USKiwi



Post #999420
Posted Wednesday, October 6, 2010 10:20 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 3:14 PM
Points: 4,363, Visits: 9,545
Okay, I want to make sure I am understanding here.

Did you use SSMS to create the maintenance plan?
Did you use the maintenance plan schedule to schedule and create the agent jobs?

If so, then open the maintenance plan in SSMS. At the top, you will see a button to manage connections. Validate that the connection setup is correct for that server.

Next, from the agent job - validate the command line that actually runs the package. For maintenance plans, it looks like:

/SQL "Maintenance Plans\{your plan}" /SERVER {your server} /CHECKPOINTING OFF /SET "\Package\{sub-plan}.Disable";false /REPORTING E

The next step would be to validate that each task in the sub plan is enabled, and that the dependencies are set correctly. In other words, in a normal backup plan I would have an integrity check task - with a green line (on success) from that task to the next task (backup databases task), etc...

If all of that is correct - then recreating the plan is the only option. It is possible the plan was created pre-SP2 and never recreated later. Any maintenance plans that were created before SP2 came out have to be recreated because of the changes made at that time. It is also possible that someone with a pre-SP2 client created the plan - which is not compatible with post-SP2 maintenance plans.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #999580
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse