Further information:
Using the deadlock finder described here: http://blogs.techrepublic.com.com/datacenter/?p=275
I found that the inserts on the table are blocked by this Full text
query
create table #tmpcatinfo ([ftcatid] smallint,[name] sysname,[path]
nvarchar(260),[status] int,[number_fulltext_tables] int) if( 1 =
DATABASEPROPERTYEX(db_name(), 'IsFulltextEnabled') ) begin insert
#tmpcatinfo execute dbo.sp_help_fulltext_catalogs end SELECT
cat.name AS [Name], cat.fulltext_catalog_id AS [ID],
CAST(FULLTEXTCATALOGPROPERTY(cat.name,'AccentSensitivity') AS bit) AS
[IsAccentSensitive], CAST(cat.is_default AS bit) AS [IsDefault],
fg.name AS [FileGroup], dp.name AS [Owner],
FULLTEXTCATALOGPROPERTY(cat.name,'LogSize') AS [ErrorLogSize],
FULLTEXTCATALOGPROPERTY(cat.name,'IndexSize') AS [FullTextIndexSize],
FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
FULLTEXTCATALOGPROPERTY(cat.name,'UniqueKeyCount') AS
[UniqueKeyCount], FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus')
AS [PopulationStatus], 0 AS [PopulationCompletionAge], cast(null as
datetime) AS [PopulationCompletionDate], ci.path AS [RootPath],
CAST(ci.number_fulltext_tables AS bit) AS [HasFullTextIndexedTables],
FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS
[PopulationCompletionDateInt],
FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS
[PopulationCompletionAgeInt], GETDATE() AS
[PopulationCompletionAgeNow] FROM sys.fulltext_catalogs AS cat LEFT
OUTER JOIN sys.filegroups AS fg ON cat.data_space_id =
fg.data_space_id LEFT OUTER JOIN sys.database_principals AS dp ON
cat.principal_id=dp.principal_id LEFT OUTER JOIN #tmpcatinfo AS ci ON
ci.ftcatid = cat.fulltext_catalog_id WHERE (cat.name=N'myFullText')
drop table #tmpcatinfo
Every time the block occurs sooner.
Hilary Cotter - 12 Jun 2008 18:26 GMT
This is the code behind one of the SSMS dialogs.
Perhaps if you are looking for something specific you should close down this
SSMS dialog and issue the queries individually, ie
select FULLTEXTCATALOGPROPERTY(cat_name,'LogSize') AS [ErrorLogSize],
FULLTEXTCATALOGPROPERTY(cat_name,'IndexSize') AS [FullTextIndexSize],
FULLTEXTCATALOGPROPERTY(cat_name,'ItemCount') AS [ItemCount],
FULLTEXTCATALOGPROPERTY(cat_name,'UniqueKeyCount') AS
[UniqueKeyCount], FULLTEXTCATALOGPROPERTY(cat_name,'PopulateStatus')
Where Cat_Name is the name of your catalog.
> Further information:
>
[quoted text clipped - 32 lines]
>
> Every time the block occurs sooner.
Vinko - 12 Jun 2008 18:48 GMT
> This is the code behind one of the SSMS dialogs.
>
[quoted text clipped - 8 lines]
>
> Where Cat_Name is the name of your catalog.
But when the deadlock occurs I have no SSMS open, well, it has
sometimes been open but without any query being executed...
So I don't really know what is sending that query which leaves the
inserts stuck.
For now I've deleted the catalog and rebuilt it and so far it hasn't
get stuck again.
But that doesn't find the root cause which would be very desirable :-)
Thanks for the input,
V.