SQL Maintenance

By jimmy-jam ·
I am looking for some recommendations about maintaining an SQL server. I have 7 standalone PC's (Windows 2000) running SQL Server 2000. There is alot of data flowing in and out of these machines. They are starting to slow down a bit. Apart from backing them up, is there some type of regular maintanence that is recommended to keep things humming along? What are you guys (and gals) doing?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Kinda busy in here this afternoon

by jimmy-jam In reply to SQL Maintenance
Collapse -

In Enterprise Manager

by Dumphrey In reply to Kinda busy in here this a ...

you can go into each server, management, database maintenance. Here you can create plans to back up etc. The key for you is optimize and integrity checking. I would run a back up first, but it hould help speed up some. Also, I would take the database off line and defragment the HD(s) its on as well. Fragmented dataspace and hard drive makes for longer read/writes/searches.

Collapse -


by cmiller5400 In reply to In Enterprise Manager

It's been several years since I actively maintained a SQL server, but in addition to the other posters comments, if you have control over the indexes, it helps to have them set up correctly.

Collapse -

I am no DBA so would

by Dumphrey In reply to And

not know if the indexes were incorrect anyway =\ I have always relied on "its worked so far".

Collapse -

I'd definitiely look

by Tony Hopkinson In reply to Kinda busy in here this a ...

at my indexes, just rebuilding them ( try it out of hours first !) can give you some good gains.
DBCC for integrity checks.
Defgragging the volumes, back up and restore sort of does a big house keeping exercise, but for cthulus sake make sure it's a good back up before you restore it over live.

You could look at how your indexes are formed, if your performnce losses are more down to volume than housekeeping, but that is more art than skill.

Be wary of some of the tools that advise you on such things, they are very sensitive.

Run one on monday morning when no normal work has happened over the week end but you did an out of hours archive job, and it will cheerfully advise you into a major f'up.

After that it's things like splitting your dbs across file systems, partitioning them by activity, or simpler tasks such as putting logs on one disk controller and data on another.

Course that's assuming your database schemas are sound.

You may find this site a boon

Lots of good stuff.

Related Discussions

Related Forums