Database sql server2000 very slow

I have a database that had worked under msde 1.0 until reached the 2GB
of dimension and the dB was blocked. To make possible the work i had
deleted old data from some table. The database restart, but the
answers from server become very slow. So i decided to pass to Sql
Server 2000 without success. May i perform a check of this database?
if is a indexs problem there is a way to rebuild them?
Thanks in advance
Andy Wet"Andy Wet" <andy_wet@.yahoo.it> wrote in message
news:cbd71cd0.0311040107.31de9ec5@.posting.google.c om...
> I have a database that had worked under msde 1.0 until reached the 2GB
> of dimension and the dB was blocked. To make possible the work i had
> deleted old data from some table. The database restart, but the
> answers from server become very slow. So i decided to pass to Sql
> Server 2000 without success. May i perform a check of this database?
> if is a indexs problem there is a way to rebuild them?
> Thanks in advance
> Andy Wet

Can you explain what you mean by 'passing to SQL Server 2000'? Did you try
to upgrade MSDE to Standard Edition, did you backup/restore your MSDE
database on another version, etc.?

It might be worth using sp_updatestats to update the statstics in the
database, and also DBCC CHECKDB, if you think the database may be corrupted.

Simon

Database size question

I have a small application that I've developed using MSDE (2K). I'm
going to put this in to production by setting up a new computer and it
seems that Server 2005 Express edition will be adequate for our needs.
I want to check that the size of the databases I'm using are less than
4GB. How do I do that? I've looked around in SQL Server Enterprise
Manager but I don't see file sizes shown anywhere.
In Query Analyzer, try this:
EXECUTE sp_spaceused
In Enterprise Mangler, right click on the database, choose [Properties]. The
file size is in the middle of the page on the [General] tab,
Also, you can use Windows Explorer to view the database file. (It's not a
precise measure, but it's close.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
|||Thanks, Arnie - I don't know why I couldn't find that
My db's are all in the single-digit MB size, so I guess that the 4GB
limit of the Express version won't be an issue. And of course, if it
ever becomes an issue, we just have to upgrade.
Thanks again.
On Sat, 11 Nov 2006 09:10:03 -0800, "Arnie Rowland" <arnie@.1568.com>
wrote:

>In Query Analyzer, try this:
>EXECUTE sp_spaceused
>In Enterprise Mangler, right click on the database, choose [Properties]. The
>file size is in the middle of the page on the [General] tab,
>Also, you can use Windows Explorer to view the database file. (It's not a
>precise measure, but it's close.)
|||The maximum size of an MSDE database is 2GB so you can be sure your
databases are under 4GB
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.

Database Size Limitations

Hey all,
Is there any way that I can find the current size limitation (eg, MSDE = 2gb
limit) of a database from a db query / DMO dll call etc?
ThanksYou can get the edition and product information with these:
SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
SELECT SERVERPROPERTY('Edition') AS [Edition]
But there is no bit that I know of that you can see for the size limitation.
It's pretty simple though if it is MSDE it is 2GB, if it is SQLExpress it is
4GB otherwise there is no limit.
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9F961CBD-4303-49DA-A8D8-8E2C63296BB5@.microsoft.com...
> Hey all,
> Is there any way that I can find the current size limitation (eg, MSDE =
> 2gb
> limit) of a database from a db query / DMO dll call etc?
> Thanks|||Thanks Andrew, but which bit out of the server results would show what
version the DB is running on?
I understand which DB's have limits, but I cant establish a way to find out
which type of DB system is being used by our customers in order to impose a
limit check.
Any help would be great! Thanks!
"Andrew J. Kelly" wrote:

> You can get the edition and product information with these:
>
> SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
> SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
> SELECT SERVERPROPERTY('Edition') AS [Edition]
> But there is no bit that I know of that you can see for the size limitatio
n.
> It's pretty simple though if it is MSDE it is 2GB, if it is SQLExpress it
is
> 4GB otherwise there is no limit.
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:9F961CBD-4303-49DA-A8D8-8E2C63296BB5@.microsoft.com...
>
>|||I am not sure i am understanding you properly. Are you asking how do you
determine if they are running MSDE or not? Is so then just run
SERVERPROPERTY('Edition') to see. You can check BooksOnLine under this
command for the proper results.
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:44A7095B-F895-49DD-AA34-28FFE7F4DC1E@.microsoft.com...
> Thanks Andrew, but which bit out of the server results would show what
> version the DB is running on?
> I understand which DB's have limits, but I cant establish a way to find
> out
> which type of DB system is being used by our customers in order to impose
> a
> limit check.
> Any help would be great! Thanks!
> "Andrew J. Kelly" wrote:
>|||Yeah, thats what im after.
When trying it out, ive got an MSDE installation locally and a full SQL 2000
on a server, but both return 'Developer Edition' from the 'Edition' server
property, so I assumed that this wasn't specific enough?
"Andrew J. Kelly" wrote:

> I am not sure i am understanding you properly. Are you asking how do you
> determine if they are running MSDE or not? Is so then just run
> SERVERPROPERTY('Edition') to see. You can check BooksOnLine under this
> command for the proper results.
>
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:44A7095B-F895-49DD-AA34-28FFE7F4DC1E@.microsoft.com...
>
>|||Hmmm. I can see the Full version showing Developer if that what it is but I
would expect MSDE to show DeskTop. Are you sure you were pointing to the
correct instance? What does "engine edition" show?
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:B68E0408-2DE2-4CEA-886A-00338CBA8645@.microsoft.com...
> Yeah, thats what im after.
> When trying it out, ive got an MSDE installation locally and a full SQL
> 2000
> on a server, but both return 'Developer Edition' from the 'Edition' server
> property, so I assumed that this wasn't specific enough?
> "Andrew J. Kelly" wrote:
>|||Ok.. im a dumbass! (Looking at the wrong DB!)
Now my 'Engine Edition' Shows 1 (Desktop Edition) which is what I would
expect.
Do we know if the 'SERVERPROPERTY(EngineEdition)' returns similar results
for SQL 2005 & SQLExpress?
"Andrew J. Kelly" wrote:

> Hmmm. I can see the Full version showing Developer if that what it is but
I
> would expect MSDE to show DeskTop. Are you sure you were pointing to the
> correct instance? What does "engine edition" show?
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:B68E0408-2DE2-4CEA-886A-00338CBA8645@.microsoft.com...
>
>|||These are the possible results from both the Edition and Engine Edition
properties in 2005 BOL:
'Desktop Engine'
'Developer Edition'
'Enterprise Edition'
'Enterprise Evaluation Edition'
'Personal Edition'
'Standard Edition'
'Express Edition'
'Workgroup Edition'
'Windows Embedded SQL'
---
Database Engine edition of the instance of SQL Server installed on the
server.
1 = Personal or Desktop Engine
2 = Standard
3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and
Developer.)
4 = Express
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:24868DAD-04FA-42B2-BF4E-7571BF6DCB06@.microsoft.com...
> Ok.. im a dumbass! (Looking at the wrong DB!)
> Now my 'Engine Edition' Shows 1 (Desktop Edition) which is what I would
> expect.
> Do we know if the 'SERVERPROPERTY(EngineEdition)' returns similar results
> for SQL 2005 & SQLExpress?
> "Andrew J. Kelly" wrote:
>sql

Database Size Limitation of SQL Server 2000 Personal Edition ?

I would like to know is there any limitation to the size of data file of SQL
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?No there is no limitation.
But there is limitation on RAM usage

Database Size Limitation of SQL Server 2000 Personal Edition ?

I would like to know is there any limitation to the size of data file of SQL
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?
No there is no limitation.
But there is limitation on RAM usage

Database Size Limitation of SQL Server 2000 Personal Edition ?

I would like to know is there any limitation to the size of data file of SQL
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?No there is no limitation.
But there is limitation on RAM usage

Database setup script

I have MSDE installed on my machine as was wondering what the NETSDK is in the following commands.

@.rem Uncomment the following line for MSDE
@.rem set DBNAME=(local)\NETSDK
set DBNAME=(local)\NETSDK

Thanks,
Bob HIt would appear that NETSDK is the "instance name" of the MSDE installation.

With SQL Server 2000 (and MSDE), multiple instances of SQL Server can be installed. The first uses the "default instance"; that is, to access it, simply reference the name of the server in a connection string. To reference a non-default instance, follow the server with \<instance name>sql

Database protection

Is there a way to prevent users from logging into an MSDE database instance?
I created an MSDE instance using a strong SA password but I was still able
to logging to the database using Windows Authentication.
The reason I don't want the user to see the database is because the database
structure that I am distributing (MSDE) is exactly the same as the one I
have online. If I let the user peek into my MSDE database they might find a
way to mess-up the database that is online. I just don't want to take the
risk.
Also, is stored procedure encryption easily bypassed if I logon as a
database administrator?
Thanks.
If you don=B4t want Windows Authentication, disable it:
http://support.microsoft.com/default...;EN-US;q285097
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:
Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>
-URL---
HTH, Jens Suessmeyer.
|||Hello,
I notice you have posted the same question in our SQLServer newsgroup,
which I have already responded. So please check my answer there and if you
need any further assistance on this particular issue, please reply to me in
that thread so I can follow up with you in time. Thanks.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||hi Jens,
Jens wrote:
> If you dont want Windows Authentication, disable it:
actually you cant disable Windows Authentication... you can disable standard
SQL Server authentication as you described, but not the contrary...
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
|||You sure right, I misunderstood the op.
Thanks.
|||In message <3ot19pF794d7U1@.individual.net>, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> writes
>hi Jens,
>Jens wrote:
>actually you cant disable Windows Authentication... you can disable standard
>SQL Server authentication as you described, but not the contrary...
Correct, however ... you can remove the "BUILTIN\..." Windows users
from the allowed Logins under Security tab to effectively disable the
Windows Users from logging into that instance.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Andrew,
Andrew D. Newbould wrote:
> ...
> Correct, however ... you can remove the "BUILTIN\..." Windows users
> from the allowed Logins under Security tab to effectively disable the
> Windows Users from logging into that instance.
yes, of course, but this has a nasty side effect on MSDE instance, where the
Agent will no longer be able to start up, where you can not use Enterprise
Manager to set up the Win login(s) running the SQL Server and SQL Server
Agent..
until sp4, you could use the http://support.microsoft.com/kb/283811/en-us to
provide the appropriate permissions for that account, but sp4 chaged
something I'm still trying to figure out...
I'm still trying troubleshooting it..
I tryed "propagating" file permissions to all sub folders as described, as
long as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
making those accounts member of the local sysadmins WinNT role
it seems to work, but I'm not completely confident about that...
feedback is welcome :D:D
but I definitevely hope kb article 283811 gets updated..
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
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

Database protection

Is there a way to prevent users from logging into an MSDE database instance?
I created an MSDE instance using a strong SA password but I was still able
to logging to the database using Windows Authentication.
The reason I don't want the user to see the database is because the database
structure that I am distributing (MSDE) is exactly the same as the one I
have online. If I let the user peek into my MSDE database they might find a
way to mess-up the database that is online. I just don't want to take the
risk.
Also, is stored procedure encryption easily bypassed if I logon as a
database administrator?
Thanks.Rene
Create a new Login (DD) in SQL Server and and don't CRANT permission it to
the database. Now, when the user login with as DD he/she will not be able
access to the database
"Rene" <nospam@.nospam.com> wrote in message
news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Is there a way to prevent users from logging into an MSDE database
> instance? I created an MSDE instance using a strong SA password but I was
> still able to logging to the database using Windows Authentication.
> The reason I don't want the user to see the database is because the
> database structure that I am distributing (MSDE) is exactly the same as
> the one I have online. If I let the user peek into my MSDE database they
> might find a way to mess-up the database that is online. I just don't want
> to take the risk.
> Also, is stored procedure encryption easily bypassed if I logon as a
> database administrator?
> Thanks.
>|||Thanks Uri
But if the user is logged on to Windows as an Administrator, doesn't this
user also has Admin right to the database by default? If this is the case,
even if I create a new database user it won't help because the person logged
into Windows as an administrator can automatically logging as "SA". Is this
the right?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Rene
> Create a new Login (DD) in SQL Server and and don't CRANT permission it
> to the database. Now, when the user login with as DD he/she will not be
> able access to the database
>
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hello,
You can use sp_grantlogin to allow a Microsoft Windows NT user or group
account to connect to Microsoft SQL Server using Windows Authentication.
Use sp_denylogin to prevent a Microsoft Windows NT user or group from
connecting to Microsoft SQL Server. For example, you can run
sp_denylogin 'builtin\administrators'
to prevent from administrator to connect to SQL Server. However, make sure
you have the SA password and the authentication mode is mixed-mode before
you do so.
For more information, refer to the following articles:
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_ga-gz_8dri.asp
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_da-di_9jji.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Well, don't permit them to connect as Administrators if you want to
implement the policy.
Remove them from 'Windows Administrators' and add to the newly created Group
only for access to the specific database
"Rene" <nospam@.nospam.com> wrote in message
news:uKoLoucuFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Thanks Uri
> But if the user is logged on to Windows as an Administrator, doesn't this
> user also has Admin right to the database by default? If this is the case,
> even if I create a new database user it won't help because the person
> logged into Windows as an administrator can automatically logging as "SA".
> Is this the right?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Have you looked at sp_revokedbaccess?
John
"Rene" <nospam@.nospam.com> wrote in message
news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Is there a way to prevent users from logging into an MSDE database
> instance? I created an MSDE instance using a strong SA password but I was
> still able to logging to the database using Windows Authentication.
> The reason I don't want the user to see the database is because the
> database structure that I am distributing (MSDE) is exactly the same as
> the one I have online. If I let the user peek into my MSDE database they
> might find a way to mess-up the database that is online. I just don't want
> to take the risk.
> Also, is stored procedure encryption easily bypassed if I logon as a
> database administrator?
> Thanks.
>|||Hi
If they are administrators and you are worried that they can damage your
system, then access to your new database is not your only problem!
John
"Rene" <nospam@.nospam.com> wrote in message
news:uKoLoucuFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Thanks Uri
> But if the user is logged on to Windows as an Administrator, doesn't this
> user also has Admin right to the database by default? If this is the case,
> even if I create a new database user it won't help because the person
> logged into Windows as an administrator can automatically logging as "SA".
> Is this the right?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
>