SQL Max Record Limit? 2

[davejazz]

Is there a maximum number of records that SQL can handle? We currently have about 120,000 and would like to double that.

 

[SQLBill]

What do you mean by "records"?

If you mean ROWS, I have ONE table that currently has 828,531,441 rows (records). It grows by about 3 million rows a day.

-SQLBill

 

[davejazz]

Thanks, SQLBill, for your reply. It certainly makes sense to me. I'm sure major corporations such as GMC have more than 120,000 records (rows) within their SQL tables just to store inventory items.

 

[JayKusch]

There is no max number of records SQL Server can hold. The maximum number of records is limited only by the available storage. This holds true from SS7 and SS2K.

Thanks

J. Kusch

 

[clarkin]

from MSDN for SQL Server 2k Features(

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_00_3jll.asp):

"
Very Large Database Improvements
SQL Server 2000 has high-speed optimizations that support very large database environments. SQL Server version 6.5 and earlier can support databases from 200 GB through 300 GB. SQL Server 2000 and SQL Server version 7.0 can effectively support terabyte-sized databases.
"

How big is your 800 million record DB?


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[SQLBill]

clarkin,

One database (it has the table with 800+million rows, along with other tables) is currently 258 GB. It grows by about 1.5 GB a day.

-SQLBill

 

[clarkin]

..can effectively support terabyte-sized databases

If that means ONE terabyte sized.. your DB is probably going to break SQL-Server in about 510 days Let us know what happens


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[davejazz]

Getting back to my original question ... it appears the only limit (for SQL) is your storage capacity and the ability of the software accessing it to deal with the size of database it encounters.

We use a highly respected (does M**200 ring a bell?) accounting software program within our mid-size corporation and have started encountering problems as the size of our inventory approaches 120,000 items. The president now wants to double the inventory.

Thanks for all your replies to my original question. Any problems we are encountering do not appear to originate within MSSQL7.

 

[clarkin]

.. have started encountering problems ..

How serious are the problems? BSoD ? Lost/broken data? Accounting software crashes?

Or just it has started to go much slower?

If its the last one, it might be possible to speed up access with good indexing or other performance optimisation.
Doesn't sound like its a table size limitation of SQL Server anyway though!


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[christywarner]

I am an independent developer and have
SQL SERVER 2000 Developer Addition, but
this cannot be used for production deployment.

My question is - what is the cheapest way
for me to create a stand-alone Access front-end
with SQL Server back-end that is completely
re-distributable? I looked at the SQL Server
2000 Desktop Engine (MSDE 200), but the max size
of the database is limited to only 2GB, when I
would ideally like to be able to process
millions of records at a time.

Does anyone know the max table / db size of the SQL Server
2000 Personal Edition?

Any help would be greatly appreciated -

Thanks,
Christy.

 

[SQLSister]

Other problems could be from poor programming practices in your user interface. For instance, I'l bet SQLBill doesn't have many queries using cursors trying to manipulate his many gigabyte database. Cursors often look fine in a development environment with limited records, but very quickly turn out to be very slow once you are in production. His SPs probably never return more data than they absolutely have to either.

Other problems could be: network issues, a computer which is not robust enough to support the number and type of connections, lack of space onthe hard drive due to not backing up the transaction log and a whole host of ther problems.

Christy, personal edition is not sold separately, you have to buy the standard edition of SQL Server. Yes, it's expensive, but that's what you need if you are going to have millions of records. Enterprise would be even better. Also I would not use an Access front end if I were processing millions of records.

 

[SQLBill]

Christy,

Here's a problem for you...

SQL Server back-end that is completely re-distributable?

That's MSDE. If you create your application on anything else, you might not be able to distribute the backend. The end user will have to purchase the proper SQL software and licenses. Or you will have to 'pre-pay' for it and transfer it to the end user.

Check Microsoft's website for SQL purchasing/distribution rules.

-SQLBill

 

[SQLBill]

Christy,

This is from Microsoft (if you chose to go the 'paid' SQL Server way).

SQL Server 2000 Enterprise Edition and SQL Server 2000 Standard Edition are the only editions of SQL Server 2000 that can be installed and used in live (deployed) server environments.

above from:

http://www.microsoft.com/sql/techinfo/planning/ChoosEd.doc

BTW-you should really have started a new thread on this and not 'tagged' onto someone else's thread.

-SQLBill

 

[SQLBill]

Christy,

Here's addtional information from the same paper:

Probably the most significant characteristic of the Desktop Engine is that it is a redistributable version of the SQL Server relational database engine. Third-party software developers can include it in their applications that use SQL Server to store data.

-SQLBill