deadlock error

I am getting quite a few deadlock errors where both sessions are
trying to execute sp_execsql according to the the trace information in
the error log (see below). The database is being asscessed by an
application written in .NET, as well as a few people using Query
Analyzer. This seems to be happening relative randomly - can't pin it
to any specific circumstances. Any thoughts would be appreciated.

RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x3738dbe0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55
ECID:0
SPID: 55 ECID: 0 Statement Type: CONDITIONAL Line #: 47
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)
Value:0x23297b80 Cost:(0/12C)

Node:2
RID: 8:1:267:91 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x3efae340 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52
ECID:0
SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x483FB570)
Value:0x37c0e060 Cost:(0/138)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)
Value:0x23297b80 Cost:(0/12C)[posted and mailed, please reply in news]

Scot Schneider (sschneider@.ebags.com) writes:
> I am getting quite a few deadlock errors where both sessions are
> trying to execute sp_execsql according to the the trace information in
> the error log (see below). The database is being asscessed by an
> application written in .NET, as well as a few people using Query
> Analyzer. This seems to be happening relative randomly - can't pin it
> to any specific circumstances. Any thoughts would be appreciated.

Both SqlClient and OleDb Client calls sp_executesql to run parameterized
statements, so if your application is doing this a lot, this could be
about anything.

The deadlock itself seems to be due to both processes holding a shared
locks, and both process wants an exclusive lock on the resource on whic
the other process have a shared lock.

There are two things in this deadlock trace that I find a little funny:

> SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115

If you app is submitting dynamic SQL statements, line 115 is a pretty
high line number. Could it be that your app is actually using stored
procedures, but is using CommandType Text rather than StoredProcedure?
Changing this could give you somewhat better performance and somewhat
more informative deadlock traces.

> RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2

Both locks are on tables without clustered indexes. This may be fully
conscious decsion, but the recommendation is to always have a
clustered index on your tables. There is no guarantee that the dealocks
goes away if you add a clustered index, but it could happen. At least
with a clustered index, it is easier to find the tables involved in
the deadlocl.

To dig out which tables that are involved in this dead lock you would
do:

SELECT db_name(8) -- gives you the database name.

DBCC TRACEON (3604, 1)
DBCC PAGE(8,1,617)
This gives you the header information for this page.

In the middle of this output, in the leftmost column is m_objId. This
is the object of the table. Copy and paste the value of m_objID, and run
SELECT object_name() for that value.

The value 37 is the row number, I would guess within that page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DBREINDEX at threshold for all databases

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on
all indexes in a database that have a logical fragmentation percentage above
a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run
this proc with DBREINDEX instead and I want to schedule it wly for all
exising user databases. (The Database Maintenance Wizard is too
inefficient.) If a new database gets added to the environment, I want the
proc to dynamically pick up that new database.
The problem is that this proc has to be executed within the database to be
defragged. I'm having trouble modifying it to loop for each existing
database.
I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
having some problems. This is probably a pretty basic type of maintenance
procedure. Does anyone already have this coded that they would share?I actually have something that may work for you - but not without a little
work. I was trying to do the same thing - a wly job that would run on an
y
existing user db's. If you run this, it will pick up all user databases. I
use PRINT @.SQL instead of EXEC because I was unable to get it to execute the
DBREINDEX without error. And right now I'm taking the results of that and
driving the job. If you're able to get around that, please advise.
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC
DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid >6
PRINT @.SQL
-- Lynn
"Stephanie" wrote:

> There is a proc in Books Online that allows you to execute a INDEXDEFRAG o
n
> all indexes in a database that have a logical fragmentation percentage abo
ve
> a specific limit. IT useds SHOWCONTIG and a temporary table. I want to r
un
> this proc with DBREINDEX instead and I want to schedule it wly for all
> exising user databases. (The Database Maintenance Wizard is too
> inefficient.) If a new database gets added to the environment, I want the
> proc to dynamically pick up that new database.
> The problem is that this proc has to be executed within the database to be
> defragged. I'm having trouble modifying it to loop for each existing
> database.
> I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
> having some problems. This is probably a pretty basic type of maintenance
> procedure. Does anyone already have this coded that they would share?
>|||Maybe this helps:
http://milambda.blogspot.com/2005/0...in-current.html
It needs a wrapper that will execute it for each database, and you need to
propagate the db_id value through to the dbcc call (currently the value of
db_id is 0 - current database).
ML

dboption

I have quite a few issues with my sql 2000 server.
1. when execute sp_dboption 'northwind', 'read only', 'true'
I got the following messages:
Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
Database option 'read only' does not exist.
It seems that all sp_dboption command are not allowed in the server any more
.
2. When I tried to drop a database, the checkbox for
"delete backup and restore history for the database" is grayed out. I was
not able to check this checkbox any more.
3. When I try to create a new database diagram, got the following error
message:
You do not have sufficient privilege to create a new database diagram
4. I tried to following the instructions suggested from knowledge base:
1. In SQL Enterprise Manager, move to the affected database.
2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
database role of the dtproperties table.
3. Grant EXEC permissions for the public database role to all these stored
procedures:
dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_whocheckedout
dt_whocheckedout_u
I check all the checkbox for the above right from role-pulic(permission),
after I hit apply and go back to see if the changes have been applied to the
public role, I found that all the checkbox that I checked could not be saved
and they were unchecked.
I have more than 20 databases on this server, the above problem occured to
all the databases including master database.
Could anyone provide any help? Could it be possible that all the database
options to public role have been disabled by mistakes? What are the command
to enable all the dboptions for master database? Is master database
corrupted?
Thank you very much for your help.Hi
Something like:
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only' , @.optvalue =
'true'
should work fine.
Execute permissions to change an option (using sp_dboption with all
parameters) default to members of the sysadmin and dbcreator fixed server
roles and the db_owner fixed database role, execute permissions to display
the options currently set in a database default to all users, therefore
check if you can
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only'
If not it would seem that someone has changed the options.
John
"she" <she@.discussions.microsoft.com> wrote in message
news:081233B1-6917-4C19-BB33-4D326DDC4DE8@.microsoft.com...
>I have quite a few issues with my sql 2000 server.
> 1. when execute sp_dboption 'northwind', 'read only', 'true'
> I got the following messages:
> Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
> Database option 'read only' does not exist.
> It seems that all sp_dboption command are not allowed in the server any
> more.
> 2. When I tried to drop a database, the checkbox for
> "delete backup and restore history for the database" is grayed out. I was
> not able to check this checkbox any more.
> 3. When I try to create a new database diagram, got the following error
> message:
> You do not have sufficient privilege to create a new database diagram
> 4. I tried to following the instructions suggested from knowledge base:
> 1. In SQL Enterprise Manager, move to the affected database.
> 2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
> database role of the dtproperties table.
> 3. Grant EXEC permissions for the public database role to all these stored
> procedures:
> dt_addtosourcecontrol
> dt_addtosourcecontrol_u
> dt_adduserobject
> dt_adduserobject_vcs
> dt_checkinobject
> dt_checkinobject_u
> dt_checkoutobject
> dt_checkoutobject_u
> dt_displayoaerror
> dt_displayoaerror_u
> dt_droppropertiesbyid
> dt_dropuserobjectbyid
> dt_generateansiname
> dt_getobjwithprop
> dt_getobjwithprop_u
> dt_getpropertiesbyid
> dt_getpropertiesbyid_u
> dt_getpropertiesbyid_vcs
> dt_getpropertiesbyid_vcs_u
> dt_isundersourcecontrol
> dt_isundersourcecontrol_u
> dt_removefromsourcecontrol
> dt_setpropertybyid
> dt_setpropertybyid_u
> dt_validateloginparams
> dt_validateloginparams_u
> dt_vcsenabled
> dt_verstamp006
> dt_whocheckedout
> dt_whocheckedout_u
> I check all the checkbox for the above right from role-pulic(permission),
> after I hit apply and go back to see if the changes have been applied to
> the
> public role, I found that all the checkbox that I checked could not be
> saved
> and they were unchecked.
> I have more than 20 databases on this server, the above problem occured to
> all the databases including master database.
> Could anyone provide any help? Could it be possible that all the database
> options to public role have been disabled by mistakes? What are the
> command
> to enable all the dboptions for master database? Is master database
> corrupted?
> Thank you very much for your help.
>

dboption

I have quite a few issues with my sql 2000 server.
1. when execute sp_dboption 'northwind', 'read only', 'true'
I got the following messages:
Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
Database option 'read only' does not exist.
It seems that all sp_dboption command are not allowed in the server any more.
2. When I tried to drop a database, the checkbox for
"delete backup and restore history for the database" is grayed out. I was
not able to check this checkbox any more.
3. When I try to create a new database diagram, got the following error
message:
You do not have sufficient privilege to create a new database diagram
4. I tried to following the instructions suggested from knowledge base:
1. In SQL Enterprise Manager, move to the affected database.
2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
database role of the dtproperties table.
3. Grant EXEC permissions for the public database role to all these stored
procedures:
dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_whocheckedout
dt_whocheckedout_u
I check all the checkbox for the above right from role-pulic(permission),
after I hit apply and go back to see if the changes have been applied to the
public role, I found that all the checkbox that I checked could not be saved
and they were unchecked.
I have more than 20 databases on this server, the above problem occured to
all the databases including master database.
Could anyone provide any help? Could it be possible that all the database
options to public role have been disabled by mistakes? What are the command
to enable all the dboptions for master database? Is master database
corrupted?
Thank you very much for your help.
Hi
Something like:
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only' , @.optvalue =
'true'
should work fine.
Execute permissions to change an option (using sp_dboption with all
parameters) default to members of the sysadmin and dbcreator fixed server
roles and the db_owner fixed database role, execute permissions to display
the options currently set in a database default to all users, therefore
check if you can
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only'
If not it would seem that someone has changed the options.
John
"she" <she@.discussions.microsoft.com> wrote in message
news:081233B1-6917-4C19-BB33-4D326DDC4DE8@.microsoft.com...
>I have quite a few issues with my sql 2000 server.
> 1. when execute sp_dboption 'northwind', 'read only', 'true'
> I got the following messages:
> Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
> Database option 'read only' does not exist.
> It seems that all sp_dboption command are not allowed in the server any
> more.
> 2. When I tried to drop a database, the checkbox for
> "delete backup and restore history for the database" is grayed out. I was
> not able to check this checkbox any more.
> 3. When I try to create a new database diagram, got the following error
> message:
> You do not have sufficient privilege to create a new database diagram
> 4. I tried to following the instructions suggested from knowledge base:
> 1. In SQL Enterprise Manager, move to the affected database.
> 2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
> database role of the dtproperties table.
> 3. Grant EXEC permissions for the public database role to all these stored
> procedures:
> dt_addtosourcecontrol
> dt_addtosourcecontrol_u
> dt_adduserobject
> dt_adduserobject_vcs
> dt_checkinobject
> dt_checkinobject_u
> dt_checkoutobject
> dt_checkoutobject_u
> dt_displayoaerror
> dt_displayoaerror_u
> dt_droppropertiesbyid
> dt_dropuserobjectbyid
> dt_generateansiname
> dt_getobjwithprop
> dt_getobjwithprop_u
> dt_getpropertiesbyid
> dt_getpropertiesbyid_u
> dt_getpropertiesbyid_vcs
> dt_getpropertiesbyid_vcs_u
> dt_isundersourcecontrol
> dt_isundersourcecontrol_u
> dt_removefromsourcecontrol
> dt_setpropertybyid
> dt_setpropertybyid_u
> dt_validateloginparams
> dt_validateloginparams_u
> dt_vcsenabled
> dt_verstamp006
> dt_whocheckedout
> dt_whocheckedout_u
> I check all the checkbox for the above right from role-pulic(permission),
> after I hit apply and go back to see if the changes have been applied to
> the
> public role, I found that all the checkbox that I checked could not be
> saved
> and they were unchecked.
> I have more than 20 databases on this server, the above problem occured to
> all the databases including master database.
> Could anyone provide any help? Could it be possible that all the database
> options to public role have been disabled by mistakes? What are the
> command
> to enable all the dboptions for master database? Is master database
> corrupted?
> Thank you very much for your help.
>

dboption

I have quite a few issues with my sql 2000 server.
1. when execute sp_dboption 'northwind', 'read only', 'true'
I got the following messages:
Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
Database option 'read only' does not exist.
It seems that all sp_dboption command are not allowed in the server any more.
2. When I tried to drop a database, the checkbox for
"delete backup and restore history for the database" is grayed out. I was
not able to check this checkbox any more.
3. When I try to create a new database diagram, got the following error
message:
You do not have sufficient privilege to create a new database diagram
4. I tried to following the instructions suggested from knowledge base:
1. In SQL Enterprise Manager, move to the affected database.
2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
database role of the dtproperties table.
3. Grant EXEC permissions for the public database role to all these stored
procedures:
dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_whocheckedout
dt_whocheckedout_u
I check all the checkbox for the above right from role-pulic(permission),
after I hit apply and go back to see if the changes have been applied to the
public role, I found that all the checkbox that I checked could not be saved
and they were unchecked.
I have more than 20 databases on this server, the above problem occured to
all the databases including master database.
Could anyone provide any help? Could it be possible that all the database
options to public role have been disabled by mistakes? What are the command
to enable all the dboptions for master database? Is master database
corrupted?
Thank you very much for your help.Hi
Something like:
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only' , @.optvalue ='true'
should work fine.
Execute permissions to change an option (using sp_dboption with all
parameters) default to members of the sysadmin and dbcreator fixed server
roles and the db_owner fixed database role, execute permissions to display
the options currently set in a database default to all users, therefore
check if you can
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only'
If not it would seem that someone has changed the options.
John
"she" <she@.discussions.microsoft.com> wrote in message
news:081233B1-6917-4C19-BB33-4D326DDC4DE8@.microsoft.com...
>I have quite a few issues with my sql 2000 server.
> 1. when execute sp_dboption 'northwind', 'read only', 'true'
> I got the following messages:
> Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
> Database option 'read only' does not exist.
> It seems that all sp_dboption command are not allowed in the server any
> more.
> 2. When I tried to drop a database, the checkbox for
> "delete backup and restore history for the database" is grayed out. I was
> not able to check this checkbox any more.
> 3. When I try to create a new database diagram, got the following error
> message:
> You do not have sufficient privilege to create a new database diagram
> 4. I tried to following the instructions suggested from knowledge base:
> 1. In SQL Enterprise Manager, move to the affected database.
> 2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
> database role of the dtproperties table.
> 3. Grant EXEC permissions for the public database role to all these stored
> procedures:
> dt_addtosourcecontrol
> dt_addtosourcecontrol_u
> dt_adduserobject
> dt_adduserobject_vcs
> dt_checkinobject
> dt_checkinobject_u
> dt_checkoutobject
> dt_checkoutobject_u
> dt_displayoaerror
> dt_displayoaerror_u
> dt_droppropertiesbyid
> dt_dropuserobjectbyid
> dt_generateansiname
> dt_getobjwithprop
> dt_getobjwithprop_u
> dt_getpropertiesbyid
> dt_getpropertiesbyid_u
> dt_getpropertiesbyid_vcs
> dt_getpropertiesbyid_vcs_u
> dt_isundersourcecontrol
> dt_isundersourcecontrol_u
> dt_removefromsourcecontrol
> dt_setpropertybyid
> dt_setpropertybyid_u
> dt_validateloginparams
> dt_validateloginparams_u
> dt_vcsenabled
> dt_verstamp006
> dt_whocheckedout
> dt_whocheckedout_u
> I check all the checkbox for the above right from role-pulic(permission),
> after I hit apply and go back to see if the changes have been applied to
> the
> public role, I found that all the checkbox that I checked could not be
> saved
> and they were unchecked.
> I have more than 20 databases on this server, the above problem occured to
> all the databases including master database.
> Could anyone provide any help? Could it be possible that all the database
> options to public role have been disabled by mistakes? What are the
> command
> to enable all the dboptions for master database? Is master database
> corrupted?
> Thank you very much for your help.
>

DBO rights

hi
As per baselining requirement, we need to drop dbo rights of the users
and assign
SELECT,INSERT,UPDATE, DELETE, EXECUTE rights on tables, SPs, views,
functions in the database.
Request you to pls revert whether dropping the dbo priviledge would affect
the functionality of database in any way.
RAHULHi Rahul,
A database always need to have an owner, and you will always have a dbo user
in a database.
Removing other users from the db_owner role should not affect the
functionality of your application however, if it is properly designed.
Jacco Schalkwijk
SQL Server MVP
"RAHUl" <anonymous@.discussions.microsoft.com> wrote in message
news:1EA6990D-BC8D-4AB4-B07E-9B617C6C9EF7@.microsoft.com...

quote:

---

> hi
> As per baselining requirement, we need to drop dbo rights of the users
> and assign
> SELECT,INSERT,UPDATE, DELETE, EXECUTE rights on tables, SPs, views,
> functions in the database.
> Request you to pls revert whether dropping the dbo priviledge would affect
> the functionality of database in any way.
> RAHUL

---