Solved

Maintenance Plan

Posted on 2006-06-26
20
653 Views
Last Modified: 2008-02-01
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
Comment
Question by:stevendunne
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16982941
>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
 

Author Comment

by:stevendunne
ID: 16982975
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16983008
>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
 

Author Comment

by:stevendunne
ID: 16983043
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16983138
>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
 

Author Comment

by:stevendunne
ID: 16983352
>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16983404
>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
 

Author Comment

by:stevendunne
ID: 16983431
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16983453
>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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16983465
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16983508
>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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16983535
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
 

Author Comment

by:stevendunne
ID: 16985675
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16985889
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16985951
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16985972
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 16986280
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
 

Author Comment

by:stevendunne
ID: 17216382
"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
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 17528337
PAQed with points refunded (350)

DarthMod
Community Support Moderator
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now