Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / June 2008

Tip: Looking for answers? Try searching our database.

Fulltext Indexing leaves a transaction open, preventing inserts on a     database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Vinko - 11 Jun 2008 17:41 GMT
Hello,

Please read this two threads first, as this all started on .newusers
(I'm one of them)

More relevant: (http://tinyurl.com/454uav  http://preview.tinyurl.com/454uav)
http://groups.google.com/group/microsoft.public.sqlserver.newusers/browse_thread
/thread/682e346776184261/a7eb5ab7f9649cf6?#a7eb5ab7f9649cf6


Less relevant: (http://tinyurl.com/3v9qxf  http://preview.tinyurl.com/3v9qxf)
http://groups.google.com/group/microsoft.public.sqlserver.newusers/browse_thread
/thread/413a14596c5be41b
#

So, the second to last message in the first thread tells us that I
identified the process that left a transaction open as part of
MSSEARCH.

I killed and restarted msftesql.exe (because it did not answer to stop
requests) and the table was able to receive inserts again.

The only somewhat weird thing I've done is that new databases are
created from a template database that has the full text index defined,
that is, I don't create the full text directly on the database, it
gets created as part of the copy or restore process. But that still
doesn't explain why does this only happen on high load "template"-
based databases and not on low load ones.

So, the question is how to debug this problem, namely fulltext
preventing inserts on a table by leaving a transaction open.

Thanks a lot for any hint you can give.

V.
Vinko - 12 Jun 2008 10:34 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.