June 17, 2002 at 5:31 am
Would the following maintainance plan be adequate for most of my databases? I am compiling a list of Tasks to properly maintain and Tune my SQL server 7 databases.
DATABASES
e.g Test
SERVERS
(local)
OPTIMIZATIONS
Occurs every 1 week(s) on Sunday, at 01:00:00.
Perform the following actions:
Reorganize data and index pages, changing the free space to 10 percent of the original space.
Shrink database when it grows beyound 50 MB. Leave 10 percent of data space as free space.
INTEGRITY CHECKS
Occurs every 1 week(s) on Sunday, at 00:00:00.
Perform integrity checks before backing up database.
Perform the following actions:
Check database
COMPLETE BACKUP
Occurs every 1 week(s) on Sunday, at 02:00:00.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Week(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
TRANSACTION LOG BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, at 00:00:00.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Week(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
Reports will be generated and stored in directory: C:\MSSQL7\LOG
Also how do you "Update usage with DBCC UPDATEUSAGE reports and corrects inaccuracies in the sysindexes table" -- What is this about?. Hard Drive fragmentation -- How do you find this out?
Kindly also enlighten me on no 9:Periodically perform a baseline analysis of the server (Memory, HardDrive, Filegrowth, Connections) using Profiler and PerfMon to make sure I am planning for future needs.
Anymore areas I should be looking at?
I am relatively quite new to SQL Server. Cheers
am relatively quite new to SQL Server. Cheers
June 17, 2002 at 5:50 am
Couple comments, I sure others will have more. First thing I see is you need to confirm that you have space to support 5 weeks worth of backup - 5 because it doesnt delete the oldest one until the newest one is created. The other is whether you can schedule a full backup during the week rather than log backups. Certainly using the log backups you'd be able to restore, but if it crashes on Fri it'll take a while to apply all the log backups. If you have the space..AND time...I'd go for a daily full backup then a more frequent log backup during the day. This will make recovery quicker and narrow the amount of data that may be lost.
Andy
June 17, 2002 at 6:27 am
In addition, you state you are somewhat new to SQL. Make sure your backups are to another drive not on the same as the databases themselves. You may have installed SQL to one drive and the data to another which is what you want but you need to make sure your backups are able to be retrieved if your data drives fail. Also make a copy to another location just in case of server failure and preferably put a copy on tape and store off site just in case your sight burn down or some other unforseen issue that makes it impossible to use the current machines.
Now for
quote:
What is this about?. Hard Drive fragmentation -- How do you find this out?
For hard drive fragmentation you will need to use the defrag utility in NT 2000 + or purchase a good one for NT 4, in most all good defraggers it will give you the percent of fragmentation of the data. You want it to below 5% if possible without defragging the drive too often (once a week is the worst I would do but plan at leats once a month unless otherwise needed). Proper file sizing can make this an almost unneeded step.
As for the baseline I will post back some details later but check out http://www.sql-server-performance.com there is a lot of usefull information there too relating to this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 18, 2002 at 12:54 am
Thanks so much Andy warren and Antares686 for your contribution. It is well appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply