SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plan Anomaly


Maintenance Plan Anomaly

Author
Message
USKiwi
USKiwi
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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



homebrew01
homebrew01
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19992 Visits: 9222
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
USKiwi
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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.



homebrew01
homebrew01
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19992 Visits: 9222
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 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31066 Visits: 10091
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 opportunities brilliantly disguised as insurmountable obstacles.

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

USKiwi
USKiwi
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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



USKiwi
USKiwi
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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



Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31066 Visits: 10091
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 opportunities brilliantly disguised as insurmountable obstacles.

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

USKiwi
USKiwi
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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



Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31066 Visits: 10091
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 opportunities brilliantly disguised as insurmountable obstacles.

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search