Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Dead links

Hi.
In report manager, links to Home, My subscription, Site settings, Help,
Contents, Properties works fine.
But links to New Folder, New Data source and Upload files are completly
dead - what could be wrong?
I'v installed evalution edition incl. SP1.
Best
LABProblem solved, it was a matter of browser-sequrity.
/LAB
"LAB" <nomail@.nomail.dk> skrev i en meddelelse
news:#NLIMzXnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Hi.
> In report manager, links to Home, My subscription, Site settings, Help,
> Contents, Properties works fine.
> But links to New Folder, New Data source and Upload files are completly
> dead - what could be wrong?
> I'v installed evalution edition incl. SP1.
> Best
> LAB
>

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or extent
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:
> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or extent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
> > We have a report that uses dbcc showcontig to identify indexes with
> > fragmentation. It shows indexes that have a scan density under 85% or extent
> > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > in the report to rebuild the indexes. When I run the report again a
> > completely different index shows up. During this time no other users or
> > processes are running against the database. Any ideas what may be causing
> > this?
> > --
> > Odell|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > You may want to build all indexes for the given table rather than specific
> > index, especially if the index is a clustered.
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > We have a report that uses dbcc showcontig to identify indexes with
> > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > in the report to rebuild the indexes. When I run the report again a
> > > completely different index shows up. During this time no other users or
> > > processes are running against the database. Any ideas what may be causing
> > > this?
> > > --
> > > Odell|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
> > Thanks for the post. We tried rebuilding all the indexes. It took several
> > hours but it didn't clean up the fragmentation.
> > --
> > Odell
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You may want to build all indexes for the given table rather than specific
> > > index, especially if the index is a clustered.
> > >
> > > John
> > >
> > > "Odell Edwards" wrote:
> > >
> > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > in the report to rebuild the indexes. When I run the report again a
> > > > completely different index shows up. During this time no other users or
> > > > processes are running against the database. Any ideas what may be causing
> > > > this?
> > > > --
> > > > Odell|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > Did you specify the indexes individually or just the table?
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > Thanks for the post. We tried rebuilding all the indexes. It took several
> > > hours but it didn't clean up the fragmentation.
> > > --
> > > Odell
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You may want to build all indexes for the given table rather than specific
> > > > index, especially if the index is a clustered.
> > > >
> > > > John
> > > >
> > > > "Odell Edwards" wrote:
> > > >
> > > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > > in the report to rebuild the indexes. When I run the report again a
> > > > > completely different index shows up. During this time no other users or
> > > > > processes are running against the database. Any ideas what may be causing
> > > > > this?
> > > > > --
> > > > > Odell|||Scan density is meaningless if you have several database files (search the archives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Look at Logical
fragmentation...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes with
>> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
>> > > > > in the report to rebuild the indexes. When I run the report again a
>> > > > > completely different index shows up. During this time no other users or
>> > > > > processes are running against the database. Any ideas what may be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
--
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took
>> > > several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than
>> > > > specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes
>> > > > > with
>> > > > > fragmentation. It shows indexes that have a scan density under
>> > > > > 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes
>> > > > > identified
>> > > > > in the report to rebuild the indexes. When I run the report
>> > > > > again a
>> > > > > completely different index shows up. During this time no other
>> > > > > users or
>> > > > > processes are running against the database. Any ideas what may
>> > > > > be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or exten
t
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:

> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or ext
ent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identifi
ed
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
>|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
[vbcol=seagreen]
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
>|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
>|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
[vbcol=seagreen]
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
>|||Scan density is meaningless if you have several database files (search the a
rchives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Loo
k at Logical
fragmentation...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...[vbcol=seagreen]
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...[vbcol=seagreen]
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>

dbo.f_myfunction only works for me and not other users using RS

I wrote a udf in sql server for my dataset for a report in Reporting
Services. I publish the report to the Report Server, and I can invoke the
report, but other users get an error message that they don't have the
permissions to run this udf. How do I establish permissions so that everyone
who is authorized to use RS reports on/from our intranet can run this udf?
Where do I set the permissions? Do I do this at the Sql Server end?
Thanks,
RichThis is set at SQL Server. If SQL 2005, right mouse click on the function,
properties, permission. You could give public permission to it. I have a
role specifically for RS users that I use for assigning rights to my stored
procedures and to my functions.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4FAA8268-64F0-49E8-AF13-EDCBE2552604@.microsoft.com...
>I wrote a udf in sql server for my dataset for a report in Reporting
> Services. I publish the report to the Report Server, and I can invoke the
> report, but other users get an error message that they don't have the
> permissions to run this udf. How do I establish permissions so that
> everyone
> who is authorized to use RS reports on/from our intranet can run this udf?
> Where do I set the permissions? Do I do this at the Sql Server end?
> Thanks,
> Rich|||Thank you for your reply. Of course, I am using Sql Server 2000. So, may I
ask, do I want to set a role for this udf or do I want to go to the server
security and assign permissions there?
Thanks,
Rich
"Bruce L-C [MVP]" wrote:
> This is set at SQL Server. If SQL 2005, right mouse click on the function,
> properties, permission. You could give public permission to it. I have a
> role specifically for RS users that I use for assigning rights to my stored
> procedures and to my functions.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4FAA8268-64F0-49E8-AF13-EDCBE2552604@.microsoft.com...
> >I wrote a udf in sql server for my dataset for a report in Reporting
> > Services. I publish the report to the Report Server, and I can invoke the
> > report, but other users get an error message that they don't have the
> > permissions to run this udf. How do I establish permissions so that
> > everyone
> > who is authorized to use RS reports on/from our intranet can run this udf?
> > Where do I set the permissions? Do I do this at the Sql Server end?
> >
> > Thanks,
> > Rich
>
>|||I think I got it. I right-clicked on the udf - all tasks - set permissions -
execute set to public. Hope that does it.
"Rich" wrote:
> Thank you for your reply. Of course, I am using Sql Server 2000. So, may I
> ask, do I want to set a role for this udf or do I want to go to the server
> security and assign permissions there?
> Thanks,
> Rich
> "Bruce L-C [MVP]" wrote:
> > This is set at SQL Server. If SQL 2005, right mouse click on the function,
> > properties, permission. You could give public permission to it. I have a
> > role specifically for RS users that I use for assigning rights to my stored
> > procedures and to my functions.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> > news:4FAA8268-64F0-49E8-AF13-EDCBE2552604@.microsoft.com...
> > >I wrote a udf in sql server for my dataset for a report in Reporting
> > > Services. I publish the report to the Report Server, and I can invoke the
> > > report, but other users get an error message that they don't have the
> > > permissions to run this udf. How do I establish permissions so that
> > > everyone
> > > who is authorized to use RS reports on/from our intranet can run this udf?
> > > Where do I set the permissions? Do I do this at the Sql Server end?
> > >
> > > Thanks,
> > > Rich
> >
> >
> >