Sql server db maintenance plan: Failed

Question

Post reply

Sql server db maintenance plan: Failed

Jaishree

Old Hand

Points: 365
More actions
March 11, 2004 at 2:31 am

#162064

Hi to all,

I am basically a VB programmer but sometimes I have to do DBA tasks also.
I have little knowledge @ db backups n restores......so i dont wht info u ppl will require to help me(I genuinely need ur help) on this topic......
Perhaps this is what I have gathered here.........
Year ago we made an sql db Maintenance plan which will backup all DB's selected on a regular basis(every day after 5hrs).There is no end date specified.
In sysdbmaintplan table of msdb owner is displayed as NTDOMAIN\MADHAVI (windows login of the m\c from where this planner was made) for this planner.
In msdb's table called backupset, its showing user_name as NTDOMAIN\Administrator for this planner.(windowsNT login of the server where this planner is executed)
Till 20th feb 2004, backup of all selected files was taken by this maintenance plan.
After this date this Plan has not backed up ne DB tht were selected.When I see
Database Maintainenance Plan History for tht particular DB Maintenance planner and date =02/21/04, I found Status with red dot enclosing cross sign and following message
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'D:\MSSQL7\BACKUP\DotsJrOld\DotsJrOld_db_200308211730.BAK' failed, status = 112. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

(Where dotsjrold is db name.)
This message is also shown in sysdbmaintplan_history of msdb database for startdate as 2/21/03 record, succeeded = 0, error = 3202
There is plenty of space available on d: drive of server.
Does nebody have ne idea why this planner has failed to back up db'es?
ne helpful links on this topic too will be appreciated.
I hope i have not broken ne of forum rules.

Thanks in advance,
Jaishree

Viewing 6 posts - 1 through 5 (of 5 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

42000 indicates some kind of access violation.

Check DotsJrOld_db_200308211730.BAK, if exists, try to rename it. You may experience difficulty.

Benediktas Laurinavicius SSC Enthusiast Points: 111 More actions · Answer 2

Jaishree - I have also encountered such a kind of error and I still haven't found any solution. In one discussion i found that sometimes the backup fails at the verifying stage, so if you uncheck the verification option it may work. However in my case the backup goes on and terminates after 80%, it does not reach the verification stage If you have some new information according this problem, please share your experience.

wz700 - i'm trying to do new maintainance plan and the file *.bak doesn't exist, the error is the same, so i think it has nothing to do with renaming. However what do you mean 'access violation'? It may occure in different situations. I thought maybe it is due to hard disk problem, but i checked and found out that my HDD is OK.

Jaishree Old Hand Points: 365 More actions · Answer 3

Benediktas:

As such I dont have enough knowledge but I must tell you that in my case,I found Sql Server Agent as stopped.So I started it and my maintenence plan started working as it did b4 the problem started.

Please make sure that there is enough disk space where back up will take place.

Jaishree

Benediktas Laurinavicius SSC Enthusiast Points: 111 More actions · Answer 4

Jaishree,

I have enough disk space in both partitions of my HDD. However I now understand where from your problem came: when you created maintainance plan it also created a JOB in the SQL Server Agent, if it is stoped obvious your backup will fail, because the JOB will fail or to be more specific it won't even start; in my case I also tried to run a maintainance plan through Query Analyzer as a script, it was doing backup till 80%, I saw how file *.bak became bigger and bigger, so activating or edactivating SQL Server Agent won't be a solution. Anyway thanks for your input.

By the way I tried other methods of doing a backup, they didn't work also. The problem is somewhere where my knowledge do not reach

Benediktas L.

dma333 Right there with Babe Points: 792 More actions · Answer 5

I searched for answers on this one to no avail, but I just figured it out. In my case it was a lack of space. Once there was enough space, the error was gone and my backup succeeded.