Maintenance Plan is failing

Question

Maintenance Plan is failing

Viewing 5 posts - 16 through 19 (of 19 total)

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

Oracle_91 SSChampion Points: 14548 More actions · Answer 1

Moreover, Assuming that the backup folder is not shared.

On the same server, nothing is in share , the job is running and unable to trace which other process is accessing the old backup file.

Assuming , there is single machine , on which backup is being done to hard disk, backup is verfied, delete the old backup and before deletion some process or someone else as you have said in a shared folder the backups are available and trying to take a backup on to a tape. How to do tracing using command line so that i can save that information to a text file.

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 2

Mahesh,

When you are backing up multiple databases and deleting them (after retention), it fails even if a single database in the whole list fails and you get a mail saying its failed.

In this case;

1.First check the directory where the actual bak/trn files are saved and see which of them are deleted and which of them are not deleted.

2.Right click on that particluar job and check for the history, it gives you some more info.Drill down on seeing a + sign and see thru all the steps and try to identify.

Its weird to hear that deletion of files (same job) after retention is working fine a day and failing the other day.

Maintenance plan for deletion only looks for the extension of the file .bak and .trn, unless someone rename extension or the file is in use, it should not have any problem.

TechJunky SSC Veteran Points: 256 More actions · Answer 3

Ok, so If this folder is not being shared out I do not know of a native way to monitor the data files to see where the problem is occuring...

However, you could place the below code in your SSIS package before your SQL scripts run.

I am hoping you are using a SSIS package? If so, you can add this code before your script and remove your deletion code in SQL. This will delete ALL BACKUPS in that directory however. But at least you will be able to tell if it did its job or not and if the backup procedure was the problem.

I didn't feel like using variables, but you could and then it would log the exact filename that caused the error. Hopefully you get the point. Also, the E: drive is a logical drive, not an attached storage device, correct?

'**********************************************************************************************************************

'* This script deletes any files located under E:\SQLDUMP\EDFUSION_CDI\ with a .BAK extension.

'* If the destination folder does not exist it will not work.

'* This script also writes error and information logs to the Windows Event Viewer. Look for WSH in event viewer.

'* ------------------------------------- *

'* Written by: http://www.kendallworks.net *

'* December / 2009 *

'**********************************************************************************************************************

On Error Resume Next

Const OverwriteExisting = True

Set ObjFSO = CreateObject("Scripting.FileSystemObject")

Set WshShell = WScript.CreateObject("WScript.Shell")

If ObjFSO.FileExists("E:\SQLDUMP\EDFUSION_CDI\*.BAK") Then

ObjFSO.DeleteFile "E:\SQLDUMP\EDFUSION_CDI\*.BAK"

WshShell.LogEvent 0, "E:\SQLDUMP\EDFUSION_CDI\ backup files Deleted Successfully!"

else

WshShell.LogEvent 2, "C:\E:\SQLDUMP\EDFUSION_CDI\*.BAK Does Not Exist!"

End If

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Hi,

Thanks for the script.

But i am working in SQL Server 2000 environment!

TechJunky SSC Veteran Points: 256 More actions · Answer 5

Ok, same deal. The script will work in SQL2000 or SQL2005.

However, you should be posting in the SQL2000 forum, not the SQL2005 forum. The advice for that platform would be slightly different.