Best Practices for maintaining a healthy Database?
-
What are the best practices for maintaining
high availability on a database ?.
how often dbcc, backup and log dump ? what other maintainence activities should be used ?
-
Backup (full, log, diff) as often as you can. I backup locally and then immedaitely copy to a remote drive. Let the tape grab it from the remote drive. Of course, some dbs are too large for this. I'd need other opinions from those with the 100GB or larger DBs.
Haven't used clustering, have to get a response from Brian K or some others on that.
DBCC, I run every week. With ss2k, it interferes less with daily operation, so you might want to do it more often.
I also have data integrity checks I schedule everyday to catch things that aren't covered by RI, but are business rules.
Check your hardware. Be sure someone monitors your RAID and the NT/2000 event logs. Have spare hardware nearby. Be sure your UPS and HVAC are being managed.
Schedule PM (periodic maintenance) once a quarter or once eveyr six months. Clean the inside of the server.
Script your databases periodically and save these somewhere else.
Practice restoring your databases elsewhere.
Lastly, log everything you do!! I mean everything!!! Route changes, IP alterations, display settings, SQL config changes (server, database, etc).
With most things, you have to balance the load of checks v activity. You will have to run them more and more often until someone complains and then backoff. Start with a week and then crank it down slowly over time.
Hope this helps and let us know what you decide to do.
Steve Jones
-
One more things:
Keep the Server patched, but test the patches elsewhere. Most downtime will come from poor QA and poor change control.
Steve Jones
-
Steve's list is good, I'll just add this - if you use the Maint Wizard and take advantage of its options, you'll be in good shape. From a pure DBA point of view, your tasks are backup, check db's for physical errors, rebuild indexes - all done by the maint wizard.
Andy
-
I'm not too proud. Maintenance Wizard is the way to go. If you use it though, NEVER check the Repair Minor Problems checkbox. In every build of SQL Server that's been released, I've seen a bug initially and later in service packs where the wizard would place a db in single user mode when this option was checked and never take it out. If there is a problem (which is rare) you will want to know about it and repair it manually. Really, the index rebuilds are the biggie. If you don't do regular rebuilds, you'll find your database will slow quickly.
I schedule a weekly reboot as well. Since my databases are clustered, the server is only down for a few minutes while it transfers control to the second node. I do have a server though that hasn't been rebooted since March. Quick, call Bill Gates and Guiness while it's still up!
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
Brian, I think I saw the single user thing fixed in one of the more recent service packs. Prior to that I had a job that checked all db's each morning after the maint run to reset any that were in single user mode!
Another caveat on the repair mode - if you're replicating and the log reader is running continuously the repair will fail, it cant get single user mode and doesnt seem to want to kill the connection to the logreader. And for those who think stopping the log reader is the answer - your log file will GROW and will not/cannot shrink until the log reader runs again!
Andy
-
Thanks guys .
i found this SQL Server 2000 Operations Guide on the Microsoft site at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
-
Two things on the maintenance plan:
1. I have had problems with the integrity checks failing. I will try Brian's solution and see if this is the issue.
2. When it fails, you have no idea why. The "Black box" approach is fine if you can determine the problem, but when this fails, you get a "SQLMaint failed" and that's it. For this reason, I have developed my own backup processes and am working on my own DBCC items.
Steve Jones
-
Call me picky but, easier is not always better. While the wizard provides a quick way to do general maint, it does not force one to really consider what they are doing.
Additionally as Steve mentioned, when you have problems (and note that I stated when), you will have to manually script for troubleshooting anyway so, why not invest the time to do it "right" in the first place??
Just the rambling thoughts of a lowly DBA with too much to do!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
We use the DB Maintenance Wizard heavily at my org, and we've not had much in the way of problems. It's like EM or any other tool... in the hands of a person who hasn't thought through what they want to do, it can be dangerous. But on the other hand, if one has, it's quick and painless and does most everything one needs.
We tend to run our integrity checks in the wee hours, so if an error is detected, we can check it out and do the manual checks ourselves first thing. It's just the idea of having an automated process to give you a heads up if everything is a-okay or if there is something amiss. If everything checks out, no worries. It's like automating backups. Every once in a while they are going to fail and that requires a manual check. But the man-hours are significantly reduced compared with running backups manually.
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
Well I script everything I can. I got lazy at my new job but I'm back on the wagon now. I submitted my backup scripts I'll do the same for my restore,index,and dbcc scripts. I just like the level of control you have over the maint wizzard.
Wes
-
I was recently asked to install some databases for a client and their support company specified that the database should be split into 3 filegroups.
1. PRIMARY - to be used for the systables only.
2. DATA - to be used for all user tables and objects.
3. INDEX - to be used for all PRIMARY KEYS and INDEX.
Now as far as SQL goes I can see that 3 files means you can put the different elements on different disk subsystems should you wish.
I'm not an NT guy but would I be write in thinking that the smaller file sizes that result from splitting the data and indices would also yield a performance benefit?
Edited by - David.Poole on 09/10/2001 02:11:23 AM
-
IMO, I dont think the file size matters at all. File groups really just give you a way to cross level your disk IO. In practice I think it makes more sense to use RAID-whatever and let the disk subsystem do the work. To me file groups make administration more complex for very little return.
Im not saying I would NEVER use them, just have not had a compeling reason to so far! Having a db grow to the point where a full backup at night was no longer possible might be one good reason (but we're back to complicated).
Andy
-
Does anyone bother to schedule database shrinks? Can it have a performance benefit?
-
quote:
Does anyone bother to schedule database shrinks? Can it have a performance benefit?You probably won't see a performance enhancement by shrinking your database or logs. Shrinking and expanding of your database is still left done as a scheduled event. This is because it causes a number of locks which prevent users from effectivily hitting the database. When my databases expand, generally, most of my applications will time out. The only performance enhancement I've seen from shrinking my db was in backing up the database, and that will only be slight.
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply