sql local data: March 2012

Friday, March 30, 2012

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
>
statement.[vbcol=seagreen]|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.[vbcol=seagreen]
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:
> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:
>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>> Mike
>> The optimiser escalates to a full table lock when it thinks it will be
>> more
>> efficient way of processing the data than using page locks. There are a
>> number of things you can do if you think it should not be performing a
>> table
>> lock.
>> Are your statistics up to date? If you don't regularly update statistics,
>> the optimiser is working with at least one hand tied behind it's back. If
>> the
>> statistics are not up tp date it may be making incorrect choices.
>> Do you have suitable indexes? If you have indexes on the data you are
>> updating (especially a clustered index) it will be easier for the
>> optimiser
>> to use page locks rather than table locks, if suitable.
>> It may be you are updating enough of the table that a table lock is the
>> correct option. In this case you can split up your updates into smaller
>> transactions, small enough that the optimiser uses page locks.
>> Hope this helps
>> John
>> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>
>
--
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:
> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> > Mike
> >
> > The optimiser escalates to a full table lock when it thinks it will be
> > more
> > efficient way of processing the data than using page locks. There are a
> > number of things you can do if you think it should not be performing a
> > table
> > lock.
> >
> > Are your statistics up to date? If you don't regularly update statistics,
> > the optimiser is working with at least one hand tied behind it's back. If
> > the
> > statistics are not up tp date it may be making incorrect choices.
> >
> > Do you have suitable indexes? If you have indexes on the data you are
> > updating (especially a clustered index) it will be easier for the
> > optimiser
> > to use page locks rather than table locks, if suitable.
> >
> > It may be you are updating enough of the table that a table lock is the
> > correct option. In this case you can split up your updates into smaller
> > transactions, small enough that the optimiser uses page locks.
> >
> > Hope this helps
> >
> > John
> >
> > "Mike" wrote:
> >
> >> It appears that when we issue an UPDATE statement on a table, it locks
> >> the
> >> WHOLE table so that any other users can not complete SELECT queries on
> >> the
> >> table (without using "WITH (nolock)" in the SELECT statement)
> >>
> >> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
> >> an
> >> update, so that maybe it takes longer, but it only locks 1 row or page at
> >> a
> >> time?
> >>
> >>
> >>
>
>

Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der EeckenI don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
--
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking levels were across different
>> versions of SQL, i.e. row vs. page vs. table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the other? Guess versions 4.2 and
>> 6.x were page-locking no matter what if I remember correctly, but want to make sure before I tell
>> my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||They called it INSERT-ROW locks in SQL 6.5, but it was not row level
locking. It was just a new type of page lock that allowed multiple inserts
on the same page.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||Thanks for the answers all of you ppl! Got the answer to my colleague
correct after all. But I really appreciate your help, and I really feel
honoured since I only got replies from MVP's..Obviously my question was kind
of obscure.
Nite,
Jan Van der Eecken
Cape Town
South Africa
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23spoQjlWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> They called it INSERT-ROW locks in SQL 6.5, but it was not row level
> locking. It was just a new type of page lock that allowed multiple inserts
> on the same page.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
>> 6.5 added some possibility to get some type of row locks for INSERTs.
>> Sorry to be vague, but it was a long time ago. As I recall, you had to
>> satisfy a number of conditions (probably what indexes you had) in order
>> to get some concurrency on the page for INSERT operations.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the
>> other? Guess versions 4.2 and 6.x were page-locking no matter what if I
>> remember correctly, but want to make sure before I tell my colleagues
>> rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>>
>

Locking

Hi all,
SQL server doesnt provide any kind of configuration to
control granularity of locking on tables. As per books
online it is managed dynamically.
I have a major problem with my SQL Server, during
execution of one SP, on one table with 400+ rows it
acquires 600,000 locks. My profiler shows
whooping 'Lock:Acquired' on this particular table.
Can somebody please throw some light on this? Why SQL
server doesn't promote it to single table level lock? Is
there any bug in SQL?
thanks very much
Himanshu JaniYou probably want to keep working on why this happens in the first place (although there's not much
to go on for us), but here are a couple of options for immediate fix:
Specify lock hint in the query (TABLOCK, for instance).
Configure the index used to not allow row locks (sp_indexoption).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Himanshu Jani" <anonymous@.discussions.microsoft.com> wrote in message
news:2c84301c393cd$c746cab0$a601280a@.phx.gbl...
> Hi all,
> SQL server doesnt provide any kind of configuration to
> control granularity of locking on tables. As per books
> online it is managed dynamically.
> I have a major problem with my SQL Server, during
> execution of one SP, on one table with 400+ rows it
> acquires 600,000 locks. My profiler shows
> whooping 'Lock:Acquired' on this particular table.
> Can somebody please throw some light on this? Why SQL
> server doesn't promote it to single table level lock? Is
> there any bug in SQL?
> thanks very much
> Himanshu Jani

Locking

Our database hase been designed around the optimistic locking strategy - this has worked well for many years- however we have now ported our database to ms sql server - where the default locking strategy is pessimistic - This is fine for most transactions - but I badly need to change the default to optimistic for one particular area of the system - how do I do this at the server side
In short - How do I change the default locking strategy from pessimistic to optimistic
We are using Borland delphi and bde to communicate with the database.Actually the default is Optimistic as far as SQL Server itself is concerned.
You should investigate how your connections are being made to the server and
what they are setting. For instance you may be set to use the SERIALIZABLE
transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I would
check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL in
BooksOnLine. You can useProfiler to see what your front end is sending to
SQL Server when you connect.
--
Andrew J. Kelly SQL MVP
"scimitar" <julian@.phoenix7.co.uk> wrote in message
news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> Our database hase been designed around the optimistic locking strategy -
this has worked well for many years- however we have now ported our database
to ms sql server - where the default locking strategy is pessimistic - This
is fine for most transactions - but I badly need to change the default to
optimistic for one particular area of the system - how do I do this at the
server side.
> In short - How do I change the default locking strategy from pessimistic
to optimistic ?
> We are using Borland delphi and bde to communicate with the database.|||Andrew, Pessimistic is the default.
The only way to obtain optimistic concurrency control is from an application
using some sort of GUID or timestamp or you would have to use some
combination of nolock,update locks ,etc . If you provide some more info, Im
pretty sure someone could assist you
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:edbBIXc%23DHA.488@.TK2MSFTNGP12.phx.gbl...
> Actually the default is Optimistic as far as SQL Server itself is
concerned.
> You should investigate how your connections are being made to the server
and
> what they are setting. For instance you may be set to use the
SERIALIZABLE
> transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I
would
> check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL in
> BooksOnLine. You can useProfiler to see what your front end is sending to
> SQL Server when you connect.
> --
> Andrew J. Kelly SQL MVP
>
> "scimitar" <julian@.phoenix7.co.uk> wrote in message
> news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> > Our database hase been designed around the optimistic locking strategy -
> this has worked well for many years- however we have now ported our
database
> to ms sql server - where the default locking strategy is pessimistic -
This
> is fine for most transactions - but I badly need to change the default to
> optimistic for one particular area of the system - how do I do this at the
> server side.
> >
> > In short - How do I change the default locking strategy from pessimistic
> to optimistic ?
> >
> > We are using Borland delphi and bde to communicate with the database.
>|||> The only way to obtain optimistic concurrency control is from an
application
> using some sort of GUID or timestamp
Although a rowversion column makes the task easier, even without one you can
compare column values with those initially retrieved. For example:
UPDATE MyTable
SET MyData = @.NewValue
WHERE MyPK = @.MyPK AND
MyData = @.OldValue
IF @.@.ROWCOUNT = 0
RAISERROR('Data modified by another user', 16, 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uDp3$We%23DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Andrew, Pessimistic is the default.
> The only way to obtain optimistic concurrency control is from an
application
> using some sort of GUID or timestamp or you would have to use some
> combination of nolock,update locks ,etc . If you provide some more info,
Im
> pretty sure someone could assist you
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:edbBIXc%23DHA.488@.TK2MSFTNGP12.phx.gbl...
> > Actually the default is Optimistic as far as SQL Server itself is
> concerned.
> > You should investigate how your connections are being made to the server
> and
> > what they are setting. For instance you may be set to use the
> SERIALIZABLE
> > transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I
> would
> > check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL
in
> > BooksOnLine. You can useProfiler to see what your front end is sending
to
> > SQL Server when you connect.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "scimitar" <julian@.phoenix7.co.uk> wrote in message
> > news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> > > Our database hase been designed around the optimistic locking
strategy -
> > this has worked well for many years- however we have now ported our
> database
> > to ms sql server - where the default locking strategy is pessimistic -
> This
> > is fine for most transactions - but I badly need to change the default
to
> > optimistic for one particular area of the system - how do I do this at
the
> > server side.
> > >
> > > In short - How do I change the default locking strategy from
pessimistic
> > to optimistic ?
> > >
> > > We are using Borland delphi and bde to communicate with the database.
> >
> >
>|||Were you using previously Interbase?
"scimitar" <julian@.phoenix7.co.uk> wrote in message
news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> Our database hase been designed around the optimistic locking strategy -
this has worked well for many years- however we have now ported our database
to ms sql server - where the default locking strategy is pessimistic - This
is fine for most transactions - but I badly need to change the default to
optimistic for one particular area of the system - how do I do this at the
server side.
> In short - How do I change the default locking strategy from pessimistic
to optimistic ?
> We are using Borland delphi and bde to communicate with the database.

Locking

I've got the following stored procedure I'm working on:
-- ============================================= -- Create procedure postCash
-- ============================================= USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================> -- Create procedure postCash
> -- =============================================> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.
> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.
>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.sql

Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-JTim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:
> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:
> > Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> > there is no need to even think about excluding anything else when just
> > reading. For example SELECT * from pubs..authors aquires two locks - an
> > Intent-Exclusive table level lock on the clustered index and another
> > Intent-Exclusive table on the non-clustered index.
> >
> > I believe this is excessive. Can anyone please explain?
> >
> > Thanks in advance,
> > Tim G-J

Locking

Can you change the locking granularity on a single table
at the database level? I want to set up a single table at
table level locking without the programmer having to send
a hint.
Thanks for the help,
JoelThis is a multi-part message in MIME format.
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_indexoption in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joel" <joel.griffin@.teijinmonofil.com> wrote in message =news:00b201c35518$a96b8d80$a101280a@.phx.gbl...
Can you change the locking granularity on a single table at the database level? I want to set up a single table at table level locking without the programmer having to send a hint.
Thanks for the help,
Joel
--=_NextPart_000_003D_01C354F8.393E6980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Check out sp_indexoption in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Joel" wrote in message news:00b201c35518$a9=6b8d80$a101280a@.phx.gbl...Can you change the locking granularity on a single table at the database =level? I want to set up a single table at table level locking =without the programmer having to send a hint.Thanks for the help,Joel

--=_NextPart_000_003D_01C354F8.393E6980--

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
Dan
It depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
statement.[vbcol=seagreen]
|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the[vbcol=seagreen]
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.
>
sql

Locking

I've got the following stored procedure I'm working on:
-- =============================================
-- Create procedure postCash
-- =============================================
USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?
Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================
> -- Create procedure postCash
> -- =============================================
> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>
|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.

> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.

>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.

Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-J
Tim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:

> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J
|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
[vbcol=seagreen]
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:

Locking

I have a Stored Procedure serving an web application that returns data from
a
number of tables.
This dat resides on three seperate tables, two of which I join in a single
select statement, the third I use to lookup data dependent on some data
retrieved from the original select statement.
On some infrequent occasions the user will receive an error message stating
they are the victim of a deadlock etc, etc.
I have checked the logs on SQL but find no error message pertaining to this.
I then created a script in QA that access this Stored procedure in a loop at
1000 and then 10000 times, but whenever I run it, I receive no error
messages, just the recordset(s) returned correctly.
The data being accessed is live and updateable 24/7 by other users. This
Stored Procedure does not moify the data in any shape or form, simply reads
it.
Another developer I work with has suggested using the NOLOCK hint on all
reads, but I am loathed to use this as it may cause dirty reads.
1. Does the scenario indicate this is truly a deadlock situation or could
there be another cause for this error message (the web app is in .NET)
2. What is the general point of view on using the NOLOCK hint and should it
be used as a matter of course or very sparingly?
I am obliged for any assistance or guidance in this matter
(Note: This question has also been posted on SQLTeam Site)> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
Most probably. You didn't post the exact error message, but the way you phra
sed it sounds like the
error was indeed an SQL Server error message.

> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
Only use NOLOCK if you can foresee the consequences and accept them. But I s
uggest you look into
other possibilities first. These can include things like keeping locks for a
s short time as possible
(do not include things in the transaction that doesn't have to be there, tun
e queries etc),
accessing the objects in the same sequence, adding or removing indexes.
The reason you didn't see deadlocks in your loop is that you probably only h
ad one connection.
Deadlock (generally) occurs when you have two or more workers accessing data
in a way so that the
deadlock arises.
You can Google on deadlock (etc) and find plenty of text on the subject, inc
luding SQL server
specific text.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>I have a Stored Procedure serving an web application that returns data from
a
> number of tables.
> This dat resides on three seperate tables, two of which I join in a single
> select statement, the third I use to lookup data dependent on some data
> retrieved from the original select statement.
> On some infrequent occasions the user will receive an error message statin
g
> they are the victim of a deadlock etc, etc.
> I have checked the logs on SQL but find no error message pertaining to thi
s.
> I then created a script in QA that access this Stored procedure in a loop
at
> 1000 and then 10000 times, but whenever I run it, I receive no error
> messages, just the recordset(s) returned correctly.
> The data being accessed is live and updateable 24/7 by other users. This
> Stored Procedure does not moify the data in any shape or form, simply read
s
> it.
> Another developer I work with has suggested using the NOLOCK hint on all
> reads, but I am loathed to use this as it may cause dirty reads.
> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
> I am obliged for any assistance or guidance in this matter
> (Note: This question has also been posted on SQLTeam Site)|||The error message was thus...
Transaction (Process ID 99) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable
dataTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ...etc, etc
which strongly indicates that SQL generated it.
In respect of the loop test, I was attempting emulate the web application
when it requests the data to ascertain whether it was on the server or not.
I
recently read an artcle on MSDN that indicated deadlocking problems could be
caused by Applications opening dual threads that block each other - leading
to a deadlock situation - but it didn't state what error message would be
created in this case over and above the regular SQL 1204/1205 error.
Any further thoughts of where to look for this?
"Tibor Karaszi" wrote:

> Most probably. You didn't post the exact error message, but the way you ph
rased it sounds like the
> error was indeed an SQL Server error message.
>
> Only use NOLOCK if you can foresee the consequences and accept them. But I
suggest you look into
> other possibilities first. These can include things like keeping locks for
as short time as possible
> (do not include things in the transaction that doesn't have to be there, t
une queries etc),
> accessing the objects in the same sequence, adding or removing indexes.
> The reason you didn't see deadlocks in your loop is that you probably only
had one connection.
> Deadlock (generally) occurs when you have two or more workers accessing da
ta in a way so that the
> deadlock arises.
> You can Google on deadlock (etc) and find plenty of text on the subject, i
ncluding SQL server
> specific text.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
> news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>sql

Locking

I have a series of stored procedures that are called from an outside
application. This application supports multi-threading, so I can have 10
instances of these stored procedures running at once. This is causing a grea
t
deal of locking. As I am working through the code, the biggest offenders are
"exists" and "not exists" statements. Each one reduces performance
dramatically. Is there another way to get the same effect and not use
"exists" and "not exists"? "In" and "Not In" are worse for locking.
Thanks for your help.Can we see some code and DDL?
AMB
"joesql" wrote:

> I have a series of stored procedures that are called from an outside
> application. This application supports multi-threading, so I can have 10
> instances of these stored procedures running at once. This is causing a gr
eat
> deal of locking. As I am working through the code, the biggest offenders a
re
> "exists" and "not exists" statements. Each one reduces performance
> dramatically. Is there another way to get the same effect and not use
> "exists" and "not exists"? "In" and "Not In" are worse for locking.
> Thanks for your help.|||Have you checked the execution plans for your EXISTS/NOT EXISTS queries? Are
they doing a table or index scan? Make sure you get an index s

for best
performance.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"joesql" <joesql@.discussions.microsoft.com> wrote in message
news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
I have a series of stored procedures that are called from an outside
application. This application supports multi-threading, so I can have 10
instances of these stored procedures running at once. This is causing a
great
deal of locking. As I am working through the code, the biggest offenders are
"exists" and "not exists" statements. Each one reduces performance
dramatically. Is there another way to get the same effect and not use
"exists" and "not exists"? "In" and "Not In" are worse for locking.
Thanks for your help.|||It's hard to say without actually seeing what you are doing but it sounds
like there are no indexes for the EXISTS to work against. With a proper
index the EXISTS should be extremely efficient. Also make sure the stored
procedures are being called with the owner specified. exec dbo.yoursp
Andrew J. Kelly SQL MVP
"joesql" <joesql@.discussions.microsoft.com> wrote in message
news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
>I have a series of stored procedures that are called from an outside
> application. This application supports multi-threading, so I can have 10
> instances of these stored procedures running at once. This is causing a
> great
> deal of locking. As I am working through the code, the biggest offenders
> are
> "exists" and "not exists" statements. Each one reduces performance
> dramatically. Is there another way to get the same effect and not use
> "exists" and "not exists"? "In" and "Not In" are worse for locking.
> Thanks for your help.|||All tables have proper indexes and I am using dbo. for all calls.
"Andrew J. Kelly" wrote:

> It's hard to say without actually seeing what you are doing but it sounds
> like there are no indexes for the EXISTS to work against. With a proper
> index the EXISTS should be extremely efficient. Also make sure the stored
> procedures are being called with the owner specified. exec dbo.yoursp
>
> --
> Andrew J. Kelly SQL MVP
>
> "joesql" <joesql@.discussions.microsoft.com> wrote in message
> news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
>
>|||Here is a sample of code:
update #temp_events
set external_calc_ind = 'Y'
from pending_event pe,
pending_commission pc,
#temp_events e
where pe.pending_event_id = e.pending_event_id
and pc.pending_event_id = pe.pending_event_id
and not exists(select 1
from comm_event_process
where comm_event_id = pe.comm_event_id )
"Alejandro Mesa" wrote:
> Can we see some code and DDL?
>
> AMB
> "joesql" wrote:
>|||You;re sure there's an index on comm_event_id in the comm_event_process tab
le?
"joesql" wrote:
> Here is a sample of code:
> update #temp_events
> set external_calc_ind = 'Y'
> from pending_event pe,
> pending_commission pc,
> #temp_events e
> where pe.pending_event_id = e.pending_event_id
> and pc.pending_event_id = pe.pending_event_id
> and not exists(select 1
> from comm_event_process
> where comm_event_id = pe.comm_event_id )
> "Alejandro Mesa" wrote:
>|||Try,
update #temp_events
set external_calc_ind = 'Y'
where exists (
select
*
from
pending_event as pe
inner join
pending_commission as pc
on pc.pending_event_id = pe.pending_event_id
left join
comm_event_process as c
on c.comm_event_id = pe.comm_event_id
where
pe.pending_event_id = #temp_events.pending_event_id
and c.comm_event_id is null
);
be sure to have indexes in:
- pending_event(pending_event_id)
- pending_event(comm_event_id)
- pending_commission(pending_event_id)
- comm_event_process(comm_event_id)
- #temp_events(pending_event_id)
AMB
"joesql" wrote:
> Here is a sample of code:
> update #temp_events
> set external_calc_ind = 'Y'
> from pending_event pe,
> pending_commission pc,
> #temp_events e
> where pe.pending_event_id = e.pending_event_id
> and pc.pending_event_id = pe.pending_event_id
> and not exists(select 1
> from comm_event_process
> where comm_event_id = pe.comm_event_id )
> "Alejandro Mesa" wrote:
>|||That is the first thing I looked for. This is the primary key too, so it is
a
clustered index.
"CBretana" wrote:
> You;re sure there's an index on comm_event_id in the comm_event_process t
able?
> "joesql" wrote:
>|||This doesn't lock as bad, thanks for the input.
"Alejandro Mesa" wrote:
> Try,
> update #temp_events
> set external_calc_ind = 'Y'
> where exists (
> select
> *
> from
> pending_event as pe
> inner join
> pending_commission as pc
> on pc.pending_event_id = pe.pending_event_id
> left join
> comm_event_process as c
> on c.comm_event_id = pe.comm_event_id
> where
> pe.pending_event_id = #temp_events.pending_event_id
> and c.comm_event_id is null
> );
> be sure to have indexes in:
> - pending_event(pending_event_id)
> - pending_event(comm_event_id)
> - pending_commission(pending_event_id)
> - comm_event_process(comm_event_id)
> - #temp_events(pending_event_id)
>
> AMB
> "joesql" wrote:
>

Locked Stored Procedures

I am reasonably new to this so bear with me.

I have a copy of a SQL Server 2000 Database, and would like to see what the stored procedures are doing (as I think it is wrong).

in Server Management Studio the stored procedures have a lock symbol on them and when I try and view them I get the message:

TITLE: Microsoft SQL Server Management Studio

Script failed for StoredProcedure 'dbo.sp_procedure'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure '[dbo].[sp_procedureprocedure]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

This was developed by someone else, but I need to see it.

My question is, is there any way i can see the text of this stored procedure?

Thanks

Will

The stored procedure code has been encrypted. Apparently, whoever wrote the code didn't want others to read the code.

I suggest that you contact the person ( or vendor ) that supplied the stored procedure code and request a copy of the code.

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Can you connect to sqlserver at all on the server itself?
Do you know which version of sql2k5 you're running? Do "select @.@.version" to find out - this assumes you can connect to the server somehow.
If you're using sqlexpress version, the ability to connect remotely is off by default. You will need to use SQL Surface Area tool to change the setting.|||

Hey OJ, thanks for trying to get me out of this pickle!

No, I don't "think" I can connect to sql server. Don't I need to add the databases my app is using to sql server in order for my app to work? As I said, this is my first time moving an app to a server.

The result I get when running "select @.@.version" is SqlServer 2000! Dang, I don't even have it running on my own laptop in the right instance of sql server! I'm glad you had me check that out. I'll move everything over to 2005. I think I have the developer version. Whatever version of 2005 I have on my laptop, I should have on the server, which I can't reach at all. I just checked the version of 2005 I have, & it's 2005 v.9.00.1399.06.

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

Before you get yourself in big trouble with licensing, I suggest you take a closer look at your server and make sure it does not run a developer edition - dev edition means for development only and should not be used for any type of production.

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

1. Logon to server locally - either via remote desktop (mstsc /console) or at the console itself.
2. Execute "net start" to get a list of started services. Your sqlserver would be one if it's started. If it's not started yet, run "services.msc" to bring up services mgr and start it. You should find you sqlserver under "SQL Server (instance_name)".
3. Now startup sql surface area tool. I.e. Start -> Programs -> sql2k5 -> config tools -> sqlSAC (i.e. "C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe")
4. Select "SAC for services and connections"
5. Pick your sql instance and ensure Remote Connection is set to local & remote.
6. Restart your sqlserver service to take effect.
7. Now you should be able to connect to your sql instance remote from another computer.
8. If your NT login is part of the sql server's local admins, you should be able to run

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:
Hi everyone,
I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?
Thanks,

please post the full error here. there can be many causes for not able to connect.

Friday, March 30, 2012

locking

Locking

Locking

Locking

Locking

Locking

Locking

Locking

Locking

locking

Locking

locking

Locking

Locking

Locking

Locking

Locking

Locking

Locked Stored Procedures

locked out of sql server management studio

sql local data

Blog Archive

About Me