May 22, 2002 at 4:05 am
I was really looking for a list of tasks one might perform when maintaining a database.
I was hoping it would be general and would give me an indication of the overheads that come with looking after a database.
May 22, 2002 at 4:52 am
1) Backups DB and Log fitting your time constraints and data recoverability needs.
2) Reindex on 7 or DefragIndex on 2000, properly maintained indexes keep speed up.
3) Update statistics.
4) Reorganize data pages and and possible shrink DB if 7 when you do reindex.
5) Clean out tranasaction logs and shrink in some cases (these are bad about sticking around and growing quite large in many cases).
6) Periodically checkd database consistency.
7) Update usage with DBCC UPDATEUSAGE reports and corrects inaccuracies in the sysindexes table.
8) Periodically take a look at Hard Drive fragmentation and if extremely high set up a maintainence downtime to shutdown the server and defrag the physical hard drive.
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.
These are some of the majors I touch on when I am maintaining my servers. But you will find there is always something to do since you have to research various issues that crop up along the way.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 22, 2002 at 5:19 am
Thats a very good list. Only thing I have to add is that using a maintenance plan on your server will take care of a large number of these items for you. They don't handle every situation of course, but for 90% of the time they are good enough.
Andy
May 22, 2002 at 8:50 am
That's excellent guys, thank you very much!
June 17, 2002 at 3:34 am
Hi Antares686 and others
Would the following maintainance plan cover your list?
DATABASES
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.
I am relatively quite new to SQL Server. Cheers
June 17, 2002 at 6:07 am
Please do not cross post. See http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4860&FORUM_ID=5&CAT_ID=1&Topic_Title=Database+Tuning+and+Maintainance&Forum_Title=Administration
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply