Hello there

In one of my clients who have SQL Standard has past the 2Gb data file.
In this office there are approxsimatly 80 users

Since then a lot of problems have happened in the database:
1. tables are locked more often and we need to reset the sql server at least
once a week (Very bad)
2. when we update one data another data is being deleted
3. the workflow of the program has been damaged and all the applications
that use sql server (Access and VB)
    become much more slower.

Does this sideffects are the result of the limitation of Sql standard? (up
to 2Gb and more then 50 users)

If so, can i get official documentation of microsoft about this limitation ,
so i can call my client and tell him that is need to upgrate the sql server
to Enterprise edition?

Roy,shalom

Database that has 2GB nowadays is really small.

I'd recommed you start  tuning your queries  ,making sure that you have
appropriate indexes define on the table

That has nothing to do ( in your case) with SQL Server Standatd Edition



Show quoteHide quote

Roy Goldhammer wrote:
Show quoteHide quoteYou are mistaken. There is no set limit of 2GB or 50 users for Standard
Editon. I'd say that 2GB is small for a Standard Edition installation.
The documented maximum capacities are here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

Performance issues are usually caused by poor design or maybe by
underspecified or poorly configured hardware. See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587

What does "locked" mean?  Can you describe the actual symptom?  Are you
using optimistic concurrency?  Are transactions being orphaned?  Are you
running select * from huge table in the default isolation level?

This sounds like an application problem.  SQL Server is not nearly as adept
at corrupting data as Access once was.  There's no way that SQL Server is
suddenly deciding, "hey, your database is now bigger than 2GB, so I'm going
to update this row you told me about, and delete this other row over here
behind your back..."

Slow usually points to problems in the design, such as indexing strategies,
normalization, etc.  I have 800GB databases that are pretty fast.

None of these problems could possibly have been caused by merely passing a
2GB size threshold.  As Uri commented, 2GB is pretty small by today's
standards.  80 users is not all that impressive, either.

There is no such limitation in SQL Standard.  I have much larger databases
with more people using them and the symptoms are not as you describe.  My
guess is that it comes down to the design.

I'm not sure, but I think SQL Server 2000 Standard is limited to 2 GB of
RAM, but that's still as large as the total size of your database. Other
than that, I don't know of any scalability limitations with the Standard
edition. You mentioned 80 users of this system, but how many average and
peak (concurrent) users?

The symptoms that you are describing below (blocking processes, unexplained
deletion of rows, "damaged workflow?"), actually sound like issues with the
application or query / stored procedure design not the scalability of the
database. Using techniques from the links below, take a more systematic
approach to auditing the performance of your database server and analyzing
the programming of your applications.

Checklist: SQL Server Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetcheck08.asp
How to Perform a SQL Server Performance Audit
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Optimizing Performance in SQL Server Solutions
http://msdn.microsoft.com/SQL/2000/learn/perf/default.aspx
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm

Specifically consider the following:
1.    Are users running reporting type queries (daily / month end close,
Excel pivot tables, OLAP, etc.) against the operational transaction
processing (OLTP) database? If so, then consider implementing a seperate
database (replicated or restored from backup) for the purpose of running
reports.
2.    Use the Show Execution Plan of Query Analyzer to determine if  your
indexes optimized.
3.    Are your stored procedures using cursors? If so, then consider finding
set based alternatives.
4.    Are your applications using recorsets with pessimistic record locking?
If so, then consider how this is impacting concurrency with other processes.


Show quoteHide quote

Evaluation of conditions
complex and large triggers
Table design question on LARGE table
Query help
Any harm in adding a primary key...