2GB Limit in MSDE 2000

I have a database that has reached the MSDE 2GB limit.
I have tried running the sp_spaceused command and I can't see how the
results equalls 2GB
Can someone explain what values are used by MSDE to determine that the
database has reached the 2GB limit?
Sp_SpaceUsed Resuls:
Database Size 1903.38MB
Unused 0.16MB
Reserved 1947864KB
Data 1038296KB
Index Size 204584KB
unused 704984KB
Hi
Database Size 1903.38MB
If you have it set to 100Mb autogrow, it can't grow any more as it would
exceed 2GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB
|||What are you storing in the database that has pushed it to this limit?
Pictures? Documents?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB

2GB Limit

What happens when an SQL2K MSDE database reaches 2GB? I've actually seen
MSDE databases reach that size and nothing happened.
Thanks,
Ademar.
hi Ademar,
Ademar Nunes wrote:
> What happens when an SQL2K MSDE database reaches 2GB? I've actually
> seen MSDE databases reach that size and nothing happened.
when you exceed that limit, nex time the Storage Engine requires a file
allocation beyond the allocated space, thus trying expanding the data file,
an exception will be raised..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Maybe the 2GB+ installation I'm referring to is actually a Personal edition
and not MSDE.
I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
tell me if it is MSDE.
Thanks,
Ademar.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3o8jmjF4mu4pU1@.individual.net...
> hi Ademar,
> Ademar Nunes wrote:
> when you exceed that limit, nex time the Storage Engine requires a file
> allocation beyond the allocated space, thus trying expanding the data
> file, an exception will be raised..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi,
If it is Personal edition; there is no db size limitation. See details in
below URL
http://msdn.microsoft.com/library/?u...asp?frame=true
Thanks
Hari
SQL Server MVP
"Ademar Nunes" <anunes@.myemail.com> wrote in message
news:%23VOAP38sFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Maybe the 2GB+ installation I'm referring to is actually a Personal
> edition and not MSDE.
> I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
> tell me if it is MSDE.
> Thanks,
> Ademar.
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3o8jmjF4mu4pU1@.individual.net...
>

2G limit in MSDE

I am aware that there is a 2G limit in MSDE. However in some of our
production databases we are running out of space. There is a long term plan
to upgrade to SQL2005 to double the space available to us. However in the
short term is there a quick way to have more space available ?
I could create a second database and then link a table / view back to the
original for example. Or would I be able to create a second filegroup which
would have another 2 G ?
Any ideas ?
Si
Ah, what are you storing in the database? Don't tell me... let me guess...
ah... it's coming to me...
PICTURES! I got it right?...No? Documents?
These are BLOBs. If you want to save space, don't store the BLOBs in the
database--put them on CDs (if they are RO) or on other drives and put the
path and attributes in the database. File IO can far faster (6 to 10x) than
SQL query IO. Yes, it would require a change in your code, but it's not that
much trouble...
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:FA3B4343-37BD-4B74-8EF4-EFFA21EC7B66@.microsoft.com...
>I am aware that there is a 2G limit in MSDE. However in some of our
> production databases we are running out of space. There is a long term
> plan
> to upgrade to SQL2005 to double the space available to us. However in the
> short term is there a quick way to have more space available ?
> I could create a second database and then link a table / view back to the
> original for example. Or would I be able to create a second filegroup
> which
> would have another 2 G ?
> Any ideas ?
> Si
>
|||Check out reindexing with a smaller fill factor.
--DatabaseAdmins.com
Remote DBA Services
"William (Bill) Vaughn" wrote:

> Ah, what are you storing in the database? Don't tell me... let me guess...
> ah... it's coming to me...
> PICTURES! I got it right?...No? Documents?
> These are BLOBs. If you want to save space, don't store the BLOBs in the
> database--put them on CDs (if they are RO) or on other drives and put the
> path and attributes in the database. File IO can far faster (6 to 10x) than
> SQL query IO. Yes, it would require a change in your code, but it's not that
> much trouble...
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:FA3B4343-37BD-4B74-8EF4-EFFA21EC7B66@.microsoft.com...
>
>
|||Bill
do you know much about the varbinary method for storing documents?
I read somewhere that you can stored docs as varbinary instead of image and
it's a lot lot lot faster-- but it only works with small docs
thanks
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:ejV$Kw0XHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Ah, what are you storing in the database? Don't tell me... let me
guess...
> ah... it's coming to me...
> PICTURES! I got it right?...No? Documents?
> These are BLOBs. If you want to save space, don't store the BLOBs in the
> database--put them on CDs (if they are RO) or on other drives and put the
> path and attributes in the database. File IO can far faster (6 to 10x)
than
> SQL query IO. Yes, it would require a change in your code, but it's not
that
> much trouble...
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---[vbcol=seagreen]
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:FA3B4343-37BD-4B74-8EF4-EFFA21EC7B66@.microsoft.com...
the[vbcol=seagreen]
the
>
|||partition your older data into an archive table-- in a different database
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:FA3B4343-37BD-4B74-8EF4-EFFA21EC7B66@.microsoft.com...
> I am aware that there is a 2G limit in MSDE. However in some of our
> production databases we are running out of space. There is a long term
plan
> to upgrade to SQL2005 to double the space available to us. However in the
> short term is there a quick way to have more space available ?
> I could create a second database and then link a table / view back to the
> original for example. Or would I be able to create a second filegroup
which
> would have another 2 G ?
> Any ideas ?
> Si
>
|||My tests (albeit unscientific at times) shows storing BLOBs (in whatever
datatype) in the database is 6x slower than storing the same data on a file.
Your mileage may vary.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Aaron Kempf" <akempf@.dol.wa.gov> wrote in message
news:eBBZVECjHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Bill
> do you know much about the varbinary method for storing documents?
> I read somewhere that you can stored docs as varbinary instead of image
> and
> it's a lot lot lot faster-- but it only works with small docs
> thanks
>
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:ejV$Kw0XHHA.3952@.TK2MSFTNGP04.phx.gbl...
> guess...
> than
> that
> rights.
> the
> the
>

260 Table Limit

A Select statement is limited to 260 tables. Tables included in nested view
s
and UDF's are included in the table total. Is there an easy way to determin
e
how many tables a Select statement is currently using? For example, I'd lik
e
to know if sproc X is currently at 259 tables.Wow,Dave do you reall deal with 260tables within a single SP?

> and UDF's are included in the table total. Is there an easy way to
> determine
> how many tables a Select statement is currently using?
No , that I'm aware
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:93191E1B-B7F6-4B85-9930-233CE6345555@.microsoft.com...
>A Select statement is limited to 260 tables. Tables included in nested
>views
> and UDF's are included in the table total. Is there an easy way to
> determine
> how many tables a Select statement is currently using? For example, I'd
> like
> to know if sproc X is currently at 259 tables.|||Hi Dave
There is no easy way of doing this, even if sysdepends was reliable, you
could not differentiate between different statements in your
procedure/function.
The question you would have to ask is why does your design reach this limit?
John
"Dave" wrote:

> A Select statement is limited to 260 tables. Tables included in nested vi
ews
> and UDF's are included in the table total. Is there an easy way to determ
ine
> how many tables a Select statement is currently using? For example, I'd l
ike
> to know if sproc X is currently at 259 tables.|||I've seen partitioned views that unionize more than 100 tables, but never a
single select with that many joins. This imposed 260 table limit is
basically a sanity constraint and there for the safety of yourself and
others.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:93191E1B-B7F6-4B85-9930-233CE6345555@.microsoft.com...
>A Select statement is limited to 260 tables. Tables included in nested
>views
> and UDF's are included in the table total. Is there an easy way to
> determine
> how many tables a Select statement is currently using? For example, I'd
> like
> to know if sproc X is currently at 259 tables.|||> Is there an easy way to determine
> how many tables a Select statement is currently using?
Being someone who has reached this limit once or twice, I understand
your concern. If this limit is a "sanity constraint", it would be good
to know how "sane" are we, regarding a particular statement.
I found a method to aproximate the number (the real number may be a bit
higher): issue a SET SHOWPLAN_ALL ON before executing the SELECT (in
Query Analyzer / Management Studio), then count the number of rows
where the Argument column begins with "OBJECT:" (you can do this by
copying the result to Excel, for example). This number can be smaller
than the real number of tables, because the query plan is already
optimized (so it doesn't show the tables that are present in the
statement or the views, but are unnecessary for returning the specified
columns).
Razvan

256 table limit for partitioned views

I have a partitioned view sitting over several tables and I'm slowly
approaching the 256 number. Can anybody confirm if there is such a
limit for the maximum number of tables that a partitioned view can
hold?

If this is true, does anybody have any suggestions or ideas to work
around this max limit?

TIA!karthik (karthiksmiles@.gmail.com) writes:
> I have a partitioned view sitting over several tables and I'm slowly
> approaching the 256 number. Can anybody confirm if there is such a
> limit for the maximum number of tables that a partitioned view can
> hold?

Yes, since the maximum number of tables per query is 256 I would
expect that there is such a limit.

> If this is true, does anybody have any suggestions or ideas to work
> around this max limit?

How big are your tables? Would it be possible to consolidate them?

In SQL 2005 there is partioned tables, which is taking this to another
level. I don't know how many partitions you can have in a table, but
it's a new ballpark.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The limit is 256 tables "per SELECT statement", not per query.
Therefore, a UNION query can have more than 256 tables, but
unfortunately, such a query may not be used in a view. For example:

CREATE TABLE T (X INT)
INSERT INTO T VALUES (1)
DECLARE @.SQL varchar(8000)

SELECT @.SQL=ISNULL(@.SQL+' UNION ALL ','')+'SELECT X FROM T'
FROM (SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 256) X

--PRINT LEN(@.SQL)
EXEC(@.SQL)

SET @.SQL='CREATE VIEW V AS '+@.SQL
EXEC (@.SQL)

For more informations, see:
http://groups-beta.google.com/group...885c192f511bd1a

Razvan|||Razvan Socol (rsocol@.gmail.com) writes:
> The limit is 256 tables "per SELECT statement", not per query.
> Therefore, a UNION query can have more than 256 tables, but
> unfortunately, such a query may not be used in a view. For example:

Thanks Razvan. I did notice "per SELECT statement", but I was too lazy
to get a practical interpretation of what that really meant.

> For more informations, see:
> http://groups-beta.google.com/group...885c192f511bd1a

That's a useful link!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Razvan and Erland...I guess I'm just going to wait for the
Partitioned Tables feature in SQL Server 2005.

25 MSDE Connections

In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
What is the definition of a "connection"? For example, if I create a
connection pool of 5 connections in an application, does that count as 5
against the 25?
-Thanks
-Tom
hi Tom,
Tom Celica wrote:
> In the MSDE Edition of Sql Server 2000 there is a limit of 25
> connections. What is the definition of a "connection"? For
> example, if I create a connection pool of 5 connections in an
> application, does that count as 5 against the 25?
there's not such a limit of "25 connection"... this magic number is just a
guess about the probably supported load of an MSDE instance.. and it fo
course is influenced by your architectural design, both within the db and
the app...
please have a look at
http://msdn.microsoft.com/library/?u...asp?frame=true
for all available info about the built in Workload Governor..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

25 MSDE Connections

In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
What is the definition of a "connection"? For example, if I create a
connection pool of 5 connections in an application, does that count as 5
against the 25?
-Thanks
-Tom
I didn't know there was a hard limit but the soft limit was actually 5.
Over 5 concurrent connections and it would start to throttle back your
performance. I don't know for sure but the license probably states a limit
of 5 concurrent connections. It was not intended to be used for that many
connections. That is what the standard edition is for. A connection is
anytime someone is connected to the server. If the pool has 5 open
connections then it is 5 connections.
Andrew J. Kelly SQL MVP
"Tom Celica" <tom@.dontreply.com> wrote in message
news:V1%Ae.1617$_%4.505@.newssvr14.news.prodigy.com ...
> In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
> What is the definition of a "connection"? For example, if I create a
> connection pool of 5 connections in an application, does that count as 5
> against the 25?
> -Thanks
> -Tom
>
|||AFAIK there is no such limit. What makes you say that the limit is 25?
MSDE is optimized for 5 connections or less so if you want 25
connections you should probably be considering Standard or Workgroup
edition.
David Portas
SQL Server MVP

25 MSDE Connections

In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
What is the definition of a "connection"? For example, if I create a
connection pool of 5 connections in an application, does that count as 5
against the 25?
-Thanks
-TomIndependently answered to many of the independently posted questions.
Please refrain from posting the same question independently to multiple
newsgroups.
"Tom Celica" <tom@.dontreply.com> wrote in message
news:s3%Ae.1619$_%4.824@.newssvr14.news.prodigy.com...
> In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
> What is the definition of a "connection"? For example, if I create a
> connection pool of 5 connections in an application, does that count as 5
> against the 25?
> -Thanks
> -Tom
>

25 MSDE Connections

In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
What is the definition of a "connection"? For example, if I create a
connection pool of 5 connections in an application, does that count as 5
against the 25?
-Thanks
-TomI didn't know there was a hard limit but the soft limit was actually 5.
Over 5 concurrent connections and it would start to throttle back your
performance. I don't know for sure but the license probably states a limit
of 5 concurrent connections. It was not intended to be used for that many
connections. That is what the standard edition is for. A connection is
anytime someone is connected to the server. If the pool has 5 open
connections then it is 5 connections.
Andrew J. Kelly SQL MVP
"Tom Celica" <tom@.dontreply.com> wrote in message
news:V1%Ae.1617$_%4.505@.newssvr14.news.prodigy.com...
> In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
> What is the definition of a "connection"? For example, if I create a
> connection pool of 5 connections in an application, does that count as 5
> against the 25?
> -Thanks
> -Tom
>|||AFAIK there is no such limit. What makes you say that the limit is 25?
MSDE is optimized for 5 connections or less so if you want 25
connections you should probably be considering Standard or Workgroup
edition.
David Portas
SQL Server MVP
--

25 MSDE Connections

In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
What is the definition of a "connection"? For example, if I create a
connection pool of 5 connections in an application, does that count as 5
against the 25?
-Thanks
-Tom
Independently answered to many of the independently posted questions.
Please refrain from posting the same question independently to multiple
newsgroups.
"Tom Celica" <tom@.dontreply.com> wrote in message
news:s3%Ae.1619$_%4.824@.newssvr14.news.prodigy.com ...
> In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
> What is the definition of a "connection"? For example, if I create a
> connection pool of 5 connections in an application, does that count as 5
> against the 25?
> -Thanks
> -Tom
>

25 MSDE Connections

In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
What is the definition of a "connection"? For example, if I create a
connection pool of 5 connections in an application, does that count as 5
against the 25?
-Thanks
-TomIndependently answered to many of the independently posted questions.
Please refrain from posting the same question independently to multiple
newsgroups.
"Tom Celica" <tom@.dontreply.com> wrote in message
news:s3%Ae.1619$_%4.824@.newssvr14.news.prodigy.com...
> In the MSDE Edition of Sql Server 2000 there is a limit of 25 connections.
> What is the definition of a "connection"? For example, if I create a
> connection pool of 5 connections in an application, does that count as 5
> against the 25?
> -Thanks
> -Tom
>

2005 Express - Concurrent Connections

Hi all !

Does someone know how many concurrent connections 2005 Express can
accommodate ? The limit on MSDE was 5 before performance drops considerably.

Thanks for your help.

SebastianThere is no workload governor as in MSDE, but Express can only use 1
CPU, 1GB RAM and the database size is limited to 4GB:

http://msdn.microsoft.com/library/d...sseoverview.asp

Simon