Backup and Maintenance strategy - Looking for feedback
-
Hi there,
I'll try to make this short. I'm a developper in a small startup company which has been givin the responsabilities of a DBA. I have lots of experience with managing the structure of a database, but maintenance and backups, not really! I'd like to explain how our database works and what i plan to do for backups and maintenance. If it's not too much to ask, i'd like the feedback and comments of your experiences in the field.
Our database stores large amounts of data in 1 primary table. The rest of the tables are basically clients info, users, etc...stuff that dont grow as much. We currently insert about 1 million record every 2 weeks, and that number is growing quickly as our company gets more and more clients. With that many inserts, the data and transaction log grows pretty fast too. Right now we have 6 months worth of data, but soon i'll have to devise a way to remove the older data and only keep a few months worth. Even though right now our data is only being inserted into the database (no updates to the main table), eventually i'll have a whole bunch of deletes also. So i'm assuming this will fragment the data file a whole bunch. Our data is critically important, so i need to be able to restore to the closest possible point at the time of a crash. I'm set in Full recovery mode.
For now here's what i had planned for my backups
-Run a full backup every 3 or 4 days
-Run a Differential backup every 6 or so hours
-Run a Log backup every 15 minutes
-Re-Index once a week
Currently our data file is quite large and so is the transaction log file. The reason, cause we we're not doing many backups and the files grew to where they are now. Now a few question about when i run the DDBC Shrinkdatabase. So many different posts and comments, it's hard to tell what's true or false. Is is safe for me to run DDBC Shrinkdatabase on my database with the recovery plan i have? And when is the best time to run a ShrinkDatabase, before of after a full backup?
One more thing, I'm assuming that the large amount of insert and deletes in my database will fragment the data files. How do you defragment a data file? With DDBC SHRINKDATABASE NOTRUNCATE? From what i read, the command would re-arrange the file and not free up the space. Sounds like what I need, or is it? Should i run this often?
Any other comments about what I should be doing to maintain the database? I appreciate all the help and feedback you can provide me. I hope i'm on the right path!
Chris
-
Hi Christian,
Welcome to DBA community.
After looking at your initial backup plan. Here is my suggestion
Run a full backup every 3 or 4 days
Instead of reaching to conclusion of full Backup every 3-4 days,
Better you observe the average time Full Backup takes and
Based on that try to see if you can take the backup once every day.
(Scheduling a job to run Midnight should be fine)
Backup is online process and will not hurt or lock DB and when scheduled
in slow time there should be nothing to worry about.
Advantage to this approach is when you have to recover or in Disaster recovery
Scenario, you will end up with restoring one day worth log (even less than that)
And recovery will be fast. Plus Full backup is much more reliable than 3-4 days worth transaction logs. Differential backup is still option that you can use once a day in between 2 full backups.
In addition to this I have written few post here previously which you may find helpful
Restore Master DB 12/19/05
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=245142#bm245274
Log File Improper Growth
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=243730#bm244182
RE: Log File growth big problem
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=233415#bm234492
Link to Good Article for Admin masters
Worst practices for SQL Server backup and recovery
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1122275,00.html
Free Disk Space
http://databasejournal.com/features/mssql/article.php/3080501
Hope you find this a good start.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Chris
Just to add to what Sameer said (apologies if I'm just repeating what's in the links he gave):
Don't shrink your database or log files if they're only going to grow again. It uses server resources and causes external fragmentation.
The insert and delete activity will cause internal fragmentation in your data files. You can see the extent of this using the DBCC SHOWCONTIG statement. Rebuilding your indexes (clustered and non-clustered) will remove this fragmentation. One of the most efficient ways of doing this is to drop the non-clustered indexes, rebuild the clustered indexes, and then recreate the non-clustered ones. Obviously you will want to do this out of hours since it will lock tables.
John
-
Yeah, I wasn't planning on shrinking file at periodic intervals. I only wanted to shrink it if it got out of hand (after setting a size I think can hold all the transactions and current data between backups and archives) So this was only information for a process i plan to do a couple times a year.
As far as Re-indexing and fragmentation...when a table gets tones of inserts and deletes, obviously it will get fragmented. Does the Re-indexing actually defragment the table or only the indexes? I'm asking this cause i read an interesting post yesterday that states: If you run a re-indexing command, it defrags your data, but if you run DBCC SHRINKDATABASE NOTRUNCATE after the re-indexing (to move the data pages to the beginning of the file), you simply end up refragmenting your data (or the indexes). Now the thread wasn't complet, so it left me with the most intreeging questions...like...
-does re-index defragment the data or indexes, or both?
-and does DBCC SHRINKDATABASE NOTRUNCATE defrag the data?
-would running the DBCC Shrink before, and re-indexing after correct the problem?
-Is it good practice to defrag the data as well as the indexing? or should i stick to indexes only?
These question are all related to me seaching for a good maintenance procedure so our database doesnt' suffer too much from lack of real DBA skills.
One more thing I wanted to know about the shrink command, i just want to make sure i dont break my backups. When i run the DBCC SHRINKDATABASE (with and without NOTRUNCATE), does this break my Full/Diff/Log backup strategy? Meaning, am i suppose to do a full backup each time after i run the shrink command? Again, this comes from threads i read, stating that running the command moves the data pages around and restoring diff. and trans. log backups require a certain pointer to be able to execute your restore correctly. And that pointer is lost after the shrink command. I would assume this to be true for the shrink command which does shrink the size, but i'm not sure if it affects the shrink command using the NOTRUNCATE command.
As far as backups, i think i have things well in hand for now.
-
Chris,
One friendly an truely generous note.
When you are requesting details and members are trying to help you
with their thoughts and experience in this great knowledge base, I request
you to use some courteous words, say thank you and similar words, before
dumping questions straight.
Not for my sake but few Gurus writing here charges real fortune when you
consult them professionally.
Please forgive if this hurts you in anyway.
Really big people are, above everything else,
courteous, considerate and generous
- not just to some people in some circumstances
- but to everyone all the time.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Thanks for the advice, and i dont feel hurt in any ways by your comments
You dont have to worry, i'm usually courteous and very gratefull in forums. I mean i've been dealing with forums and discussion boards more than half my life. Since this was an ongoing discussion, i felt i would thank all who helped me in a later post. Plus i was in a bit of a hurry this morning writing the reply. But i'm not here to make excuses.
If anyone felt offended, you or John, i'm sorry and I do appreciate the help.
Chris
-
On shrinking, it should only be in the event something got out of hand, as you mentioned. don't run it if you have fragmentation, only if you run some transactions that are one-time events and make things grow unnecessarily. Even then, if you haven't exceeded the size you set for your files, don't shrink.
Shrink should have no effect on your backups. the truncate options for "BACKUP" do.
The tables may or may not get fragmented on inserts. Depends on your fillfactor, which indexes, etc. You should have a clustered index, which means a rebuild would defragment.
Also some info on fragmentation -
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/28/649884.aspx
-
Thanks for all the help
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply