?
Solved

sql server size

Posted on 2010-01-10
16
Medium Priority
?
189 Views
Last Modified: 2012-05-08
Hi,
After installation of sql server 2005, as a DBA how could we estimate the size of the database to be created.
what is the best practise followed in real time scenario .
0
Comment
Question by:dotnet0824
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 57

Accepted Solution

by:
giltjr earned 800 total points
ID: 26279944
Um, well it is fairly simple.  Just add up the total number of bytes needed for the database.

Of course in order to do this you would need to know the logical design of the database, number of tables, number of records in each table, all of the columns in each table, the type of data in each column, and the size of each column.

Here is an MSDN article http://msdn.microsoft.com/en-us/library/ms187445.aspx




0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26279987
This would help you find the size of your database objects, thus helping you to estimate the database size in future..

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1313431,00.html

Note: Free registration is required to view the complete contents in the above link..
0
 

Author Comment

by:dotnet0824
ID: 26280038
lets say we just joined a new set up company as a DBA and we have no data yet then how could we know

1) The size of data files
2) the memory required for the server
3) Number of processors etc.

Any good links available which would explain clearly considering this kind of scenario?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26280049
>>lets say we just joined a new set up company as a DBA and we have no data yet then how could we know <<
Unless you have some idea of the potential size of the tables and number of transactions you cannot know.  All you can do as a DBA is attempt to guage and do your best to make sure that you have built as scalable a platform as possible.  This comes from experience.  No amount of estimates and calculations will help you here.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26280054
>> we have no data yet then how could we know

Ideally you can do an approximate calculation if you know

* how many lookup (Static - No new records) tables are there in your database
* how much records would go into each and every table ( this varies for each and every table)
* how many nullable columns are there in your database tables since that would also disturb your calculations..

In simple words, just work with the person who designed or modeled that Database to get complete information about that so that your calculation is precise...

Without which I would say that it wont be accurate..
0
 
LVL 57

Expert Comment

by:giltjr
ID: 26280125
You can't est. the size of something if you have no details.



0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 400 total points
ID: 26281554
>>lets say we just joined a new set up company as a DBA and we have no data yet then how could we know

1) The size of data files
2) the memory required for the server
3) Number of processors etc.<<

first you have to collect some details about what will be the use of database? what application will be using your database? how many tables will be there? how many user will concurrently access database? there are many more but these are basic.

Once you have an idea about these questions you can estimate the size of database, server capacity etc. until then nothing can be decided.

0
 

Author Comment

by:dotnet0824
ID: 26281668

"first you have to collect some details about what will be the use of database? what application will be using your database? how many tables will be there? how many user will concurrently access database? there are many more but these are basic.

Once you have an idea about these questions you can estimate the size of database, server capacity etc. until then nothing can be decided."


Q) Assuming that we have these details how could we estimate. Is there any examples online .
for eg : lets say as per what you say  there are 100 Tables, 20 concurrent users etc then how. I am looking for an example just to get a fair idea.





0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 26281687
well, this is not something I can explain in one answer as it depends on so many factor, even you can get an idea from below link.

http://sqlcommunity.com/Blogs/tabid/70/EntryId/28/How-to-estimate-disk-space-needed-for-SQL-Server.aspx

BTW, you said you may have 100 tables and 20 users. this is not enough, how many fields of those tables? what would be the datatype and what would the growth rate of data approximately etc. are needed.
0
 
LVL 57

Expert Comment

by:giltjr
ID: 26282463
You are missing so much data.  You can't even being to try and guess at what you need.

Is this for real job?  Or is this some type of intro to database class?

If this is a for a real job and they really expect you, as a brand new DBA, to be able to est. the size and performance requirements or a database knowing nothing about the database or its usage, then RUN, RUN as fast as you can away from there.  

If this is for a class, just let us know and we can guide you, but a lot of the links that have been provide do give you the basics of est. database sizes.
0
 

Author Comment

by:dotnet0824
ID: 26283939
well I would be going ahead within few months as a Jr DBA, so just wanted to know about how in real time these things r tackled considering database design right from the scratch. What r the guideliness to be followed regarding estimation etc before creating the DB. Hope its clear now.
0
 
LVL 57

Expert Comment

by:giltjr
ID: 26284501
Well the links provided give you the basics.

You start with table sizes, which is based on the the table design.  The size and data type of each column and est. number of rows.  Then you can est. the size of the index for the table.  You do need to include free space for a table that is updated a lot.

Once you have the size of each table and its index (including free space) then you just add them together and you have the database size.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 800 total points
ID: 26292599
>> What r the guideliness to be followed regarding estimation etc before creating the DB.

In this case, you need to calculate the

* Estimated size of your tables.
* Estimated size of your Clustered Indexes.
* Estimated size of your Nonclustered indexes.

And few links to start out since you are new to this:

http://msdn.microsoft.com/en-us/library/ms187445%28SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms175991(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms189124(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms178085(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms190620(SQL.90).aspx
0
 

Author Comment

by:dotnet0824
ID: 26294915
ok thanks. finally what is the number of  user concurrent connections to do with database size? can it be explained pls.
0
 
LVL 57

Expert Comment

by:giltjr
ID: 26295024
Nothing.  

But you also asked about memory and processors.  These deal with performance and performance is not just related to the size, but also the transaction rate, which is typically related to number of concurrent users.
0
 

Author Closing Comment

by:dotnet0824
ID: 31675293
Thanks guys..
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question