division of db sizing in terms of temp, log, data

I have a client asking the following Please specify the division of db sizing in terms of temp, log, data for database assume a 15 GB database
more ▼

asked May 14, 2012 at 07:05 AM in Default

Srinivas Govada gravatar image

Srinivas Govada
22 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It depends. There, I said it.

The data file itself will be there to contain the data. The size depends on the amount of data being held. I'm sure there are tools out there to help you size a database given the table structures and numbers of rows of data etc.

The log file is there to contain changes to the data before they are backed up. The size of this depends upon the sort of queries being run - a simple SELECT statement shouldn't affect the log size, but any data-changing activities (UPDATE, INSERT, DELETE) and object creation / alteration etc.

The tempDB also has data & log segments; again, the use of this will depend upon the query load that your database/server experiences - more complex queries will make greater use of the TempDB than simple queries.

So, without knowing (a) the sort of data, and (b) the sort of use, you can't specify this.

Some reading material for you:

[2]: http://www.sqlservercentral.com/articles/Administration/sizingadatabase/383/
more ▼

answered May 14, 2012 at 07:43 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left

Without tons more knowledge, how many users, what anticipated load, how many transctions, what size transactions, etc., you can't know how to estimate size & growth. However, with 15gb of data, I'd make the data size about 25gb to allow for growth (these are very small databases, so you don't need to worry about multiple files and file storage yet, but additional information could change that). I'd make the log about 5gb, and that assumes full recovery and a regularly scheduled log backup (every 15-20 minutes). Tempdb... that's harder to size, but probably around 10gb, assuming no other databases on the system. Sizing tempdb is completely different than sizing a single database, so that's just a complete SWAG (silly wild-a***d guess).

But to do this correctly, you need lots more information than just the current size of the data.
more ▼

answered May 14, 2012 at 12:15 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 14, 2012 at 07:05 AM

Seen: 984 times

Last Updated: May 14, 2012 at 12:15 PM