MSDE size

Hi,
I am not quite sure about the MSDB maximum dabatase size.
Is the 4 GB maximum size applicable for each database
size or a table?
Can a single table size be more than 3 GB?. I have a
single table of size over 2.5 GB. If the size of a single
table is limited to 2 GB (I heard from my friend ... but
not sure) then should I split that large table into 2 or
3 tables such that I can take the maximum benefit of 4 GB
of MSDE size.
Please share your opinion and any new ideas other than
what I have written over here.
I appreciate it in advance !!
David
Currently with MSDE, the maximum database size, excluding logs is 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/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:08bd01c4e062$7dd4ee60$a501280a@.phx.gbl...
> Hi,
> I am not quite sure about the MSDB maximum dabatase size.
> Is the 4 GB maximum size applicable for each database
> size or a table?
> Can a single table size be more than 3 GB?. I have a
> single table of size over 2.5 GB. If the size of a single
> table is limited to 2 GB (I heard from my friend ... but
> not sure) then should I split that large table into 2 or
> 3 tables such that I can take the maximum benefit of 4 GB
> of MSDE size.
> Please share your opinion and any new ideas other than
> what I have written over here.
> I appreciate it in advance !!
> David
|||Though be aware that this is changing with 2005 Personal Edition, MSDE's
successor. The 2005 version will only recogognoze 1 GIG of memory, but I
believe it did increase the allowable db size to 4 GB. It also removed the 5
concurrent batch restriction and stopped shipping the SQL Agent. If you want
to autoamte task in 2005 you will have to use the NT scheduler.
Caveat: This was my understanding as of my last check. MS loves to change
things before final release
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uIAZ3aG4EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Currently with MSDE, the maximum database size, excluding logs is 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/
> "David" <anonymous@.discussions.microsoft.com> wrote in message
> news:08bd01c4e062$7dd4ee60$a501280a@.phx.gbl...
>
|||I believe you mean Express Edition not personal Edition.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Castleman" <nomail@.here> wrote in message
news:OVGrezS4EHA.3596@.TK2MSFTNGP12.phx.gbl...
> Though be aware that this is changing with 2005 Personal Edition, MSDE's
> successor. The 2005 version will only recogognoze 1 GIG of memory, but I
> believe it did increase the allowable db size to 4 GB. It also removed the
5
> concurrent batch restriction and stopped shipping the SQL Agent. If you
want
> to autoamte task in 2005 you will have to use the NT scheduler.
> Caveat: This was my understanding as of my last check. MS loves to change
> things before final release
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uIAZ3aG4EHA.3416@.TK2MSFTNGP09.phx.gbl...
>

MSDE replication example

Hi,
could you give me an step-by-step example how to do replication between
MSDE2000's ?
I have 50 tables, each table has an ID GUID primary key column.
thanks.
SRINGER,
you can't use the GUIswith MSDE (not legally) so you'll have to use SQLDMO.
Have a look on www.replicationanswers.com where I have put some code in the
scripts section to set up publishers and subscribers.
Rgds,
Paul Ibison
[vbcol=seagreen]
|||Paul,
What did you mean that you can't use GUIDs with MSDE? We had planned to use
ID columns that implemented the UniqueIdentifier datatype and used the
NEWID() function to put in a GUID. We want to use these instead of the
AutoIdentity columns since they are usch a headache in merge replication.
Tommy
"Paul Ibison" wrote:

> SáRINGER,
> you can't use the GUIswith MSDE (not legally) so you'll have to use SQLDMO.
> Have a look on www.replicationanswers.com where I have put some code in the
> scripts section to set up publishers and subscribers.
> Rgds,
> Paul Ibison
>
>
|||I'm an idiot. I saw that you said GUIs and I am assuming you meant using EM
instead of the command line. Nevermind! Doh!
Tommy
"Paul Ibison" wrote:

> SáRINGER,
> you can't use the GUIswith MSDE (not legally) so you'll have to use SQLDMO.
> Have a look on www.replicationanswers.com where I have put some code in the
> scripts section to set up publishers and subscribers.
> Rgds,
> Paul Ibison
>
>

MSDE Purge/shrink not allowed as it would take over 2048MB?

I have a database file which is close to the 2048MB limit, but not
there yet.
I am trying DELETE FROM TABLE many of the tables so that I can shrink
it and get under the 2048 limit.
When doing this, I get errors that ALTER (?) would put me over the
2048MB limit..?
I have many full blown SQL servers. This one just needs MSDE. I just
got a little behind in internal logging cleanup (not ldf.)
Can I move this file to a full blown SQL server, run my DELETE, then
move it back? What's the easiest way to do that? Remotely attach it
using UNC? Any better ways?
hi,
edavid3001@.gmail.com wrote:
> ...
> Can I move this file to a full blown SQL server, run my DELETE, then
> move it back?
yep...

> What's the easiest way to do that? Remotely attach it
> using UNC? Any better ways?
nope, I'd go detaching the database, moving corresponding physical files to
the full blown SQL Server machine, reattach it, delete/shrink, detach, move
and reattach, or, instead of detach/attach use backup/restore...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE Problem

I am running MSDE (SQL Agent) and I am having the following problem:

When I setup my connection string to my table, it says that the connection was succesful. I have a asp.net page that has a text box and a button (trying to learn how to add data to a SQL table), anyways, when I input data into the text box and hit the button for it to add the data to the table I get the following error:

Error:
Login failed for user 'Discovery3\ASPNET'
Exception Details: System.Data.SqlCLient.SqlException: Login failed for user 'Discovery3\ASPNET'.

The line that is highlighted is
myConnection.Open()

Here is the code that I am using:
<>
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myConnectionString As String

If myConnectionString = "" Then
myConnectionString = "server=Discovery3;database=dbDVD;trusted_Connection=true"
End If

Dim myConnection As New Data.SqlClient.SqlConnection(myConnectionString)
Dim myInsertQuery As String = "INSERT INTO tblMaster(TITLE) values('" & TextBox1.Text & "')"
Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
myCommand.Connection = myConnection
myConnection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()
End Sub

I don't understand how the connection can be succesfull, yet it errors out at the myConnection.Open() code. My table contains one field TITLE with nothing special.

Any ideas?The ASPNet user must be a user/login on the database - -|||There are a couple of solutions.

1. Go the the SQL Server and grant the ASPNET user Windows Integrated access to the server and the database "dbDVD". More general info on ASP.NET with MSDE can be found onKB 872905.
2. Use SQL authentication instead (less secure): add a SQL account to the database server, grant that account (read/write) access to the database. Change the connection string by omitting the trusted_connection flag and add a uid and pwd flag containing the user name (SQL account) and the password. To do this, you'll have to tweak the MSDE installation to allow SQL auth (you can MSN for this to find more info). However, I don't recommend this second solution.

Make sure only to grant the required access to the database that is needed from the web app; in this case likely only read/write to the tables (lowest privileges access).|||Thanks, this worked...

I got passed the permission error, but now I am getting one for the tables.

Here is the error I get now:

INSERT permission denied on object 'tblMaster', database 'master', owner 'dbo'

how do I give read/write access?

Thanks|||As explained in the aforementioned KB article, you'll need to grant access for the ASPNET login to the database (in this case "master" - side tip: I recommend to put your own private data in another database than the master!).

osql -E -S COMPUTER_NAME\VSDOTNET -Q "sp_grantlogin 'COMPUTER_NAME\ASPNET'"
osql -E -S COMPUTER_NAME\VSDOTNET -d Pubs -Q "sp_grantdbaccess 'COMPUTER_NAME\ASPNET'"
osql -E -S COMPUTER_NAME\VSDOTNET -d Pubs -Q "sp_addrolemember 'db_owner' 'COMPUTER_NAME\ASPNET'"

In here, replace the COMPUTER_NAME with the name of your computer and VSDOTNET with the name of the MSDE instance. In the second command, replace Pubs with the tables where you want to grant the ASPNET account access to.|||Thanks, I got the first to perfect it was the 'db_owner' that corrected the issue.

I appreciate the help.

MSDE Performance

Hello to all,
I am using MSDE and i recently realised that the query performance is
decreasing while db size increases. In a db table of 20000 rows a simple
select query with 6 inner joins took 1.27 sec. to execute (XP Pro, 775 MHz,
248 MB). I upgraded to SQL Server personal edition and the same query was
executed instantly. I tested it in many different pc and the results were
the same.
Could it be that MSDE performance is so dramatically lower than SQL Server?
And if this is the case then for which applications is MSDE suitable for?
Any advice is much appreciated.
Thanks,
Theo
Hi
MSDE 2000 does not support query parallelism and some other tuning features.
If you woul have more RAM, it could cache more data, negating the effect of
the simpler query processor.
Regards
Mike
"Theo" wrote:

> Hello to all,
> I am using MSDE and i recently realised that the query performance is
> decreasing while db size increases. In a db table of 20000 rows a simple
> select query with 6 inner joins took 1.27 sec. to execute (XP Pro, 775 MHz,
> 248 MB). I upgraded to SQL Server personal edition and the same query was
> executed instantly. I tested it in many different pc and the results were
> the same.
>
> Could it be that MSDE performance is so dramatically lower than SQL Server?
> And if this is the case then for which applications is MSDE suitable for?
>
> Any advice is much appreciated.
> Thanks,
>
> Theo
>
>
|||Hi,
i am not sure i understand what you mean when you refer to query parallelism
but this is the query i am having problems with:
SELECT
cls_Suppliers.Supplier, lot_Grouping.BatchCode, cls_Products.Product,
cls_Color.Description AS ClDesc,
cls_Class.Description AS CsDesc, cls_Size.Description AS SzDesc,
cls_Boxes.Box, lot_Production.LabelCode,
lot_Production.lot_ID, cls_Color.Code AS ClCode, cls_Class.Code AS CsCode,
cls_Size.Code AS SzCode, cls_Suppliers.Code AS SpCode, cls_Boxes.Code AS
BCode, cls_Products.Code AS PCode, cls_Boxes.BoxWeight
FROM
lnk_lot_tracing lnk_lot_tracing_1
LEFT OUTER JOIN lot_Grouping ON lnk_lot_tracing_1.lot_in =
lot_Grouping.lot_ID
RIGHT OUTER JOIN lot_Production ON lnk_lot_tracing_1.lot_out =
lot_Production.lot_ID
LEFT OUTER JOIN cls_Suppliers ON lot_Production.YREF_Suppliers =
cls_Suppliers.OID
LEFT OUTER JOIN cls_Color ON lot_Production.YREF_Color = cls_Color.OID
LEFT OUTER JOIN cls_Products ON lot_Production.YREF_Products =
cls_Products.OID
LEFT OUTER JOIN cls_Class ON lot_Production.YREF_Class = cls_Class.OID
LEFT OUTER JOIN cls_Boxes ON lot_Production.YREF_Boxes = cls_Boxes.OID
LEFT OUTER JOIN cls_Size ON lot_Production.YREF_Size = cls_Size.OID
RIGHT OUTER JOIN lnk_lot_tracing ON lot_Production.lot_ID =
lnk_lot_tracing.lot_in
WHERE
lnk_lot_tracing.lot_out = 'PAL05109000005'
ORDER BY
cls_Suppliers.Supplier, lot_Grouping.BatchCode, CsDesc, ClDesc, SzDesc,
cls_Boxes.Box
In a XP Home, 3.0 GHz pc, 204 MB pc they query was executed in 30 sec. Again
after upgrading to SQL Server the execution was instant. I read in an
article about MSDE features that optimum performance is limited to five
concurrent batch workloads. Does that mean five concurrent transactions,
five concurrent connections, or something totally different that i don't get
?
What do you mean by 'simpler query processor'? Is it that apart from the
concurrent workload limitation, MSDE processes queries differently that SQL
Server?
Regards,
Theo
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net>
news:69D4E639-EE00-4145-B07D-D4006E59FE09@.microsoft.com...
> Hi
> MSDE 2000 does not support query parallelism and some other tuning
features.
> If you woul have more RAM, it could cache more data, negating the effect
of
> the simpler query processor.
> Regards
> Mike
>
|||Query parallelism is where SQL Server splits up a query into multiple pieces,
assigns it to one or more processors. This enables multiple parts of the
query to run, without having to wait for each other. Then the results are
merged at the end.
The workload governor will kick in at 8 concurrent processes. 3 are used by
the system , so if you submit 5 queries, over 5 connections at the same time,
and a 6th one comes in, all the queries get slowed down.
I have found MSDE to be less aggressive when requesting memory. Standard
Edition will ask sooner when it realizes it needs more. MSDE is assumed to
run on someone's desktop PC, so it needs to behave better so that other
applications can also do their work and keep the system responsive.
The governor and performance limiting is a bit of a 'black box' that
Microsoft has not chosen to disclose the full workings of. Figuring out how
it behaves is not very easy so it is hard to compare.
SQL Server Express (SQL Server 2005's version of MSDE), does not have a
query governor it in and supports larger databases, but will only use 1
processor and at maximum 1GB RAM. The 'black box' uncertainty falls away.
Regards
Mike
"Theo" wrote:

> Hi,
> i am not sure i understand what you mean when you refer to query parallelism
> but this is the query i am having problems with:
> ----
> SELECT
> cls_Suppliers.Supplier, lot_Grouping.BatchCode, cls_Products.Product,
> cls_Color.Description AS ClDesc,
> cls_Class.Description AS CsDesc, cls_Size.Description AS SzDesc,
> cls_Boxes.Box, lot_Production.LabelCode,
> lot_Production.lot_ID, cls_Color.Code AS ClCode, cls_Class.Code AS CsCode,
> cls_Size.Code AS SzCode, cls_Suppliers.Code AS SpCode, cls_Boxes.Code AS
> BCode, cls_Products.Code AS PCode, cls_Boxes.BoxWeight
> FROM
> lnk_lot_tracing lnk_lot_tracing_1
> LEFT OUTER JOIN lot_Grouping ON lnk_lot_tracing_1.lot_in =
> lot_Grouping.lot_ID
> RIGHT OUTER JOIN lot_Production ON lnk_lot_tracing_1.lot_out =
> lot_Production.lot_ID
> LEFT OUTER JOIN cls_Suppliers ON lot_Production.YREF_Suppliers =
> cls_Suppliers.OID
> LEFT OUTER JOIN cls_Color ON lot_Production.YREF_Color = cls_Color.OID
> LEFT OUTER JOIN cls_Products ON lot_Production.YREF_Products =
> cls_Products.OID
> LEFT OUTER JOIN cls_Class ON lot_Production.YREF_Class = cls_Class.OID
> LEFT OUTER JOIN cls_Boxes ON lot_Production.YREF_Boxes = cls_Boxes.OID
> LEFT OUTER JOIN cls_Size ON lot_Production.YREF_Size = cls_Size.OID
> RIGHT OUTER JOIN lnk_lot_tracing ON lot_Production.lot_ID =
> lnk_lot_tracing.lot_in
> WHERE
> lnk_lot_tracing.lot_out = 'PAL05109000005'
> ORDER BY
> cls_Suppliers.Supplier, lot_Grouping.BatchCode, CsDesc, ClDesc, SzDesc,
> cls_Boxes.Box
> ----
>
> In a XP Home, 3.0 GHz pc, 204 MB pc they query was executed in 30 sec. Again
> after upgrading to SQL Server the execution was instant. I read in an
> article about MSDE features that optimum performance is limited to five
> concurrent batch workloads. Does that mean five concurrent transactions,
> five concurrent connections, or something totally different that i don't get
> ?
>
> What do you mean by 'simpler query processor'? Is it that apart from the
> concurrent workload limitation, MSDE processes queries differently that SQL
> Server?
>
> Regards,
> Theo
>
>
> ? "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> Y??á?? ó?? ìTX?ìá
> news:69D4E639-EE00-4145-B07D-D4006E59FE09@.microsoft.com...
> features.
> of
>
>
|||Thanks a lot Mike, this has been helpful indeed.
Theo.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net>
news:7B72D454-5D64-46BF-9E02-C643C049DA73@.microsoft.com...
> Query parallelism is where SQL Server splits up a query into multiple
pieces,
> assigns it to one or more processors. This enables multiple parts of the
> query to run, without having to wait for each other. Then the results are
> merged at the end.
> The workload governor will kick in at 8 concurrent processes. 3 are used
by
> the system , so if you submit 5 queries, over 5 connections at the same
time,
> and a 6th one comes in, all the queries get slowed down.
> I have found MSDE to be less aggressive when requesting memory. Standard
> Edition will ask sooner when it realizes it needs more. MSDE is assumed to
> run on someone's desktop PC, so it needs to behave better so that other
> applications can also do their work and keep the system responsive.
> The governor and performance limiting is a bit of a 'black box' that
> Microsoft has not chosen to disclose the full workings of. Figuring out
how
> it behaves is not very easy so it is hard to compare.
> SQL Server Express (SQL Server 2005's version of MSDE), does not have a
> query governor it in and supports larger databases, but will only use 1
> processor and at maximum 1GB RAM. The 'black box' uncertainty falls away.
> Regards
> Mike

MSDE memory usage

I am using VB .NET to convert data from Access to MSDE.
While I am transferring table by table, the memory usage of 'sqlsvr' keeps increasing, and also CPU usage is @. 100 %
Some Tables have more than 10,000 records. Is there a way to reduce the memory usage or an explaination why would memory usage increase each time MDSE is accessed.
Just to note, there is only one connection to each database(Acces97 and MSDE)
and all the recordset connections are opened and closed
Thanks
Bobby
hi Bobby,
"Bobby" <Bobby@.discussions.microsoft.com> ha scritto nel messaggio
news:70184C7E-3CBF-4BEB-9381-A3AA8174A646@.microsoft.com...
> I am using VB .NET to convert data from Access to MSDE.
> While I am transferring table by table, the memory usage of 'sqlsvr' keeps
increasing, and also CPU usage is @. 100 %
> Some Tables have more than 10,000 records. Is there a way to reduce the
memory usage or an explaination why would memory usage increase each time
MDSE is accessed.
> Just to note, there is only one connection to each database(Acces97 and
MSDE)
> and all the recordset connections are opened and closed
>
i think Ken Henderson's book extracted, available at
http://msdn.microsoft.com/library/de...v_03252004.asp
is worth reading..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply