Database Maintenance...

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
Anakha56
Forum Administrator
Posts: 22136
Joined: 14 Jun 2004, 02:00
Processor: Ryzen 1700K
Motherboard: Asus X370
Graphics card: Asus 1060 Strix
Memory: 16GB RAM
Location: Where Google says

Database Maintenance...

Post by Anakha56 »

Morning All,

So just a quick question for all you database freaks out there and this applies MSSQL and Exchange 2010 which as far as my understanding goes is a MSSQL database anyway...

What tasks need to be done to maintain performance? What are the best best practices regarding this? And I take it these sort of operations should not be done while the database is live?

My reason for asking (and I expect some flak regarding this) is because we dont have any Maintenance tasks for our live Production database and our Exchange 2010 database and I suspect as well as our SAP database. So yeah I just need some tips regarding database maintenance but I also need know the risks involved.

/This is what happens when ones IT Department is stretched to thin... :(
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Database Maintenance...

Post by hamin_aus »

You should be backing up everyday. SQL and Exchange have built in backup tools.

On the SQL side in addition to backups you should be doing index reorders or rebuilds - this may not have to be done daily, depending on your databases.
What is your live production database? You work in the automotive indstry, so I take it it's some sort of workflow or job tracking system? These are pretty low maintenance and creating a generic backup plan with the wizards should suffice.

What you should do is ask your vendor what maintenance they suggest you do, as some vendors have recommended set of things they want you to do, and other vendors are just developers and have no more clue about how to maintain a DB than you do ;)

I guess if it hasn't broken by now it's not the busiest set of DB's in the world and will probably coast on for ages... but my advice would be to get a DBA in there, even if it's just for a few days to do a health check - capture a workload and work out just how much maintenance needs to be done.
The beauty of MSSQL is that once you set a maint plan up it will run forever, unless something silly happens - like a permissions change to SQL service accounts, or a disk filling up...
So you can pay a guy a once-off fee to set up a plan, document it so that you guys can keep track of it and then you can get on with whatever it is you do when you are ignoring the maintenance of your DB's :P
Image
Anakha56
Forum Administrator
Posts: 22136
Joined: 14 Jun 2004, 02:00
Processor: Ryzen 1700K
Motherboard: Asus X370
Graphics card: Asus 1060 Strix
Memory: 16GB RAM
Location: Where Google says

Re: Database Maintenance...

Post by Anakha56 »

Our database works with data flowing in from our customer, SQL then translates that information into data that our Production System uses, which then flows the data out to the relevant points. Once those points have processed that data it flows to the final point which generates it into a shipping list. We do have backups for this system and every now and then we truncate the logs but thats like once in every 7 months.

The Exchange 2010 database is another beast entirely because I have no idea what I should be doing on that one. I have found some online content that I will be implementing tomorrow once the RAID array has finished rebuilding (yes rusty after 72 hours its still going ;) ). I just wanted to get the guru's thoughts on the matter hence the reason why I posted :).
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
Post Reply