Lock Issue

I've got a 2000 server (2G usable RAM) where we just
installed SP2. Immediately after installation I began
getting the below error:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration.
Error: 1204, Severity: 19, State: 1
I've got the default configuration setting for locks of 0
and nothing has changed on this server that I know except
for SP2. Furthermore, I've been monitoring the total
number of locks at any given time and I'm not seeing
anything that should cause problems. The most locks I've
seen is about 40,000.
The last time we got this error msg in the error log, I
had two index defrag jobs going and there was bulk load
and/or delete job running.
Is this simply memory related? Is there anything with SP2
that might cause this (especially if it uses extra memory
for index defrags)?
Any insight would be much appreciated?!Hi Curt.
What is your configuration for locks? Run this to find out:
exec sp_configure 'locks'
Perhaps it got set to 40000 maximum during SP2 installation somehow?
Regards,
Greg Linwood
SQL Server MVP
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> I've got a 2000 server (2G usable RAM) where we just
> installed SP2. Immediately after installation I began
> getting the below error:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration.
> Error: 1204, Severity: 19, State: 1
> I've got the default configuration setting for locks of 0
> and nothing has changed on this server that I know except
> for SP2. Furthermore, I've been monitoring the total
> number of locks at any given time and I'm not seeing
> anything that should cause problems. The most locks I've
> seen is about 40,000.
> The last time we got this error msg in the error log, I
> had two index defrag jobs going and there was bulk load
> and/or delete job running.
> Is this simply memory related? Is there anything with SP2
> that might cause this (especially if it uses extra memory
> for index defrags)?
> Any insight would be much appreciated?!
>|||It is set to 0 for run and config value (which I am sure
it was pre-SP2 as well).
>--Original Message--
>Hi Curt.
>What is your configuration for locks? Run this to find
out:
>exec sp_configure 'locks'
>Perhaps it got set to 40000 maximum during SP2
installation somehow?
>Regards,
>Greg Linwood
>SQL Server MVP
>"CurtM" <cndmoyer@.hotmail.com> wrote in message
>news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
>> I've got a 2000 server (2G usable RAM) where we just
>> installed SP2. Immediately after installation I began
>> getting the below error:
>> The SQL Server cannot obtain a LOCK resource at this
time.
>> Rerun your statement when there are fewer active users
or
>> ask the system administrator to check the SQL Server
lock
>> and memory configuration.
>> Error: 1204, Severity: 19, State: 1
>> I've got the default configuration setting for locks of
0
>> and nothing has changed on this server that I know
except
>> for SP2. Furthermore, I've been monitoring the total
>> number of locks at any given time and I'm not seeing
>> anything that should cause problems. The most locks
I've
>> seen is about 40,000.
>> The last time we got this error msg in the error log, I
>> had two index defrag jobs going and there was bulk load
>> and/or delete job running.
>> Is this simply memory related? Is there anything with
SP2
>> that might cause this (especially if it uses extra
memory
>> for index defrags)?
>> Any insight would be much appreciated?!
>
>.
>|||At this point it is just memory issue and you may have a lot of low
granularity locks such as RID or Key. You may want to escalate if possible
using hints for the problematic query
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:08c201c391c9$77b35000$a301280a@.phx.gbl...
> It is set to 0 for run and config value (which I am sure
> it was pre-SP2 as well).
> >--Original Message--
> >Hi Curt.
> >
> >What is your configuration for locks? Run this to find
> out:
> >
> >exec sp_configure 'locks'
> >
> >Perhaps it got set to 40000 maximum during SP2
> installation somehow?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"CurtM" <cndmoyer@.hotmail.com> wrote in message
> >news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> >> I've got a 2000 server (2G usable RAM) where we just
> >> installed SP2. Immediately after installation I began
> >> getting the below error:
> >>
> >> The SQL Server cannot obtain a LOCK resource at this
> time.
> >> Rerun your statement when there are fewer active users
> or
> >> ask the system administrator to check the SQL Server
> lock
> >> and memory configuration.
> >>
> >> Error: 1204, Severity: 19, State: 1
> >>
> >> I've got the default configuration setting for locks of
> 0
> >> and nothing has changed on this server that I know
> except
> >> for SP2. Furthermore, I've been monitoring the total
> >> number of locks at any given time and I'm not seeing
> >> anything that should cause problems. The most locks
> I've
> >> seen is about 40,000.
> >>
> >> The last time we got this error msg in the error log, I
> >> had two index defrag jobs going and there was bulk load
> >> and/or delete job running.
> >>
> >> Is this simply memory related? Is there anything with
> SP2
> >> that might cause this (especially if it uses extra
> memory
> >> for index defrags)?
> >>
> >> Any insight would be much appreciated?!
> >>
> >
> >
> >.
> >

Location of SQL-datafile after installation of VB 2005 DB-application

Hello Experts,

I am relatively new to VB 2005 and SQL Express. I have a question concerning where I can find the SQL-database file (*.mdf) after I have installed (published) my VB 2005 database-application (the *.mdf file was created by Visual Studio Express?). It seems it is nowhere in the disk of the of pc it is installed on. So, what happens to *.mdf file after installation of the application, in what way I can connect to the *.mdf file after the application - the file is part of - is published?

In need of help,

MY

That depends on wheter you are using User instances (standalone mdf files with a SQL Server Express) or server attached files. User instances are normally put in the app_Data directory, whereas saved and marked for deploy and installation will be placed in the appropiate configured folder.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thank you for your reply,

I found finally the *.mdf files (along with the rest of the program files) installed as hidden folders under C:\Documents and Settings\User\Local Settings\...Data\. So, in this respect problem is solved. Obviously, I was using User Instances - at least in the end of my app's ConnectionString I was "User Instance=True". Does this mean that I am using user instances? - And if I mark "User Instance=False" will the installation program automatically attach the *.mdf data file then to SQL Express local server?

And if so, then I have another troublesome question. I am creating an VB DB-application whose DB-structure will change later (further tables will be added later). And my client will add data already data into database of preliminary version. Then I later will update / redeploy (with ClickOnce method) the application - so will the redeployment of the new version of application overwrite the data in SQL-database of older version? - i.e. does the new database file (with nothing or meaningless test data in it) replace the older database content (It would be serious damage to overwrite that data). The change of DB-structure is a another problem.

Could you, please, clarify for me what should I do,

Matti

|||

ClickOnce installation replaces all of the project files, including your database file. This is a short comming in how VS handles database files in 2005. You'll have to find an alternative way to deal with deployment within the ClickOnce framework.

Happily, you can watch the web cast I recorded discussing just such an alternative, links to both the 'cast and the source code demonstrated are available from this blog post.

Mike

|||

Mike,

thank you very much for your reply - and your SQL Server Express WebLog. That is all now and in coming days very important information for me that.

Well, I have started to consider not to include mdf-file in the deployment process at all but install (attach) it manually on a deployment pc via SQL Server Management Studio Express (which of course needs then to be installed on the deployment pc as well). In this way I will not certainly touch in the process of redeployment / update the inserted data in DB.

Also this makes the future changes of DB-structure easier for me; I can perform them through SSMS via SQL: simply add here new tables and relationships.

Thank you,

MY

Location of SQL-datafile after installation of VB 2005 DB-application

Hello Experts,

I am relatively new to VB 2005 and SQL Express. I have a question concerning where I can find the SQL-database file (*.mdf) after I have installed (published) my VB 2005 database-application (the *.mdf file was created by Visual Studio Express?). It seems it is nowhere in the disk of the of pc it is installed on. So, what happens to *.mdf file after installation of the application, in what way I can connect to the *.mdf file after the application - the file is part of - is published?

In need of help,

MY

That depends on wheter you are using User instances (standalone mdf files with a SQL Server Express) or server attached files. User instances are normally put in the app_Data directory, whereas saved and marked for deploy and installation will be placed in the appropiate configured folder.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thank you for your reply,

I found finally the *.mdf files (along with the rest of the program files) installed as hidden folders under C:\Documents and Settings\User\Local Settings\...Data\. So, in this respect problem is solved. Obviously, I was using User Instances - at least in the end of my app's ConnectionString I was "User Instance=True". Does this mean that I am using user instances? - And if I mark "User Instance=False" will the installation program automatically attach the *.mdf data file then to SQL Express local server?

And if so, then I have another troublesome question. I am creating an VB DB-application whose DB-structure will change later (further tables will be added later). And my client will add data already data into database of preliminary version. Then I later will update / redeploy (with ClickOnce method) the application - so will the redeployment of the new version of application overwrite the data in SQL-database of older version? - i.e. does the new database file (with nothing or meaningless test data in it) replace the older database content (It would be serious damage to overwrite that data). The change of DB-structure is a another problem.

Could you, please, clarify for me what should I do,

Matti

|||

ClickOnce installation replaces all of the project files, including your database file. This is a short comming in how VS handles database files in 2005. You'll have to find an alternative way to deal with deployment within the ClickOnce framework.

Happily, you can watch the web cast I recorded discussing just such an alternative, links to both the 'cast and the source code demonstrated are available from this blog post.

Mike

|||

Mike,

thank you very much for your reply - and your SQL Server Express WebLog. That is all now and in coming days very important information for me that.

Well, I have started to consider not to include mdf-file in the deployment process at all but install (attach) it manually on a deployment pc via SQL Server Management Studio Express (which of course needs then to be installed on the deployment pc as well). In this way I will not certainly touch in the process of redeployment / update the inserted data in DB.

Also this makes the future changes of DB-structure easier for me; I can perform them through SSMS via SQL: simply add here new tables and relationships.

Thank you,

MY

Location of default instance SQL server 2005 files

I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.

It ended up on C: and I needed it on D:.

So my questions are:

Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000?

Is there a way to do it during setup so I can avoid this in the future?

Please refere to a previous posting called "How to specify default data location? " .. which talks about the exact place to change it.|||

There is a file called template.ini located at the same folder as setup.exe. In this file, all related command line parameters are discussed when SQL Server 2005 is installed under unattended mode, i.e., command line. You can specify paths to install SQL Server 2005 components.

Under attended mode, i.e., GUI mode, during selecting components to install, clicking the Advanced button will prompt to change paths to install SQL Server 2005 components.

|||

Jiongxiong,

There is no file "template.ini" with the SQL setup program (cd or installed location).

The SQL readme points to a generic windows deployment info page - which is unhelpful.

|||

If you're installing SQL Server Express edition you are running an executable called SQLEXPR.EXE. This is a compressed package that when run expands the contents to a temporary folder, runs the installation and then deletes the temporary folder. You can persist the uncompressed contents by running sqlexpr.exe /x - this will prompt you for a folder to save the uncompressed files. Once uncompressed you'll find the template.ini file in this folder (same folder as setup.exe). The parameters that can be passed to setup.exe can also be passed to sqlexpr.exe - the compression tool will pass the parameters to setup.exe when it runs it.

If you're installing an edition other than Express you will find template.ini in the same folder as setup.exe.

Cheers,
Dan

|||

Jiongxiong, you are right! Sorry, I missed the template.ini file somehow. I will see if it works for my clean install tests.

It is simply amazing that M$ can not do simple file management for installation. There are files, they have versions, they need to be copied to a folder from the CD, and some settings stored - how complicated is that? Well if you are M$ you make SQL05 installation into a mass of gobble-gook. SQL Server 05 is not ready for production use until its installation abomination is fixed!

|||

Most people think that SQL Server setup is just about copying files and writing some reg keys. While it's true it's only a small fraction of what setup does. Here is a small sampling of the other things SQL Server setup does.

SQL Server setup checks the configuration of the machine to determine if the machine is in a valid state - which improves the chances of a good installation. Windows Installer doesn't support this.

SQL Server is a multi-instance product. Windows Installer doesn't support this.

SQL Server requires configuration to happen as part of the setup process. Windows Installer doesn't support this. If you've ever performed a MySQL installation a separate tool is launched post setup to do the configuration - although at the end of setup you don't have a working server.

SQL Server cluster install is extremely robust. Every other product I've seen requires you to rerun setup on every node and make sure the configurations are the same. SQL does this for you. Windows Installer doesn't support cluster installs.

SQL Server requires a secure installation. This means that every file/directory/service/reg key is ACL'd to the appropriate service account. Windows Installer doesn't support this.

SQL Server setup supports version and edition upgrade. Windows Installer doesn't support this.

Windows Installer handles very well the copying of files and the writing of reg keys, but it doesn't handle any of the other complexities associated with SQL Server setup. Everywhere I mentioned that Windows Installer doesn't support something it means we've had to build it. I'm not using this as an excuse, but merely highlighting the fact that setup is much more than just copying files and writing reg keys. Anyone who's worked on the setup of a complex product (such as SQL or Visual Studio) will attest to this.

Is our setup perfect? No. But I certainly don't believe it deserves the criticism. There are millions of ways a user can hose their machine and it would be impossible for us to test for every single case. I'm sure you'll be tempted to compare SQL with other products - don't. There are very few products that come to mind that have the same level of complexity that SQL Server does. Again, this is not an excuse for any particular bug that remains in setup. The complexity of SQL Server has grown tremendously from SQL Server 7.0. The setup has had to embrace the increased complexity and deliver a stable and consistent experience. I believe this was achieved.

Cheers,
Dan

|||

Dan:

This is critical tidbit that surely belongs in Embedding SQL Server Express in Custom Applications. That's a 32-page article (from November 2005) at the Microsoft SQL Server Developer Centre, and while it obviously spends time on template.ini, it doesn't tell you how to find it. Luckily for me, I found your reply on this forum.

Please pass this on to Robert Walters, who wrote the article.

Regards
Josh Korn

Location of default instance SQL server 2005 files

I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.

It ended up on C: and I needed it on D:.

So my questions are:

Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000? Is there a way to do it during setup so I can avoid this in the future?

Please refere to a previous posting called "How to specify default data location? " .. which talks about the exact place to change it.|||

There is a file called template.ini located at the same folder as setup.exe. In this file, all related command line parameters are discussed when SQL Server 2005 is installed under unattended mode, i.e., command line. You can specify paths to install SQL Server 2005 components.

Under attended mode, i.e., GUI mode, during selecting components to install, clicking the Advanced button will prompt to change paths to install SQL Server 2005 components.

|||

Jiongxiong,

There is no file "template.ini" with the SQL setup program (cd or installed location).

The SQL readme points to a generic windows deployment info page - which is unhelpful.

|||

If you're installing SQL Server Express edition you are running an executable called SQLEXPR.EXE. This is a compressed package that when run expands the contents to a temporary folder, runs the installation and then deletes the temporary folder. You can persist the uncompressed contents by running sqlexpr.exe /x - this will prompt you for a folder to save the uncompressed files. Once uncompressed you'll find the template.ini file in this folder (same folder as setup.exe). The parameters that can be passed to setup.exe can also be passed to sqlexpr.exe - the compression tool will pass the parameters to setup.exe when it runs it.

If you're installing an edition other than Express you will find template.ini in the same folder as setup.exe.

Cheers,
Dan

|||

Jiongxiong, you are right! Sorry, I missed the template.ini file somehow. I will see if it works for my clean install tests.

It is simply amazing that M$ can not do simple file management for installation. There are files, they have versions, they need to be copied to a folder from the CD, and some settings stored - how complicated is that? Well if you are M$ you make SQL05 installation into a mass of gobble-gook. SQL Server 05 is not ready for production use until its installation abomination is fixed!

|||

Most people think that SQL Server setup is just about copying files and writing some reg keys. While it's true it's only a small fraction of what setup does. Here is a small sampling of the other things SQL Server setup does.

SQL Server setup checks the configuration of the machine to determine if the machine is in a valid state - which improves the chances of a good installation. Windows Installer doesn't support this.

SQL Server is a multi-instance product. Windows Installer doesn't support this.

SQL Server requires configuration to happen as part of the setup process. Windows Installer doesn't support this. If you've ever performed a MySQL installation a separate tool is launched post setup to do the configuration - although at the end of setup you don't have a working server.

SQL Server cluster install is extremely robust. Every other product I've seen requires you to rerun setup on every node and make sure the configurations are the same. SQL does this for you. Windows Installer doesn't support cluster installs.

SQL Server requires a secure installation. This means that every file/directory/service/reg key is ACL'd to the appropriate service account. Windows Installer doesn't support this.

SQL Server setup supports version and edition upgrade. Windows Installer doesn't support this.

Windows Installer handles very well the copying of files and the writing of reg keys, but it doesn't handle any of the other complexities associated with SQL Server setup. Everywhere I mentioned that Windows Installer doesn't support something it means we've had to build it. I'm not using this as an excuse, but merely highlighting the fact that setup is much more than just copying files and writing reg keys. Anyone who's worked on the setup of a complex product (such as SQL or Visual Studio) will attest to this.

Is our setup perfect? No. But I certainly don't believe it deserves the criticism. There are millions of ways a user can hose their machine and it would be impossible for us to test for every single case. I'm sure you'll be tempted to compare SQL with other products - don't. There are very few products that come to mind that have the same level of complexity that SQL Server does. Again, this is not an excuse for any particular bug that remains in setup. The complexity of SQL Server has grown tremendously from SQL Server 7.0. The setup has had to embrace the increased complexity and deliver a stable and consistent experience. I believe this was achieved.

Cheers,
Dan

|||

Dan:

This is critical tidbit that surely belongs in Embedding SQL Server Express in Custom Applications. That's a 32-page article (from November 2005) at the Microsoft SQL Server Developer Centre, and while it obviously spends time on template.ini, it doesn't tell you how to find it. Luckily for me, I found your reply on this forum.

Please pass this on to Robert Walters, who wrote the article.

Regards
Josh Korn

Location of default instance SQL server 2005 files

I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.

It ended up on C: and I needed it on D:.

So my questions are:

Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000? Is there a way to do it during setup so I can avoid this in the future?

Please refere to a previous posting called "How to specify default data location? " .. which talks about the exact place to change it.|||

There is a file called template.ini located at the same folder as setup.exe. In this file, all related command line parameters are discussed when SQL Server 2005 is installed under unattended mode, i.e., command line. You can specify paths to install SQL Server 2005 components.

Under attended mode, i.e., GUI mode, during selecting components to install, clicking the Advanced button will prompt to change paths to install SQL Server 2005 components.

|||

Jiongxiong,

There is no file "template.ini" with the SQL setup program (cd or installed location).

The SQL readme points to a generic windows deployment info page - which is unhelpful.

|||

If you're installing SQL Server Express edition you are running an executable called SQLEXPR.EXE. This is a compressed package that when run expands the contents to a temporary folder, runs the installation and then deletes the temporary folder. You can persist the uncompressed contents by running sqlexpr.exe /x - this will prompt you for a folder to save the uncompressed files. Once uncompressed you'll find the template.ini file in this folder (same folder as setup.exe). The parameters that can be passed to setup.exe can also be passed to sqlexpr.exe - the compression tool will pass the parameters to setup.exe when it runs it.

If you're installing an edition other than Express you will find template.ini in the same folder as setup.exe.

Cheers,
Dan

|||

Jiongxiong, you are right! Sorry, I missed the template.ini file somehow. I will see if it works for my clean install tests.

It is simply amazing that M$ can not do simple file management for installation. There are files, they have versions, they need to be copied to a folder from the CD, and some settings stored - how complicated is that? Well if you are M$ you make SQL05 installation into a mass of gobble-gook. SQL Server 05 is not ready for production use until its installation abomination is fixed!

|||

Most people think that SQL Server setup is just about copying files and writing some reg keys. While it's true it's only a small fraction of what setup does. Here is a small sampling of the other things SQL Server setup does.

SQL Server setup checks the configuration of the machine to determine if the machine is in a valid state - which improves the chances of a good installation. Windows Installer doesn't support this.

SQL Server is a multi-instance product. Windows Installer doesn't support this.

SQL Server requires configuration to happen as part of the setup process. Windows Installer doesn't support this. If you've ever performed a MySQL installation a separate tool is launched post setup to do the configuration - although at the end of setup you don't have a working server.

SQL Server cluster install is extremely robust. Every other product I've seen requires you to rerun setup on every node and make sure the configurations are the same. SQL does this for you. Windows Installer doesn't support cluster installs.

SQL Server requires a secure installation. This means that every file/directory/service/reg key is ACL'd to the appropriate service account. Windows Installer doesn't support this.

SQL Server setup supports version and edition upgrade. Windows Installer doesn't support this.

Windows Installer handles very well the copying of files and the writing of reg keys, but it doesn't handle any of the other complexities associated with SQL Server setup. Everywhere I mentioned that Windows Installer doesn't support something it means we've had to build it. I'm not using this as an excuse, but merely highlighting the fact that setup is much more than just copying files and writing reg keys. Anyone who's worked on the setup of a complex product (such as SQL or Visual Studio) will attest to this.

Is our setup perfect? No. But I certainly don't believe it deserves the criticism. There are millions of ways a user can hose their machine and it would be impossible for us to test for every single case. I'm sure you'll be tempted to compare SQL with other products - don't. There are very few products that come to mind that have the same level of complexity that SQL Server does. Again, this is not an excuse for any particular bug that remains in setup. The complexity of SQL Server has grown tremendously from SQL Server 7.0. The setup has had to embrace the increased complexity and deliver a stable and consistent experience. I believe this was achieved.

Cheers,
Dan

|||

Dan:

This is critical tidbit that surely belongs in Embedding SQL Server Express in Custom Applications. That's a 32-page article (from November 2005) at the Microsoft SQL Server Developer Centre, and while it obviously spends time on template.ini, it doesn't tell you how to find it. Luckily for me, I found your reply on this forum.

Please pass this on to Robert Walters, who wrote the article.

Regards
Josh Korn

sql

LocalAdmins, LocalSystem, and the sysadmin role

During SQL Server 2005 installation, several logins are added to the
sysadmin role including:
- local administrator group (BUILTIN\Administrators),
- Local System (NT AUTHORITY\SYSTEM), and
- sa.
A common hardening practice is to later remove the local administrator group
from the sysadmin role, thereby separating server administration from DBMS
administration. However, I don't recall ever seeing the recommendation to
also remove the LocalSystem account from the sysadmin role.
Has anyone seen recommendations to remove BOTH local administrators and
LocalSystem from the sysadmin role for hardening purposes, and - if this
were to be done - what are the consequences?
Thanks in advance (and apologies for re-posting in hopes of a response),
DrewHello Drew,
Yes, generally we remove local administrator group from sysadmin role which
actually prevent accessing sql server who is having system admini privilages
on the server.
Now personally I prefer not to remove Local System as I was facing problem
while using the full-text search. Please refer the following Microsoft
article.
http://support.microsoft.com/kb/317746
Hope this will help you.
Regards,
MB
"DHamre" <dhamre@.comcast.net> wrote in message
news:%23Au%23cyMMHHA.3424@.TK2MSFTNGP02.phx.gbl...
> During SQL Server 2005 installation, several logins are added to the
> sysadmin role including:
> - local administrator group (BUILTIN\Administrators),
> - Local System (NT AUTHORITY\SYSTEM), and
> - sa.
> A common hardening practice is to later remove the local administrator
> group from the sysadmin role, thereby separating server administration
> from DBMS administration. However, I don't recall ever seeing the
> recommendation to also remove the LocalSystem account from the sysadmin
> role.
> Has anyone seen recommendations to remove BOTH local administrators and
> LocalSystem from the sysadmin role for hardening purposes, and - if this
> were to be done - what are the consequences?
> Thanks in advance (and apologies for re-posting in hopes of a response),
> Drew
>