The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Access vs. SQL Server disk consumption

I just finished collecting 2 weeks worth of data in an Access database, and the size is 106 megs.  I am obviously going to use SQL Server, but I just upsized the Access database, and it appears the SQL database is using 230 megs.  Why is SQL using much more disk space then Access?  And can I expect in 1 year for the SQL size to be 6 gigs?
J. B. Send private email
Saturday, April 08, 2006
You don't mention if the size increase is simply in the Data files for the SQL Server database or if it is the combination of Data files + Log files.

Since you just upsized, you have all of the transaction records for the data you just imported in the Transaction Log file.

Is 6 GB for a year of your data too much?  IOW, why are you concerned about 6GB for a year?  Many databases have more than that every month, week, or day depending on the use.

The size won't impact performance very much (6GB is still small for a database depending on what you are tracking) if you have proper indexes in place for retrieving the data.
Saturday, April 08, 2006
I am not really that worried about the size, but I do have to figure out how will the database grow, as I am preparing a business case.

The figue I gave you, you can times that by 2.5, because I did not include all the data that will be going into it.

It total, every 2 weeks there will be approximately 770,000 records.

So how can I get the SQL Server size to a point now, where I can get some sort of ball park estimate as to the size it will grow each year?
J. B. Send private email
Saturday, April 08, 2006

"In total, every 2 weeks there will be approximately 770,000 records."

This doesn't really tell us anything. If a record is one byte long, then the DB grows by 770KB every two weeks.  If a record is 1,000 bytes long, then the DB grows by 770MB every two weeks.

How many indexes do you have?  This will also affect storage usage.

Also, you only mention one table.  What about the other tables in the DB?

You also have overhead in your Access DB because unless you split out your application code to a separate DB, all of your forms, reports, etc. are stored in the file.  In SQL Server you don't have this overhead.

These are only a few of the questions you need to ask before you can make an intelligent estimate of storage requirements over time.
Karl Perry Send private email
Saturday, April 08, 2006
Hi Karl

Thanks for your help here.  It has more then 1 table, the 770,000 records is a total of all tables. 

I don't have any indexes done yet.  I am just trying to get a ballpark figure of the disk space needed so I can include that on my business case.  Basically there would be 4 main tables, with 4 - 6 indexes in each, and I am just guessing at this point.

So, if I can get the current size of 230 megs with 2 weeks of data down to it's proper production size, I think we can come up with some sort of ball park estimate.
J. B. Send private email
Saturday, April 08, 2006
Just a quick note, I didn't use the upsize wizard, I just imported the tables and data using the SQL import data wizard.
J. B. Send private email
Saturday, April 08, 2006
Hi Jared

The data file is 100 megs and the log file is 136 megs.  So what would be the ratio going forward with normal inserts?
J. B. Send private email
Saturday, April 08, 2006
Regarding log files... any time a record is inserted the full information is stored to be able to replicate the insert if the log needed to be applied to a backup file to bring a backup into working order.

All updates and deletes store information necessary to perform them over again for the same reason.

BUT, you don't need to maintain the full log all the time (depending on your backup procedures) so this file, that is 136 MB can shrink if you take the proper steps.

Sounds like you need to read on SQL Books Online for some additional information on backup/restore strategies, etc.

It isn't just a copy and paste a file like with Access.

Think of the log like a working file that can be cleared out from time to time as backups of the data are taken and moved to other storage.
Saturday, April 08, 2006
Hey J.B.

If I were you, I  would think about the fact that if I use access for the 14 lakh records monthly in just a couple of months the database will be too slow and therefore useless.

SQL Server on the other hand can handle gigs of data. Also disk space is cheap these days. Data availability is more important. I think you should just put in the cost of having lets say a 200 gb harddisk and another for backups and thats about it.
Amit George Send private email
Saturday, April 08, 2006
OK, thanks for you help.  I just submitted the business case, omitting an estimate on the annual disk space usage, since disk space is cheap and a 200 gig hd would last years.  Thanks again.
J. B. Send private email
Monday, April 10, 2006
How about inserting 770,000 dummy records and seeing what happens? ;-)
codechef Send private email
Tuesday, April 11, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz