April 5, 2017 at 7:04 am
Hi everyone,
I'm in the early stages of installing a 2012 instance and am unsure how much space I should allocate for each drive.
Is there a general rule around "if your Data drive is 250gb then make your Log drive 100gb" etc?
As I understand it it's best practice to have five drives:
At the moment I know I have a total of 600gb space and the database is currently 200gb (and grows a small amount each day).
As ever, any help or ideas would be hugely appreciated!
Cheers,
Lins
April 5, 2017 at 7:45 am
I'd say the "general" rule is, have enough space for your data files and log files with some room to grow.
Making up some numbers, lets say your database is 200GB + 100GB Transaction Log. Your small growth per day is ~10GB to the data file, no growth to the log file. You could then, in theory, have a 450GB data drive and a 150GB Log drive. Now, myself, I'd find that log drive a bit "slim" but mainly because I always presume some user / developer is going to run something dumb that causes the Transaction Log to explode in size, and having more space (with storage alerts configured) gives me a better chance of catching the problem before I start hearing "hey, the application isn't working, is the database down?"
The room to grow will largely depend on your needs, if you've got a fast (fast again depends on your requirements) growing database, you'd want more free space. Slower growing (same thing) you could have less.
Additionally, how much free space would also depend on how easy / hard it will be to add more space down the road. If it's a physical server, it may be rather difficult to expand your drive space, so you might want more free space so you can avoid going to additional files / filegroups. If it's a virtual machine, then it's generally possible for your VM Admins to expand and extend your drives (although you'll also likely need to reboot, if not shut down, for this.) If your drives live on a SAN, then I don't know enough about SANs to say if it's easy / hard.
As with many things, the real answer to your question is "it depends." Only you can determine if the disk sizes you use are appropriate for your system.
April 5, 2017 at 7:56 am
Whatever you do, make sure that it's easy to add drive space at the drop of a hat... any hat.
I'd also avoid installing anything of SQL Server on the same drive as the OS.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2017 at 9:58 am
Thanks both for the suggestions; really helpful.
The server will be a development environment, so I am expecting users to do dumb things that could cause rapid growth to the log.
I need to check to see how easy and quick it is going to be to get new space.
But as a first attempt, does this sort of a storage plan seem sensible to start with?
Cheers,
Lins
April 5, 2017 at 10:31 am
lindsayscott23 - Wednesday, April 5, 2017 9:58 AMThanks both for the suggestions; really helpful.
The server will be a development environment, so I am expecting users to do dumb things that could cause rapid growth to the log.I need to check to see how easy and quick it is going to be to get new space.
But as a first attempt, does this sort of a storage plan seem sensible to start with?
- Operating System - 75gb
- SQL Server application - 25gb
- Data - 300gb
- Log - 100gb
- TempDB - 50gb
- Backups - 50gb
Cheers,
Lins
I know not all places can manage this, but personally I always like to have my backups on different storage, be that direct to tape (if you have control to restore as required and your solution allows restores to different locations easily) or preferably a set of separate spindles - as remote as you can get it.
This means if your box/vm goes boom, you can recover to a new one.
You're left desperately hoping that the last time the network team backed up your box (and don't assume they care) was recently enough to keep you in a job otherwise.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 5, 2017 at 10:38 am
Big question, are these separate physical drives? 600GB is not very much to work with for a database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2017 at 11:47 am
andrew gothard - Wednesday, April 5, 2017 10:31 AMlindsayscott23 - Wednesday, April 5, 2017 9:58 AMThanks both for the suggestions; really helpful.
The server will be a development environment, so I am expecting users to do dumb things that could cause rapid growth to the log.I need to check to see how easy and quick it is going to be to get new space.
But as a first attempt, does this sort of a storage plan seem sensible to start with?
- Operating System - 75gb
- SQL Server application - 25gb
- Data - 300gb
- Log - 100gb
- TempDB - 50gb
- Backups - 50gb
Cheers,
Lins
I know not all places can manage this, but personally I always like to have my backups on different storage, be that direct to tape (if you have control to restore as required and your solution allows restores to different locations easily) or preferably a set of separate spindles - as remote as you can get it.
This means if your box/vm goes boom, you can recover to a new one.
You're left desperately hoping that the last time the network team backed up your box (and don't assume they care) was recently enough to keep you in a job otherwise.
+1000000!!! Preach it Brother!!! We went through that when I first got to my current company because of the previous regime. Those were not fun moments. The only good part was that the tape backup hadn't left the site yet. We did loose about 6 hours of development work (it was, fortunately, "just" a Dev box that took the hit).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2017 at 11:58 am
lindsayscott23 - Wednesday, April 5, 2017 9:58 AMThanks both for the suggestions; really helpful.
The server will be a development environment, so I am expecting users to do dumb things that could cause rapid growth to the log.I need to check to see how easy and quick it is going to be to get new space.
But as a first attempt, does this sort of a storage plan seem sensible to start with?
- Operating System - 75gb
- SQL Server application - 25gb
- Data - 300gb
- Log - 100gb
- TempDB - 50gb
- Backups - 50gb
Cheers,
Lins
I can tell you up front that 75GB for the OS drive isn't going to be enough if you do (and you should do) regular Windows Updates. And there's no way that a 50GB drive for backups is going to be good enough for 300GB of data even if you do use compression. 50GB for TempDB is probably serious overkill. My 2TB system has been running just fine on eight 2GB files with no growth for years.
And I wouldn't go cheap here. Compared to the time of Developers, disk space is cheap. If you're going to do some serious development, plan on some headroom to allow experimentation. Set it up for a TB or 2 so that you don't have to mess with it for several years.
And just to emphasize again, 75GB is too small for the OS and 50GB is way too small for backup space. I'd have 1 or 2 TB on another box just for that. Sounds like a lot but you'll be surprised at how fast it goes in the first year or two.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2017 at 5:57 am
Jeff Moden - Wednesday, April 5, 2017 11:58 AMlindsayscott23 - Wednesday, April 5, 2017 9:58 AMThanks both for the suggestions; really helpful.
The server will be a development environment, so I am expecting users to do dumb things that could cause rapid growth to the log.I need to check to see how easy and quick it is going to be to get new space.
But as a first attempt, does this sort of a storage plan seem sensible to start with?
- Operating System - 75gb
- SQL Server application - 25gb
- Data - 300gb
- Log - 100gb
- TempDB - 50gb
- Backups - 50gb
Cheers,
Lins
I can tell you up front that 75GB for the OS drive isn't going to be enough if you do (and you should do) regular Windows Updates. And there's no way that a 50GB drive for backups is going to be good enough for 300GB of data even if you do use compression. 50GB for TempDB is probably serious overkill. My 2TB system has been running just fine on eight 2GB files with no growth for years.
And I wouldn't go cheap here. Compared to the time of Developers, disk space is cheap. If you're going to do some serious development, plan on some headroom to allow experimentation. Set it up for a TB or 2 so that you don't have to mess with it for several years.
And just to emphasize again, 75GB is too small for the OS and 50GB is way too small for backup space. I'd have 1 or 2 TB on another box just for that. Sounds like a lot but you'll be surprised at how fast it goes in the first year or two.
That's really good to know - thanks Jeff. I'm going to start monitoring the db growth each day so I can be a bit more certain about that. Also I need to find out from the infrastructure team if the drives are physical, or a single drive partitioned out (I think it'll be the latter).
Budget is a big concern (as the business is a small /medium NFP) and so will be key to how much space I can get. It's really useful to have those sizes as a benchmark.
Thanks for your help on this everyone.
Cheers
lins
April 6, 2017 at 7:41 am
Jeff Moden - Wednesday, April 5, 2017 7:56 AMWhatever you do, make sure that it's easy to add drive space at the drop of a hat... any hat.I'd also avoid installing anything of SQL Server on the same drive as the OS.
there will always be some core components of sql server that go onto the boot drive, binaries arent too much of an issue, they're loaded into memory at service startup. It's mainly for management purposes to have separate app drive and also to avoid having situation where apps fill the C drve and server BSODs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 6, 2017 at 12:19 pm
lindsayscott23 - Thursday, April 6, 2017 5:57 AMJeff Moden - Wednesday, April 5, 2017 11:58 AMlindsayscott23 - Wednesday, April 5, 2017 9:58 AMThanks both for the suggestions; really helpful.
The server will be a development environment, so I am expecting users to do dumb things that could cause rapid growth to the log.I need to check to see how easy and quick it is going to be to get new space.
But as a first attempt, does this sort of a storage plan seem sensible to start with?
- Operating System - 75gb
- SQL Server application - 25gb
- Data - 300gb
- Log - 100gb
- TempDB - 50gb
- Backups - 50gb
Cheers,
Lins
I can tell you up front that 75GB for the OS drive isn't going to be enough if you do (and you should do) regular Windows Updates. And there's no way that a 50GB drive for backups is going to be good enough for 300GB of data even if you do use compression. 50GB for TempDB is probably serious overkill. My 2TB system has been running just fine on eight 2GB files with no growth for years.
And I wouldn't go cheap here. Compared to the time of Developers, disk space is cheap. If you're going to do some serious development, plan on some headroom to allow experimentation. Set it up for a TB or 2 so that you don't have to mess with it for several years.
And just to emphasize again, 75GB is too small for the OS and 50GB is way too small for backup space. I'd have 1 or 2 TB on another box just for that. Sounds like a lot but you'll be surprised at how fast it goes in the first year or two.
That's really good to know - thanks Jeff. I'm going to start monitoring the db growth each day so I can be a bit more certain about that. Also I need to find out from the infrastructure team if the drives are physical, or a single drive partitioned out (I think it'll be the latter).
Budget is a big concern (as the business is a small /medium NFP) and so will be key to how much space I can get. It's really useful to have those sizes as a benchmark.Thanks for your help on this everyone.
Cheers
lins
If they're "just" going to be internal drives, drive space is incredibly inexpensive even for an NFP. Buy 2 and do a hardware mirror. 😉 See the following...
https://www.amazon.com/Hard-Drives-Internal-Desktop-Components/b?ie=UTF8&node=1254762011
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply