Maint. plans

I have created a maintenance plan to do the backup for a set of
databases in a server, I created the backup device first and checked to
see that there is enough space on that drive to hold the backups and the
tran log dumps. the job is failing the optimization is failing too. In
the plan log it indicates that the server must be in single user mode to
complete the backup of the tran log. The backup of the main databases
does not happen until sun morning so I do not know if this is going to
fail as well. The funny thing is if I try to backup the db from EM it
works. This is the error message I get from the job history:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
Can anyone help?
*** Sent via Developersdex http://www.examnotes.net ***Bill,
Having the option in the maint plan to "Attempt to repair any minor
problems." will require the database to be in single user mode. Might look
to see if that option is selected for the maint plan.
HTH
Jerry
"Bill Orova" <nospam@.devdex.com> wrote in message
news:uWnAePtvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have created a maintenance plan to do the backup for a set of
> databases in a server, I created the backup device first and checked to
> see that there is enough space on that drive to hold the backups and the
> tran log dumps. the job is failing the optimization is failing too. In
> the plan log it indicates that the server must be in single user mode to
> complete the backup of the tran log. The backup of the main databases
> does not happen until sun morning so I do not know if this is going to
> fail as well. The funny thing is if I try to backup the db from EM it
> works. This is the error message I get from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> Can anyone help?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Jerry Spivey,
Thank you very much that was the problem
Bill Orova
*** Sent via Developersdex http://www.examnotes.net ***|||Maintenance plans are a blessing and a curse. Depending on what options you
check they try to do too much.
If you just want to backup your databases you can back them up directly to a
file.
BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
You don't even need to create a backup device first!
If you want to perform database maintenance I would suggest that you look
into DBCC commands and run them separately.
You can schedule your backups and your DBCC statements via jobs within the
SQL Server Agent node of Enterprise Manager.
Keith
"Bill Orova" <nospam@.devdex.com> wrote in message
news:uWnAePtvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have created a maintenance plan to do the backup for a set of
> databases in a server, I created the backup device first and checked to
> see that there is enough space on that drive to hold the backups and the
> tran log dumps. the job is failing the optimization is failing too. In
> the plan log it indicates that the server must be in single user mode to
> complete the backup of the tran log. The backup of the main databases
> does not happen until sun morning so I do not know if this is going to
> fail as well. The funny thing is if I try to backup the db from EM it
> works. This is the error message I get from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> Can anyone help?
>
> *** Sent via Developersdex http://www.examnotes.net ***