SQL 2005 Maintenance Plan - No Expert Here

Question

SQL 2005 Maintenance Plan - No Expert Here

Steve-438436

Valued Member

Points: 70
More actions
August 6, 2007 at 9:22 am

#179936

I have responsibility for a SQL 2005 Server but I have no background in SQL Server maintenance.
We are a small non-profit charity with limited personnel and resources.

I am looking for an article and or recommendations for maintenance plans.

I think I understand the Back Up Database Task and Maintenance Cleanup Task.
I perform a Full Backups once a day and Differentials every hour.
I delete files older than 3 days

The following tasks I do not understand and need some understanding of the consequences of running them such as do all users have to be logged off the system and how do I do that?

I also need recommendations as to how often and what order these tasks should be run.
     Check Database Integrity Task
     History Cleanup Task
     Rebuild Index Task
     Reorganize Index Task
     Shrink Database Task
     Update Statistics Task

Also, is there anything else I should consider consider.

We are a 7X24 shop but slow at night.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

to get more useful recommendations i would suggest you give more info on for example how busy the server is i.e. transactions per hour etc , the size of your databases, which sql server edition you are using

Everything you can imagine is real.

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 2

I agree that more info is needed on the size of your database and how much it grows, etc...

But from the information you have given, I do not think a nightly backup and hourly differential backup is correct either way. There are several ways to set up your backups depending on your needs. A good backup plan to start with is as follows.

- Weekly Full backups

- Nightly Differential backups

- Hourly Transaction Log backups

- Weekly maintenance including Reorganizing indexes and updating statistics.

You can see how this works and adjust it as needed.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

To run the tasks on your list, you do not need to have users logged off. However, the objects may be locked while some of tasks are running. Users may not be able to access/modify data while running some of these tasks, in particular, reindex, update statistics, ...

How often you need to run these tasks depends on your project. It is really a case-by-case question. If you data is modified quite often and heavily, you may need to run reindex, update statistics, ... more often. You may run these tasks every day if there is no downtime limit and database sizes are small. Otherwise, you may have to run them weekly or monthly. It is a kind of trade-off.

Hopefully, the above is helpful.

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 4

Stats should be automatically updating. Run them manually if you have big data changes (loads or deletions), or schedule them if you have regular changes, prob weekly.

Backups - Like Ronda's advice. Tend to run once a week fulls, diff's daily or even regularly like every 4-6 hours if recovery needs to be quick. Logs anywhere from hourly to every 5 minutes. Be sure you get these files off the server, but keep enough to recover. Meaning keep a full, the latest diff, and logs since the diff on the server.

Indexes, weekly unless you have big data changes. Might do these nightly in that case and if there are lots of changes, might be sure your fill factor is low enough to prevent constant page splits.

History - Run this when after backups.

EdVassie SSC Guru Points: 60472 More actions · Answer 5

Steve Hogan,

Where are you based? Maybe someone who monitors this thread is nearby and can offer some free help.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara