Determining sizes for Separated Partitions of programs, data and logs in SQL Server

Question

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

Answer 1

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 & 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.

Answer 2

If you have a test system, do some load testing and monitor the file size growth. The log file size growth depends on your recovery strategy. If you do not need point in time restores, then you can switch to simple mode and this will keep the size of your log file the smallest of the recovery models. To set the DB in simple mode, in SSMS>right click your DB>Properties>Options>Recovery Model. Set this this to simple.