Maintenance Plan Anomaly

Question

Maintenance Plan Anomaly

USKiwi

SSChasing Mays

Points: 604
More actions
September 27, 2010 at 1:28 pm

#227395

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

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

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 .... :w00t:

So there were no backups !

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

USKiwi SSChasing Mays Points: 604 More actions · Answer 2

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

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.

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 4

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
“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

USKiwi SSChasing Mays Points: 604 More actions · Answer 5

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

USKiwi SSChasing Mays Points: 604 More actions · Answer 6

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

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 7

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
“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

USKiwi SSChasing Mays Points: 604 More actions · Answer 8

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

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 9

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
“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