sql local data: number

Showing posts with label number. Show all posts

Wednesday, March 28, 2012

Lock resources in Bpool ??

I have read that the memory allocated for locks is finite. I wanted to know
what that finite number is and in which compartment of the SQL Server memory
portion does it reside in..BPool or MemToLeave ? Also what about user
connection info ..where is that stored ?Both lock memory and user connection memory come from the buffer pool.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OXrSsnLBFHA.3588@.TK2MSFTNGP11.phx.gbl...
>I have read that the memory allocated for locks is finite. I wanted to know
> what that finite number is and in which compartment of the SQL Server
> memory
> portion does it reside in..BPool or MemToLeave ? Also what about user
> connection info ..where is that stored ?
>|||And whats the finite amount of memory allocated for locks ?
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:ePRsJ9QBFHA.608@.TK2MSFTNGP15.phx.gbl...
> Both lock memory and user connection memory come from the buffer pool.
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OXrSsnLBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> >I have read that the memory allocated for locks is finite. I wanted to
know
> > what that finite number is and in which compartment of the SQL Server
> > memory
> > portion does it reside in..BPool or MemToLeave ? Also what about user
> > connection info ..where is that stored ?
> >
> >
>|||It is a moving value based on a percentage of buffer pool resources. I
wouldn't be concerned with the actual value, as it could change from release
to release. "Inside SQL Server 2000" has a good chapter on lock manager
internals.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OKHfoARBFHA.3592@.TK2MSFTNGP11.phx.gbl...
> And whats the finite amount of memory allocated for locks ?
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:ePRsJ9QBFHA.608@.TK2MSFTNGP15.phx.gbl...
>> Both lock memory and user connection memory come from the buffer pool.
>> --
>> Ryan Stonecipher
>> Microsoft Sql Server Storage Engine, DBCC
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:OXrSsnLBFHA.3588@.TK2MSFTNGP11.phx.gbl...
>> >I have read that the memory allocated for locks is finite. I wanted to
> know
>> > what that finite number is and in which compartment of the SQL Server
>> > memory
>> > portion does it reside in..BPool or MemToLeave ? Also what about user
>> > connection info ..where is that stored ?
>> >
>> >
>>
>

Lock resources in Bpool ??

Wednesday, March 21, 2012

Lock a row in the database

Hello,

I created a form that retrieve a number from a database. Can I lock that record so that other people cannot access it?

Similar to "lock" function in Visual Fox Pro database.

Best regards,
Tee Song Yann

Sounds like you are doing some form of incrementing.

A very good way to handle that would be to use the OUTPUT capability of SQL 2005 -it allows you to BOTH increment and retrieve the incremented value in the same query.

Perhaps something like this:

DECLARE @.MyNumberTable table
( NextNumber int )

UPDATE MyNumberTable
SET NextNumber = ( NextNumber + 1 )
OUTPUT inserted.NextNumber INTO @.MyNumberTable

SELECT NextNumber
FROM @.MyNumberTable

|||

Hello Arnie Rowland,

Thanks for the reply. Your solution solved the first part of my problem. The record locking part is still remain unsolved. A friend from another forum suggested:

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }[ ; ]

What do you think?

All your help is much appriciated.

Best regards,
Tee Song Yann

|||

Are you attempting to create an auto-incrementing column? If so use an identity column.

Updating a central value and then holding a lock on it will introduce a bottleneck into your application as all connections will eventually be blocked waiting to update the same value.

|||

Dear Jerome Halmans,

My idea is:

1) I have a table that store various last used number for various document.

2) I will use those number combine with a few more code to create a meaningful serial number. (For example: An invoice will consist of XX XXXXXX XXXX which is the company code, year/month/date and next number of last used number.)

3) If another person login from another company, it will have different company code and different last number.

4) The fun part is I do not want to have jump numbers.

5) If the first user cancel the record inserting, then the number should stay unchanged.

6) The only way that I can think of is by locking the record while the first user is inserting the record.

Is there a batter way for me to achieve that?

Best regards,

Tee Song Yann

|||

I HIGHLY recommend dropping the idea of trying to maintain consequtive 'numbers' if users can 'cancel' the insert action. That will prove to be a major headache. That will create a significant blocking issue and a major performance problem.

Unless, of course, you do not assign the 'number' until the data is saved. At that point, the number can be reported back to the user (post-hoc), and there is not a cancellation issue.

|||

Thanks Arnie,

I get what you mean. I think I will follow your second idea.

Then just out of curiosity, SQL server can perform record locking?

Best regards,

Tee Song Yann

|||

Yes. Look up [ Hints -Locking ] in Books Online.

There are several ways to accomplish locking -it depends upon what you want to accomplish. With the exception of TRANSACTIONS, and in the absence of a clear understanding of the implications and effects, it is best to leave record locking behavior to the server.

Location of SQL

I have an SQL server with a number of databses including :
Sharepoint and SMS. I would like to move these databases to the D
partition and they are currently in the C:\ partition.
How can this be done ?Easiest is to us sp_detach_db and sp_attach_db (documented in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||options
1. Detach / Attach
2. Backup / Restore with MOVE
3. add files on d drive to file group
DBBC SHRINKFILE with EMPTRYFILE
drop old files
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||Hi thanks for your response. How Do I move the databses using EM ? Also
How do I attach , detack with EM ?
"jaylou" wrote:
> The easiest way would be to detach the databases, Move the MDF and LDF files
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
> > I have an SQL server with a number of databses including :
> >
> > Sharepoint and SMS. I would like to move these databases to the D
> > partition and they are currently in the C:\ partition.
> >
> > How can this be done ?|||Hi , I found out how to detach but i am having the problem with one
datbase. A databse that is named tembdb which is 5GB in size
"jaylou" wrote:
> The easiest way would be to detach the databases, Move the MDF and LDF files
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
> > I have an SQL server with a number of databses including :
> >
> > Sharepoint and SMS. I would like to move these databases to the D
> > partition and they are currently in the C:\ partition.
> >
> > How can this be done ?|||The easiest way would be to detach the databases, Move the MDF and LDF files
to the new location and re-attach the databases.
You can do this all in Enterprise Manager. You can also set the default
location to your new location of these databases thru EM Properties of the
Server.
HTH,
Joe
"scuba19" wrote:
> I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||tempdb is a system database that is used to proccess queryies...refer to
Books Online
to move this database you can simply use ALTER DATABASE...
if you restart your sql server ,this db will shrink back to its origional
size...determined by the settings of the model db
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
>> The easiest way would be to detach the databases, Move the MDF and LDF
>> files
>> to the new location and re-attach the databases.
>> You can do this all in Enterprise Manager. You can also set the default
>> location to your new location of these databases thru EM Properties of
>> the
>> Server.
>> HTH,
>> Joe
>>
>> "scuba19" wrote:
>> > I have an SQL server with a number of databses including :
>> >
>> > Sharepoint and SMS. I would like to move these databases to the D
>> > partition and they are currently in the C:\ partition.
>> >
>> > How can this be done ?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;224071#XSLTH3207121122120121120120
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uFfhhZNAGHA.356@.TK2MSFTNGP12.phx.gbl...
> tempdb is a system database that is used to proccess queryies...refer to
> Books Online
> to move this database you can simply use ALTER DATABASE...
> if you restart your sql server ,this db will shrink back to its origional
> size...determined by the settings of the model db
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...
>> Hi , I found out how to detach but i am having the problem with one
>> datbase. A databse that is named tembdb which is 5GB in size
>> "jaylou" wrote:
>> The easiest way would be to detach the databases, Move the MDF and LDF
>> files
>> to the new location and re-attach the databases.
>> You can do this all in Enterprise Manager. You can also set the default
>> location to your new location of these databases thru EM Properties of
>> the
>> Server.
>> HTH,
>> Joe
>>
>> "scuba19" wrote:
>> > I have an SQL server with a number of databses including :
>> >
>> > Sharepoint and SMS. I would like to move these databases to the D
>> > partition and they are currently in the C:\ partition.
>> >
>> > How can this be done ?
>|||I am not able to do this to a databse named tempdb which is 5 GB in size ?
"David J. Cartwright" wrote:
> options
> 1. Detach / Attach
> 2. Backup / Restore with MOVE
> 3. add files on d drive to file group
> DBBC SHRINKFILE with EMPTRYFILE
> drop old files
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
> >I have an SQL server with a number of databses including :
> >
> > Sharepoint and SMS. I would like to move these databases to the D
> > partition and they are currently in the C:\ partition.
> >
> > How can this be done ?
>
>|||What problem are you having with TempDB? Did you move Master and model as
well?
I never tried to do this with system DBs only my own created DBs.
"scuba19" wrote:
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
> > The easiest way would be to detach the databases, Move the MDF and LDF files
> > to the new location and re-attach the databases.
> > You can do this all in Enterprise Manager. You can also set the default
> > location to your new location of these databases thru EM Properties of the
> > Server.
> >
> > HTH,
> > Joe
> >
> >
> > "scuba19" wrote:
> >
> > > I have an SQL server with a number of databses including :
> > >
> > > Sharepoint and SMS. I would like to move these databases to the D
> > > partition and they are currently in the C:\ partition.
> > >
> > > How can this be done ?

Wednesday, March 28, 2012

Lock resources in Bpool ??

Lock resources in Bpool ??

Lock resources in Bpool ??

Wednesday, March 21, 2012

Lock a row in the database

Location of SQL

Location of SQL

Location of SQL

sql local data

Blog Archive

About Me