Super Weird Issue - Maintenance Plan

[qsac]

I have a very very very standard Maintenance Plan.

All it does is full backup of all databases to a drive and delete files older than 2 days.

The drive has plenty of space.

The job hangs after it backs up all the databases. I see in the logs that all the DBs are backed up, but job is hung from there.

Any Ideas?

Thanks,
Q

 

[NortonES2]

Check event logs, SQL logs and the job history for any pointer as to what the problem could be.

-------------------------------

If it doesn't leak oil it must be empty!!

 

[DrSql]

Also make sure any other process are locked the backup file. If it was running for 2 days it might take two days to roll back. Kill the process thats connected and try to do backup again.

Dr.Sql
Good Luck.

 

[qsac]

No processes are running. I alos checked the logs and there was no issues reported.

I even check to record error logs. It creates a 0KB file, but doesnt write to it.

I am starting to lean towards permissions. But the user who runs the job is part of Admins group.

As always, thanks, for any insight.

Q

 

[DrSql]

Look into permissions of the user that SQL Server Service and Agent is running...

Dr.Sql
Good Luck.

 

[qsac]

OK, i dont think it is permissions.

I found one Database that is not getting backed up. When i back it up manually, that database backs up with no issues.

The logging is turned on and it writes 0 KB file. I am so lost.

I have tried recreating job. No Luck

Anyone have any ideas?

 

[SQLBill]

A manual backup runs under the permission of the login running it.

A scheduled (job) backup runs under the permission of the SQLServerAgent service login.

So it COULD be a permission issue.

-SQLBill

Posting advice: FAQ481-4875

 

[qsac]

OK, i am getting closer.

There is one Database that is the Culprit.

ALl databses back up with no issues accept one.

If i back that DB up manually, it works. But as part of the plan it does not backup. All other DB's backup up normal. No errors anywhere.

I appreciate everyones help.

Q

 

[SQLBill]

Again, I don't use the maintenance plan...but check the steps...normally each step has a setting for what should happen when the step succeeds and when it fails. Check the step before the one that doesn't work....it might be set to Quit upon success. If so, then the following step won't happen.

Also, you say no errors anywhere...did you check the Windows Event Viewer logs? In Enterprise Manager, expand to Management>Jobs. Find the maintenance plan job, right click on it and select View job history. Check the box for expanding job steps. Click on each step and see what it says about it. If the step never started, then most likely there was a command like I explained above.

-SQLBill

Posting advice: FAQ481-4875

 

[Matchcare]

QSAC,

I may have a workaround for you.

The situation you've described has also arisen on our web/database-farm: One database seems to be blocking the succesfull database- and transaction log-backups of other databases. (Luckily, the DB itself remained online and responsive to the application.) This may or may not be important, but we use database replication on our site. Do you do as well?

In troubleshooting our installation, I have discovered the following:
- Manual backup of the offending DB works fine.
- Deleting the pull-subscriptions from the DB works fine, as do other maintenance
actions, such as shrinking the DB.
- When I remove the offending DB from the maintenance plan (A), plan A does
not hang anymore, and all remaining DB's in plan A will thereafter backup
succesfully.
- When I create a new plan (B) for the offending DB, it will still NOT backup
the DB or the T-Log.
- Detaching/Re-attaching the offending DB does not remedy the situation.
I tried this with both plans A and B.
- Detaching the DB, and moving it physically from server X to server Y, and
attaching it to that SQL-Server _did_ resolve the issue. Adding the newly
moved DB to an existing maintenance plan (C) on this machine worked fine
as well. All DB's in plan C continue to backup succesfully, including the
(previously) offending DB.

This last step may be a workaround for you, however it does not offer an explanation about why the problem arose in the first place. We've noticed SQL-Server going berserk on the processors (2 Hyperthreaded CPU's at 100%) prior to these problems, which left no option other than restarting the service. After that all seemed normal again, until the next daily backup started...

Seeing that a move of the physical MDF/LDF files to another machine creates a once more working DB, suggests to me that the problem may not lie in the DB itself but perhaps in the registration of the DB within the SQL/Server instance. If this is the case I fear more problems when we add another DB to server X.

Could anyone comment on this point?

Regards,
Stephen.