Editing Maintanance plan? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Editing Maintanance plan?

Hi, I found my backup and integrity check job fails with following erors….
************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server ‘GBO278A’ as ‘NT AUTHORITYSYSTEM’ (trusted)
Starting maintenance plan ‘gbo278a_daily_appdb_backup’ on 5/26/2005 4:00:03 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database ‘DBA’
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database DBA: Check Data and Index Linkage…
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. The following errors were found: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database DBA: Database Backup… The backup was not performed since data verification errors were found. ************
And in one document I found the solution as ..
In your maintenance plan, uncheck the Attempt to repair minor problems
checkbox on the Integrity tab.
Can anyone tell me how to edit Maint-Plan. for eg: my case .. Please advice
In your maintenance plan, uncheck the Attempt to repair minor problems checkbox on the Integrity tab. The reason behind this is integrity checks are still performed on the databases and you will be notified of any errors as it stands as a good practice to check the reports. In any case it won’t try and autmacially repair the errors. It is purposefully used to notify the issues. This has been removed in SQL 2005. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Sathya, I dont know how to edit it. Can i use Wizard to edit maint-plan? or you want to me to recreate all the jobs using Main-Planer wizard? let me know rajiv
You can go to Manangment, jobs, right click on job, properties, set tab and change SQL statment.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
<b>This is my SQL statment looks like, can anyone tell what changes to made to disable "Attempt to repair minor problems"</b> EXECUTE master.dbo.xp_sqlmaint N’
-PlanID DFE7A4F1-9804-4CF3-854C-66993C2F4446
-Rpt "g:program FilesMicrosoft SQL ServerMSSQLLOGgbo278a_daily_appdb_backup4.txt"
-DelTxtRpt 4WEEKS -WriteHistory
-VrfyBackup -BkUpOnlyIfClean
-CkDBRepair -BkUpMedia DISK
-BkUpDB
-UseDefDir
-DelBkUps 3DAYS
-CrBkSubDir
-BkExt "BAK"’
Clear -CkDBRepair .
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Hi Luis wht is -BkUpOnlyIfClean for?? is tht necessary?
All options in: http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Hi I am back again. So if -CkDBRepair reports some error means, there is some error with database.So backingup withought this option is a good practice?? pls advice .How do i rectify error if this reports any error?
Run DBCC CHECKDB to check what kind of issues lies on the database.
Yes it is a good practice to backp the database after the consistency checks proves no issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Satya, -CkDBRepair reports some error in maintanance log , and backfails becos of that. when i ran DBCC checkDB, i didnt get any error. removing -CkDBRepair is recommended?
Yes better do not use it in the same plan, divide the operations in seperate plans. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>