Determining sizes for Separated Partitions of programs, data and logs in SQL Server As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.

Question is, what is the recommended way to go on about that, especially with regards to how size should be divided between them? If you wanted to do that, how'd you set it?

Thanks
Question is, what is the recommended way to go on about that, especially with regards to how size should be divided between them? If you wanted to do that, how'd you set it?
  


The programs itself won't take much space. You need to make sure the default locations are updated to reflect the data, log and backup locations.

Coming to the size of data &amp; log, you need to do some estimates on the size of clustered index + non-clustered indexes and use the potential estimates for data growth over the next n time period. Estimates may NOT match with the actuals but that is the right way to do it for the data file size. The below links will help.

http://msdn.microsoft.com/en-us/library/ms178085.aspx

http://msdn.microsoft.com/en-us/library/ms190620.aspx

For the size of the transaction log, there are some defaults used by few consultants that log should be this many times of the data file but that is NOT a good way to do it. You will either make the log smaller or too big for your needs if you follow that route. The right way is estimate the largest transaction in your database and keep the log with some head room for that and watch for the transaction log size autogrow event. If the log grew after sometime then you need to make the log bigger than that size and watch it for some more time. It takes few iterations but that is the right way to do it. What is the largest transaction? That depends and most likely it may be the index rebuild of your largest table. Hope this is a good starting point for you, let me know if you have more questions/comments on this.