September 17, 2009 at 9:18 am
Hi
When deciding on disk size on a server do you use any formulas for disk size.
I will have 3 disks one for data one for logs one for backups (and one for OS and perhaps another for tempdb if the budget is right).
Say my data disk will be 250gb and I want 3 days worth of backups and most dbs will have Full recovery.
How do you work out the size for backup and log file disks?
This is for SQL Server 2005 with no compression or 3rd party backup utility used.
I'd be interested to hear your suggestions.
Seth
September 17, 2009 at 9:52 am
Seth Lynch (9/17/2009)
HiWhen deciding on disk size on a server do you use any formulas for disk size.
I will have 3 disks one for data one for logs one for backups (and one for OS and perhaps another for tempdb if the budget is right).
Say my data disk will be 250gb and I want 3 days worth of backups and most dbs will have Full recovery.
How do you work out the size for backup and log file disks?
This is for SQL Server 2005 with no compression or 3rd party backup utility used.
I'd be interested to hear your suggestions.
Seth
How big are your current backups? The simple answer is really multiply a daily backup by 3, but don't forget to factor in growth. If for example, your databases are growing by 1GB per day, then factor in the increase in growth.
As for the logs you will need to factor in tasks other than daily load. For example, if you took a log backup every hour over a 24 hour period and the average log size was 200MB, you'd have 4.8GB of logs. However, if you performed some defrags on indexes or had to perform some data loads, you could find your logs growing. So rather than allowing for 4.8GB x 3 days, you will want plenty of breathing room.
And keep monitoring the space on your drive. Trend the growth of data and log backups so you give yourself some advance warning of when you may need to grow your drives. The last thing you want is to come in and find out your backups did not run because you filled the drive.
September 17, 2009 at 10:47 am
Hi Seth,
what I use for backup compression is LiteSpeed. It's a 3rd party tool that compress and encrypts the backups pretty good... I've seen SQL backups of 15 GB become 3 GB using litespeed.
Also, as Clive mentioned, it is quite important to know the estimated growth of your databases. There are some ways to predict how big your tables will be based on the data type and length. You can browse the web for the calculations. Of course, the key factor for that is rows inserted per month, year or whatever time period you want.
cheers,
Alejandro
Alejandro Pelc
September 18, 2009 at 2:03 am
/****Sorry for multiple replies - the server timed out and I thought it had failed then it put it through twice while I was re-entering it*****/
Thank-you both for your replies - they are useful . We used to use litespeed at my last job and it was very good for compression.
My 'problem' here is a lack of information about the databases. We have one or two that are on SQL 2000 and need to be moved to 2005 - for these I know how big they are and growth rates etc.
We have no SQL 2005 server so I need to spec one to accommodate all future dbs we create (in house applications) as well as the few that are being upgraded.
So I am in the dark about growth etc. The disks will be on the SAN. Using rough guides based on our pother specs 250gb will accommodate the data files. This includes a healthy bit of breathing space and allows for any new large app which may come along.
The Logs will generally be small and backed up every hour. For most of these Full recovery mode is not needed but... They tend to do a few user triggered imports through the day and once there was a PK conflict. There were no conflicts in the database after the (import once the pk was dropped) and no conflict in the source db - they conflict occurred due to the way it update the records. I was able to sort this out by rolling the db back to a minute before the import and running it again. So I leave most dbs in Full recovery for my benefit mostly.
For backups I normally keep 3 days worth on disk - they go to tape nightly too.
So without much hard data I was hoping to fall back on some formulea like Data:Log:Backup 2:1:2
Of course - if I get the backup side wrong I can then use litespeed of Redgates backup for compression.
Seth
September 18, 2009 at 2:06 am
deleting long multi entry
September 18, 2009 at 2:14 am
Delteing long multi entry
September 18, 2009 at 2:33 am
Don't forget, when using maintenance plans, they do the backups before deleting the old ones, so you end up with more disk space being used for a while.
That probably won't make much difference for the transaction log backups, but if you do a full backup daily and keep it for 3 days, then immediately after the backup phase, you will have 4 full backups on disk.
September 18, 2009 at 2:51 am
That's true (and I have been caught out by it before) - these days I write my own plans.
As I have backups on tape I delete the oldest on the server and then make a new backup.
I find the builtin maintenance plans a bit of a pain all in all - like T-log backups that try to run on every database even if they are in Simple recovery mode. Although they are better than nothing.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply