?
Solved

Maintenace Plan-T-log backup failed

Posted on 2010-03-24
23
Medium Priority
?
863 Views
Last Modified: 2012-05-09
Hi,

We have two different maintenance plans scheduled to take t-log backup for two different sets of DB's.Both the jobs are scheduled to run every 15 min's.
But these jobs are failing very often almost at same time.

Found following error messages in

SQL Server Log:
Error: Maintenance plan 'All ad-hoc plans'.  B:\SQL_Backup\Backup\<t-log filr name>.TRN ...

Windows Event Log:
Error: 60000 Severity: 10 State: 1 Error: Maintenance plan 'All ad-hoc plans'. ...

Could some one let me know the reason behind this..and how can i solve this issue.

Thanks,
0
Comment
Question by:parpaa
  • 10
  • 8
  • 5
23 Comments
 
LVL 7

Expert Comment

by:wittyslogan
ID: 28455463
have you looked in maintenance plan history?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 800 total points
ID: 28458911
Right click your Maintenance plan and click View history which would give you the detailed explanation of your error..
Posting it would help..
0
 

Author Comment

by:parpaa
ID: 28461035
Yes i did,

The backup step is successful for both the jobs but the Cleanup step is getting failed causing the jobs to get failed.

fyi
The cleanup step is set on two jobs but specified common folder. My understanding is that when both cleanup jobs starting at same time causing the step to fail. am i right?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 800 total points
ID: 28461143
No.. Kindly check whether the folders specified in Cleanup task are correct or not..
if correct, then check whether you have appropriate permissions to those folders or not.
0
 

Author Comment

by:parpaa
ID: 28461771
yes we do.

Both cleanup steps in two different jobs point to same common folder "Backup". If there are no right permissions to this folder why the job fails some times and run suscessfully most of the time.

fyi
The backup folders are all present in this common folder so this is set as common in two clean up steps.

Thanks,
0
 
LVL 7

Assisted Solution

by:wittyslogan
wittyslogan earned 1200 total points
ID: 28461927
are the backups date/time stamped?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28461955
>> If there are no right permissions to this folder why the job fails some times and run suscessfully most of the time.

Do you have two ways of executing that plan..
Any password expirations or reset like that.
0
 

Author Comment

by:parpaa
ID: 28462369
Sorry, I'm not clear with your quetsion.

Thanks
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28462641
Are you executing Maintenance plan only using the scheduled way..
Also check the password expiration policy of the user account under which this plan executes.

Also make whether the path is available all the time if it is a network path or shared path..
0
 
LVL 7

Assisted Solution

by:wittyslogan
wittyslogan earned 1200 total points
ID: 28462738
is that for wittyslogan or rrjegan17?
I'm asking about timestamps just incase the 2 jobs ARE trying to delete same file at the same time.
Have you run profiler by the way to see where the job fails exactly?
0
 

Author Comment

by:parpaa
ID: 28463012
Are you executing Maintenance plan only using the scheduled way..
  >> Yes it is scheduled to run every 15 min.

Also check the password expiration policy of the user account under which this plan executes.
  >> No, there isn't any password policy set have sys admin privileges.

Also make whether the path is available all the time if it is a network path or shared path
  >> It is local drive, not a share.
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 28463360
what is the exact error that you see in maintenance plan history?
0
 

Author Comment

by:parpaa
ID: 28463639
wittyslogan,

Yes it looks the jobs are failing when the cleanup step start same time, timestamp as below when two jobs failed,

Job1:: 3/24/2010 3:31:15 AM
Job2:: 3/24/2010 3:31:07 AM

Have you run profiler by the way to see where the job fails exactly?
I'm not sure that running profiler will give information on this issue.

Thanks,
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28463882
>> Yes it looks the jobs are failing when the cleanup step start same time, timestamp as below when two jobs failed

There should not be any issue in having both tasks running at the same time..
0
 

Author Comment

by:parpaa
ID: 28463977
wittyslogan,

are the backups date/time stamped?
 >>yes

Thanks,
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 28464004

When I mentioned the time stamp I meant do the files get given a timestemp eg. backup_file_db_230310170134000.trn?

Also, is it not worth having a go with profiler since it does write sql statements and we can then at least rule it out as a help in this case?

Also there is probably a crossover as we are all typing quite fast but do you have the error from maintenance plan history
Cheers
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 28464109
are the backups being cleaned up?
0
 

Author Comment

by:parpaa
ID: 28464526
wittyslogan,

Yes we have the same timestamp setting to the backup file.

are the backups being cleaned up?
  >> Yes they are getting cleaned up in the next successful run.

Thanks :)
0
 
LVL 7

Assisted Solution

by:wittyslogan
wittyslogan earned 1200 total points
ID: 28464691
but do you have to error from the maintenance plan history?
0
 

Author Comment

by:parpaa
ID: 28464721
I have a question here,

In the cleanup task window there is one check box option set "Include first-level subfolders" in File Location tab, What does it exactly refer to?


0
 
LVL 7

Assisted Solution

by:wittyslogan
wittyslogan earned 1200 total points
ID: 28465014
what this means is that if you have a folder called "backups" and in you have the folders for the backups of databases 1 to 3 then it will go in and clean up the subfolders 1 to 3 without you having to actually write clean up 1 to 3.

If this isn't ticked then it will only clean up the backups in the folder "backups" and won't look in any subfolders.
0
 

Author Comment

by:parpaa
ID: 28466407
wittyslogan,

We created this Maintenance Plan using Quest Litespeed, So it show up with following error, I shld have mention this earlier. I'm sorry about that.

There are error messages in it. It is beacus of the two clean up steps as i think,

It says in the error message
Error delete file: "\\Backup\\temp_TLog_201003200330.TRN". Error: "The system cannot find the file specified"

Found same message in other maintenance job history as well.

Thanks
0
 

Author Comment

by:parpaa
ID: 28466653
Thanks a lot guys for your support.

Cheers :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question