Anyone Tell me what regular Housekeeping is needed for SQL DB's

Question

Anyone Tell me what regular Housekeeping is needed for SQL DB's

dax.latchford

SSCommitted

Points: 1969
More actions
April 9, 2015 at 4:17 am

#295797

Hi - Just looking for advice regarding what regular housekeeping tasks should be performed on SQL DB's?
Thanks

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

There are many factors affecting the database maintenance plan based on the application and type of database and environment. However primarily you can focus on backup strategy, High availability, Database consistency check, Checking fragmentation and reindexing, if required archiving data etc.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

Check Database Integrity

Database Backups

Log Backups

Backup Testing

Statistics Maintenance

Index Maintenance

Those are the basics I think.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Grant Fritchey (4/9/2015)
Check Database Integrity
Database Backups
Log Backups
Backup Testing
Statistics Maintenance
Index Maintenance
Those are the basics I think.

Just to add to that fine list... Checks on performance and resource usage (usually against a previous baseline which should be taken at least monthly).

And to be clear, "Backup Testing" should be assumed to include "Restore Testing" for both RPO and RTO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Jeff Moden (4/9/2015)
Grant Fritchey (4/9/2015)
Check Database Integrity
Database Backups
Log Backups
Backup Testing
Statistics Maintenance
Index Maintenance
Those are the basics I think.
Just to add to that fine list... Checks on performance and resource usage (usually against a previous baseline which should be taken at least monthly).
And to be clear, "Backup Testing" should be assumed to include "Restore Testing" for both RPO and RTO.

I left off Monitoring and Alerting of all the above, plus what you said.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

This does fall into the category of Log Backups which Grant mentioned..

I would expand it to Log File maintenance. This includes changing the recovery model to Simple for Databases that don't need log file backups (I see this all the time and it drives me crazy). Also making sure that log files are sized correctly - e.g. Allocating enough space so as to limit (or eliminate) log file autogrowth.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001