Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

Maintenance Plan

Since we been using SQL 2000 to host our DB, I have never used the maintenance plan wizard.  I've just created a job to perform the full backups every night.  

Our developers have recommended to run a maintenance plan to perform the optimize job & integrity check against the production DB.

The integrity check failed when I tried to run it for the 1st time over the weekend, saying "Database needs to be in single user mode."  I had the "attempt to repair any minor problems" selected.  Is it recommended to select this ?  If so, is there any other way of running this job without scripting it ?

Also is it essential to backup the database as part of the maintenance plan ?
0
stevendunne
Asked:
stevendunne
  • 7
  • 6
  • 4
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Our developers have recommended to run a maintenance plan to perform the optimize job & integrity check against the production DB.
you should not need to run that regulary, assuming that all tables have clustered indexes.

more important is to have a valid backup and fault-tolerant disk system, and of course the option "auto-update statistics" on the database and all tables to be enabled
0
 
stevendunneAuthor Commented:
Forgive me, I'm new to maintenance plans.  

Our DB is sitting on a single server, with fault tolerant disks, differential backups every 3 hrs, and a complete backup at night.

So should I run the optimize and integrity checks every month, or maybe longer ?

I haven't got the auto-update option selected on the optimize check, I will enable this.

The integrity check failed initially saying "Database needs to be in single user mode."  I had the "attempt to repair any minor problems" selected.  Is it recommended to select this ?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So should I run the optimize and integrity checks every month, or maybe longer ?
you don't need those checks.

>I haven't got the auto-update option selected on the optimize check, I will enable this.
I spoke of that setting in the per database, nothing to do with the maintenance plans

>The integrity check failed initially saying "Database needs to be in single user mode."  I had the "attempt to repair any minor problems" selected.  Is it recommended to select this ?
as you don't really need those checks, you can drop that all together. to get rid of the error, you could uncheck the "attempt to repair errors" as that setting indeed requires singe user mode and is NOT RECOMMENDED as regular action
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
stevendunneAuthor Commented:
Why don't I need the optimization & integrity checks then ?

Also, after I upgraded to SP4 on SQL 2000, the taskpad view doesn't run on my production database.   I get an IE script error...

res://C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html

It does however run the on the system databases ?  Any clues off the top of your head ?

Thanks again
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Why don't I need the optimization & integrity checks then ?
that's microsoft vision of doing things that actually you don't need. you have fail-tolerant disks, you don't need integrity checks.
and if all tables have clustered indexes, optimization checks are redundant.


regarding the error, if you connect remotely to the box, you need to install SP4 also on your client machine
0
 
stevendunneAuthor Commented:
>that's microsoft vision of doing things that actually you don't need. you have fail-tolerant disks, you don't need integrity checks.

What about optimisation checks on the indices ?  Those are the words of what my developer would like.

Like I said earlier, I had run the optimise check over the weekend, the job was successfull.  The integrity check failed.
Now today my 11am and 1pm differential backups are 10GB in size, rather than around 1.5GB.  What's happening here ?

I'm on box through RDP with those script errors occurr.  But I can use the taskpad view on the system DBs etc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Now today my 11am and 1pm differential backups are 10GB in size, rather than around 1.5GB.  What's happening here ?
that's due to the opimizations job. it will recreate alot of indexes and table parts, and this will generate alot of transaction log space.
this is the main reason why you should NOT do this automatically, and your developer should be fine with his database unless he has performance problems (while those problems will usually NOT be solved with the optimizations job)
0
 
stevendunneAuthor Commented:
What do I need to do in order for my differential backups to return to the normal size ?

I'll scrap the optimise & integrity jobs !
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>What do I need to do in order for my differential backups to return to the normal size ?
do this:
>>>> scrap the optimise & integrity jobs !
+ run 1 full backup
0
 
MikeWalshCommented:
AngelIII forgive my ignorance here, but aren't the integrity checks doing a DBCC CHECKDB() on each database specified in the maint plan?

That is definitely a NECESSARY check. Fault tolerant disks are great, but if you have corruption, you have corruption. That is not a failure, it could be a quark, it could be a virus scan scanning an MDF/LDF, it could be a power failure, it could be any number of bugs/processes/physical events.

If you don't regularly do DBCC CHECKDBs you may not know about corruption until it is too late. Running them regularly allows you to catch a problem earlier when there is less potential for data loss/downtime.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but if you have corruption, you have corruption.
and then, you do what?
dbcc checkdb() will not detect a corruption until it has arrived, so it won't help alot.
this would mean that you have to run dbcc checkdb() all the time, in order to detect the failure "before" the users...
... which is something you can forget (IMHO).

anyhow, the database properties to check for "torn pages" is enough.
0
 
MikeWalshCommented:
Well the DBCC can check corruption that torn page detection will not discover. I would reccomend running a DBCC CHECKDB() at least weekly on databases. I believe Microsoft also reccomends this. No, you may not always catch it before the users but you can at least catch something when there are fewer errors/issues making recovery time quicker.

http://blogs.msdn.com/sqlserverstorageengine/

that links to a blog run by the storage engine team at SQL. Read some of Paul Randal's entries about checkdb...
0
 
stevendunneAuthor Commented:
Mike,

Do you also agree with removing the option "attempt to repair any minor problems" from the integrity check.  

Do you run both the optimize & integrity checks on your production DBs, say every weekend ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
MikeWalsh:
>that links to a blog run by the storage engine team at SQL. Read some of Paul Randal's entries about checkdb...
very interesting stuff, that is at the same level as the books "inside Sql server" and "SQL unleashed", with the advantage that it compares between SQL 2000 and SQL 2005.

I see your point, but I does not convince me of using it (regulary)
in high-load servers, this only increases the load during the time, and put locks on the pages during the investigation.
in low-load servers, the detectable issues are very ! rare (i have 4 sql servers, which during 8 years did not present such corruptions)
0
 
MikeWalshCommented:
Well I guess we can agree to disagree :) I like being proactive. I hardly have to do restores, but I still take all care in ensuring my backups are successful and always available.

I also have read both books mentioned and constantly refer to them. I think a lot of the blog entries over there go a lot more in depth than those books.
0
 
MikeWalshCommented:
Steve,

I do remove the repair automatically and just let it check. Then I have a process to read the error logs for errors. If an error is found, I will act on it myself rather than let the system repair it for me.
0
 
Scott PletcherSenior DBACommented:
I, too, strongly believe you should do the integrity check.  It is true that corruption should be very rare.  However, it is also true that, if it occurs, you want to catch it as quickly as possible, especially since it may be a sign of a hardware problem that is likely to get worse and afflict more data if left uncorrected.

Btw, optimization is also needed, albeit the schedule needed may vary greatly for each table.  A more specific answer can only be given on a case-by-case / table-by-table basis.  But in no way is the answer "you never need that".  Sometimes you need frequent, even daily, reorgs of selected table(s), sometimes monthly is enough, and, rarely, even less often than that.    

To avoid the incremental backup issue, do the reorg *before* you do the normal full backup for that day.  Then the full backup will prevent an overly-large incremental backup.
0
 
stevendunneAuthor Commented:
"To avoid the incremental backup issue, do the reorg *before* you do the normal full backup for that day.  Then the full backup will prevent an overly-large incremental backup"

I am running the integrity & optimise checks after the full backup (then doing another full backup), just incase the checks cause a problem with the DB.  Or in your experiences, is this very rare ?

Talking of the optimise check, I'm not currently using the settings...

'Remove unused space from database files'
'Shrink DB when it grows beyond'  
'Amount of free space to remain after shrink'

Do you, if so, any recommendations ?

0
 
DarthModCommented:
PAQed with points refunded (350)

DarthMod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now