Database Health Checks

Question

Database Health Checks

Maha-848518

SSC Enthusiast

Points: 142
More actions
October 16, 2008 at 11:13 pm

#199877

Dear All,
Assume i have close to 100 server hosting SQL 2000. I have to perform database health checks on the user databases in all the servers periodically. Could you please let me know the options available. Your help would be much appreciated. Thanks very much
regards
Maha

Viewing 4 posts - 1 through 3 (of 3 total)

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

tosscrosby SSCoach Points: 18045 More actions · Answer 1

Since nobody has replied, I'll give you my 2 cents and see if others have a better idea. I only have 15 servers so not quite the same situtation but....Create a maintenance plan on each server and run the database integrity check across all databases on the server. I believe you'll have to setup each job separately on each server. At a minimum, I do this on ALL my production servers and personally would recommend you do as well (I also do it on some development servers that see a fair amount of activity). Check the results in the SQL log when the job has completed. With luck it should say "0 errors found, 0 errors repaired" for each database that was checked. If it doesn't, determine what the issue is and post the results to the corruption topic at this forum for surprisingly quick and accurate responses (although, with corruption, it may not be the response you want to hear!). That's my approach. How frequently you run the process is determined by what outage schedule you have. I'd make an attempt to at least run this once a week. Knowing your data is clean will help you sleep a little easier at night.

-- You can't be late until you show up.

U J SSCrazy Points: 2423 More actions · Answer 2

Please follow these steps :-

1) On your local machine,create linked server for each server you want to monitor.

2) Create stored procedures/write select statements on your local machine.

(You can use four part query to fetch the data)

for e.g. select * from openquery(linkedservername,'select name from master..sysdatabases')

3) Save the results into local tables

You can add a date column in each table in which you are storing the data,so you can get the history details too..:)

Maha-848518 SSC Enthusiast Points: 142 More actions · Answer 3

Thanks very much for both of you...! I wish to know the various options for database healthchecks, one option would be to issue a CHECK DB with PHYSICAL_ONLY | DATA_PURITY(as directed by Terry), Could you suggest a similar options, i would be interested to know them all. Fingers crossed for your answers.

regards

Maha