January 23, 2007 at 3:53 pm
Hi All,
I'm wondering if there are standard, recommended maintenance routines for user databases. Thus far I've found 'it's relative to the application', but I think there must be standard 'good practice' maintenance routines. I'm from an Oracle background and I'm always trying to do the SQL Server <-> Oracle translation.
On all of my user database (all < 10 Gb) I do the following:
1. check database integrity
2. shrink database
3. reorganize index
4. backup database
5. update statistics
6. maintenance clean up
Thanks again everyone for your assistance, this is an excellent tool !
January 23, 2007 at 10:20 pm
Welcome. I knew a Tim White once.
As for standards. What you do on Oracle is fine for small databases. Some things you don't need to do no SQL unless you have a problem:
Database Integrity - don't really need to do this unless you are having a problem. If you've got the time in your maintenance window, go ahead, but don't sweat it if you don't.
Shrink database. Unless you've got a problem, don't sweat this either. Because of the mechanism for pointers in the log files, etc. just running the job once isn't necessarily going to shrink the files anyway. Shrink if you think it needs it.
Rebuild the indexes as often as you can without disturbing your business units.
Backup databases always. There's several different opinions on how to do this, and all depend on what you want to accomplish in the disaster recovery or refresh process.
Update Stats - again, this is something you may or may not want to do. Depends on your maintenance window, and whether or not you're using auto update stats, and if you have any perf problems/complaints. I don't find it necessary to run this often except on some databases which table structures that aren't designed for SQL Server.
Not sure what you mean by Maintenance cleanup. Delete old backup files possibly?
Lezza
January 24, 2007 at 8:53 am
Hi Leeza,
Thanks tonnes for your response !
What is the difference between 'Rebuild Index' and 'Reorganize Index' ? I can't turn anything up in BOL (meaningful to my anyway).
Regarding Maintenance Cleanup - you bet... i mean deleting old backups.
January 24, 2007 at 9:12 am
You're welcome.
In SQL Server you don't re-org, you rebuild, reindex or defrag. In SQL 2000 Books online (BOL) type in DBCC REINDEX. You'll find a handy little explanation about reindexing and defraging. Then use DBCC DBREINDEX. Of course, there's a tremendous amount of things you have to know and understand about indexs with SQL server that don't correspond directly to Oracle indexing structures. You have to learn SQL's B-tree structure, then you have to learn how each database's table design differs from what Microsoft wanted it to be, and then you have to design a reindexing program that'll offset/compliment that design. This is really where most of the muscle is in a SQL server dba. The better you understand this, the better you can do your job.
January 25, 2007 at 5:41 am
As far as the Update Stats routine. I have the two auto stats flags on for all databases so stats are updated when SQL routines make changes to the data. As a precaution I have a monthly job that updates stats for all databases with 70% sample. I guess if you had an extremely large db you would have to lower the percentage for the sample as the job could run all day.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply