GRANT statements issue Sch-M locks....Why?

We are experiencing blocking situations with a application that issues GRANT
statements. It becomes blocked trying to take out a Sch-M lock while other
long running report processes are holding Sch-S locks.
I'm wondering:
1) Why is the non-DDL GRANT statement wanting to lock the schema?
2) Is there any way to prevent it, or to prevent the reporting processes
from taking out Sch-S locks?
Thanks!1) Because GRANT _is_ DDL.
2) You can run the reporting processes WITH(NOLOCK), but that might cause
them to fail occasionally.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
> We are experiencing blocking situations with a application that issues
> GRANT
> statements. It becomes blocked trying to take out a Sch-M lock while
> other
> long running report processes are holding Sch-S locks.
> I'm wondering:
> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
> 2) Is there any way to prevent it, or to prevent the reporting processes
> from taking out Sch-S locks?
> Thanks!|||Hi
Isn't GRANT DCL ?
"Jacco Schalkwijk" wrote:

> 1) Because GRANT _is_ DDL.
> 2) You can run the reporting processes WITH(NOLOCK), but that might cause
> them to fail occasionally.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
>
>|||What if someone tries to drop the object while you are in the process of
GRANT'ing ?

> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
Gopi
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
> We are experiencing blocking situations with a application that issues
> GRANT
> statements. It becomes blocked trying to take out a Sch-M lock while
> other
> long running report processes are holding Sch-S locks.
> I'm wondering:
> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
> 2) Is there any way to prevent it, or to prevent the reporting processes
> from taking out Sch-S locks?
> Thanks!|||Yes, good question. I could understand it needing to take a Sch-S to protec
t
the object while granting, but I don't see how it is a schema MODIFICATION t
o
grant permissions.
"gopi" wrote:

> What if someone tries to drop the object while you are in the process of
> GRANT'ing ?

good statistic for blocking

We are having an occasional problem occur where a
process will not stop blocking.

We are trying to trace the problem, but in the interim,
I would like to set up an alert that notifies me when a
process has been blocking for too long.

Are any of the lock wait times good statistics to use
for such an alert? If not, is there anything else I could
look at from the alert level?

If I had to, I could periodically create a table of
sysprocess spids that are at the top of blocking chains,
then test for a spid that lingers. I'm hoping I can
avoid this and use the built-in monitoring instead, though.

Thanks!
GeoffWe're also monitoring open transactions kept for a specific threshold, depending on an app. In addition, look at sp_monitor system stored procedure and you may get some ideas on what and how it can be accomplished.|||What information do you have so far concerning the locking issue - how long is it blocking, how did you discover that blocking was occuring ... ?|||Thanks, rdjabarov. Active Transactions is a good idea, since they
don't ever seem to build up higher than 4 or 5, if today's performance
monitor results can be trusted.

rnealjr:

When we encounter the problem, the blocking processes seem like
they would continue blocking perpetually. The only thing I can do to
solve the situation is to go in and kill the offending process. I am
trying to address the reason this occurs, but in the meantime I need
to fix it by brute force.

We are running a Java application that accesses the data using
a CORBA-based middle tier (Versata, in case you've heard of it).
The CORBA objects use the MS JDBC driver to hit the database,
which is MS SQL 2000.

I just tried upgrading the driver to sp2 and SQL to sp3a last night.
Things were going well this morning, but I think I caused some
trouble when I ran DBCC SHOWCONTIG while following a performance
tuning doc.

Anyway, I figured I'd show an example of an offending process,
obtained by using sp_blocker_pss80:

========================================

SPID: 144
STATUS: sleeping
BOLCKED: 0
OPEN_TRAN: 1
WAITRESOURCE: 8:1:269596
WAITTYPE: 0x0000
WAITTIME: 0
CMD: AWAITING COMMAND
LATWAITTYPE: PAGEIOLATCH_SH
CPU: 30
PHYSICAL_IO: 26
MEMUSAGE: 26
LASTBATCH: 2004-01-14 08:48:28.003
LOGINTIME: 2004-01-14 08:41:00.490
NETADDRESS 000000000000
NET_LIBRARY: TCP/IP
DBID: 8
ECID: 0
KPID: 0
HOSTNAME: spnvls00.na01.crl.com
HOSTPROCESS: 0
LOGINNAME: ilims
PRGRAMNAME:
NT_DOMAIN:
NT_USERNAME:
UID: 1
SID: 0xCDEB103BDA944A418B51092B9253F78D...
SQL_HANDLE: 0x0000000000000000000000000000000000000000
STMT_START: 0
STMT_END: 0

========================================

The thing I find weird, although a more experienced eye may not, is
that it's sleeping and "AWAITING COMMAND", yet it still has 1 open
transaction. It seems to me like this kind of situation could cause
a lingering block.

Thanks again!|||99% of the time it IS the main reason for blocking, along with long running transactions.|||When this happens again, you can use enterprise manager. Go to management -> current activity -> locks/processid - this will help you locate the process that is blocking and will show all the lock information associated with that process. Open that process - that will show up to 255 characters what action is causing the block which will help you locate what/where the problem is. Always make sure that you refresh the "current activity" first - otherwise, you might be looking at old process info. Once you have the information you need, then kill the process.|||Below is the script I've been using since 6.5 version to identify the blocking chain:

set statistics time off
set nocount on
set statistics io off
if charindex('6.5', @.@.version) = 0 begin
exec ('set statistics profile off')
end
--go
select
spid,
BlockedBy=blocked,
Status=convert(char(10), status),
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
WHERE Exists (
select
spid,
blocked,
status,
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses victim (NOLOCK)
where blocker.spid = victim.blocked)
UNION
select
spid,
blocked,
Status=convert(char(10), status),
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
where blocked != 0
ORDER BY blocked, spid

begin tran
create table #tmp (spid int null, BlockedBy int null)
commit tran
insert #tmp (spid, BlockedBy) select spid, BlockedBy from (
select
spid,
BlockedBy=blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
WHERE Exists (
select
spid,
blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses victim (NOLOCK)
where blocker.spid = victim.blocked)
UNION
select
spid,
blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
where blocked != 0 ) x
ORDER BY blockedby, spid
if @.@.rowcount > 0 begin
set nocount on
declare @.spid int, @.cmd varchar(255), @.cmd1 varchar(255)
select @.spid = min(spid) from #tmp where BlockedBy = 0
while @.spid is not null begin
select @.cmd = 'dbcc inputbuffer(' + convert(varchar(25), @.spid) + ')'
set @.cmd1= '!!!!!!!!!!!!!!!!!INPUTBUFFER information for blocking SPID ' + convert(varchar(25), @.spid) + ':'
print ''
print '************************************************* ***************************************'
print @.cmd1
print '************************************************* ***************************************'
exec (@.cmd)
-- print '************************************************* ***************************************'
print ''
select @.spid = min(spid) from #tmp where BlockedBy = 0 and spid > @.spid
end
print '************************************************* ****************************************'
print ''
print 'LIST OF AFFECTED PROCESSES'
print '**************************'
select @.spid = min(spid) from #tmp
while @.spid is not null begin
select @.cmd = 'dbcc inputbuffer(' + convert(varchar(25), @.spid) + ')'
select @.cmd1= 'INPUTBUFFER informtion for SPID ' + convert(varchar(25), @.spid) + ':'
print @.cmd1
exec (@.cmd)
print ''
select @.spid = min(spid) from #tmp where spid > @.spid
end
end else
print 'No blocking detected!'
go
drop table #tmp
go

GK - Blocking the unauthorized use of the SQL Management Console ( or third party tool

Hi to all,
Whenever SQL server is installed onto a server it can be accessed by the SQL
MC and easily registered for use. Whenever someone not authorized installs
the SQL MC or a third party tool with the same capabilities all database con
tent can be altered !! THIS
IS A VERY SERIOUS THREAD !
Is there a way to prevent this?
GKramer
The NetherlandsCan you please elaborate on your concerns? Only users who have been granted
access to SQL Server can connect and access database objects. When SQL
Server is initially installed, only members of the local Administrators
group can connect. Once you grant an account access to SQL Server, they can
use any tool but are limited by the SQL Server permissions you have granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:2BDA7DD5-3FDE-45CC-BD95-BDCD8AD01BDE@.microsoft.com...
> Hi to all,
> Whenever SQL server is installed onto a server it can be accessed by the
SQL MC and easily registered for use. Whenever someone not authorized
installs the SQL MC or a third party tool with the same capabilities all
database content can be altered !! THIS IS A VERY SERIOUS THREAD !
> Is there a way to prevent this?
> GKramer
> The Netherlands
>