Taking care of your health is incredibly important (says the marathon runner). Eating right, exercising, and getting plenty of rest help you stay healthy and perform optimally.
Your database server’s health is important to your company’s health. You need to take regular backups, check for consistency, and keep indexes tuned for optimal performance. Here are a few tips to make sure your database stays in top shape.
Back up System Databases
The master, model, and msdb databases on every SQL Server are integral parts of the system.
Master contains information such as logins, linked servers, and information about all other databases on the server. Without this database, SQL Server can’t start up. If you don’t have a current backup of this database and a disaster occurs, you lose this information.
Model is used as a template for every new database that is created on your system. There are certain default settings you may adjust, such as file sizes, autogrowth, and the recovery model. If you don’t take a backup of this and a disaster occurs, when you set up the server again you have to remember to reconfigure all of these settings, rather than restoring them.
Msdb contains all of the information about SQL Server Agent operators, jobs, and alerts. It also holds backup and restore history tables. If this isn’t backed up and a disaster occurs, you will need to recreate all of your Agent items. I had to do this once – it was one of the worst weeks in my first DBA job.
Ensure that your backup job covers these databases, because in the event of a disaster, you want to restore them rather than rebuilding all of that information.
Run DBCC CHECKDB On All of Your Databases
You need to run DBCC CHECKDB to check for consistency errors in your database –yes, corruption. If you’re not running this on system databases, or only running it on “important” databases, you run the risk of not finding corruption in a database.
As already explained, the system databases are incredibly important. Combine not running DBCC CHECKDB with not taking a backup, and you can have a disaster without a hardware failure of any sort.
Only checking those databases you feel are “important” is dangerous. Who defines “important”? Is archived data as important as live data? To the person that uses that information for forecasting and trending, it is. Is a system used for reporting once a quarter as important as production data? To the person that has to use those reports to file taxes, it is. If a database is truly “not important”, and isn’t being used, it should be backed up and removed from the server – not sitting out there, taking up space and resources.
Index Maintenance
Indexes are the closest thing to a turbo button that SQL Server has. Proper indexes on tables will help your select queries run faster, and won’t hinder the performance of inserts, updates, and deletes. But putting an index or four on a table when it is released to production, then doing nothing further, is not productive. Table structure, the data in the tables, and the queries from users and applications change over time.
As data is added, updated, and removed, the pages in the index will become fragmented. Fragmentation leads to poor performance. The more scattered across the disk the data is, the harder storage has to work to retrieve it. Regular, consistent reorganization and rebuilding of the indexes can combat this.
Are you wondering where to start? Ola Hallengren has a brilliant set of maintenance scripts, including index maintenance, which can help you out!
Keep Your Server Healthy
By performing regular maintenance on your SQL Servers, you’ll ensure that your data is secure and it is performing optimally. Much like you don’t want to wait until you suffer a heart attack to see a doctor, you don’t want to wait until a disaster to find the weak points in your server.
14 Comments. Leave new
Hi,
we take a daily backup of our system databases, but I’m wondering if that is enough? I guess for model that will be more than enough as we rarely change that, but w/r/t master and msdb would you recommend more frequent backups?
Richard, that’s usually enough, unless you make very frequent changes to your databases, logins, and jobs. If you make any major changes, such as adding a new job, it doesn’t hurt to take an extra full backup at that point in time.
Hey Jes, good article.
I’m working (My 2nd week) on this place and i’m working on this database that is 120Gb and all tables (yessss all table) are heap. now my manager wants to add a clustered index to a table that has 10500567 record on it. How do you think i should add a clustered index to this table without running out? thanks.
Best practice, not on this place. all users have directly access to tables and 23 users (not DBA’s) can remote into the server box and make changes to it.
the pay is good but i think that i might have to run away sooner or later. or it should be i good experience.
the dba that was here was a senior dba but i don’t see from where or how? the more access to different boxes that i have the more well the more i’ want to cry.
Good luck with the new job! Your question about adding a clustered index – what are you worried about running out of?
hi Jes.
im working on a heap table that have 48000000 millions rows on it.
i need to create a clustered index on it because the table is 99.% fragmented. i’m concern if i create a new column with a clustered index i might do something wrong with the data? or should i drop and create a new table with the clustered index on it. ?
thank you.
What you need to know about creating a clustered index on a heap: you’ll need free space in your database, or room for the file to grow – at least enough space for the old (heap) and new (clustered) data. You may also need some space in tempdb for sorting. The process can take some time. Any nonclustered indexes that exist on the table will need to be rebuilt so they include the clustered index key.
You shouldn’t have to worry about losing data. A copy of the data is retained until the new clustered index is built.
The best thing I can recommend: test it in development! This will give you an idea of how much space you need and how long it will take.
Thank you Jes for replying back to me.
Just the last Q on this issue, i’m planning to work on this issue over the weekend just to have a good window time.
I was thinking to create a new column first, add a identity Increment # to it.
Once the new column is populated i can make this column a clustered index and i should be good.
All Tables are been created before i got here. 5 tables have more than 105 millions records on it and none of the columns are a good candidate for a clustered index. That’s why i was thinking about the new column(e.g. ((id) int null) instead.
Your opinion counts. Thanks Jes.
Hi Jes,
Great blog.
I have thankfully never had to restore a system database. (knock knock)
But should the need arise, I would like to know what to do.
When you said about the Master DB “Without this database, SQL Server can’t start up.”
Does this mean you have to reinstall the instance then restore the Master DB or is the another trick to restore it on the existing instance?
What are your thoughts on the Distribution DB?
Cheers
Dennis – Start by checking out the Books Online section on restoring the master database:
http://msdn.microsoft.com/en-us/library/ms190679.aspx
When you say “What are your thoughts on the Distribution DB”, can you be more specific?
Always a good place to start. 🙂 MS is pretty clear on how to do that.
As for the Distribution DB:
Have you guys come across any unexpected issues when restoring? Things that occur due to a misconception in the backup strategy or something else? Easy things to forget after it is restored?
Those sort of insights.
Gotcha – yeah, that’s a little beyond something we can address quickly in a blog comment, sorry. Replication is a huuuuuge feature with all kinds of unexpected issues! 😀
How about backup and restoring SSAS databases? Just yesterday I found out we have backups of everything, but nothing for SSAS.
Reason for asking: Our SSAS project was not really a project… And yes I made the mistake of editing while thinking it was offline, but in reality it was (very big sigh) onlline. And now it is all gone.
Is a maintenance plan using the task ‘Check Database Integrity’ in essence running DBCC CHECKDB? Or should DBCC CHECKDB be run separately?
It’s checkdb.