FullTextCatalogProperty (Index and log sizes)

Question

Post reply

FullTextCatalogProperty (Index and log sizes)

Claudio Gatto

Old Hand

Points: 341
More actions
September 14, 2005 at 5:17 am

#165975

I am investigating the index and log sizes as given by this function:
I have index sizes (in Mb) of 375, 27, 36 and corresponding log sizes of 100, 101, 201.
Can these logs be viewed/cleaned up?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 1

the logs should take care of themselves depending on what model you are using.

that is database properties--> options tab. Model.

If the model is set to full the transcation logs will only truncate themselves when a bckup of the datbase is performed. I f simple then they will truncate after each checkpoint.

Hope this helps.

Claudio Gatto Old Hand Points: 341 More actions · Answer 2

I'm using Change Tracking with Update Index in Background. BOL describes the logsize as representing:

"Size, in bytes, of the combined set of error logs associated with a Microsoft® Search Service full-text catalog"

You're saying these are not "error logs" but transaction logs. Is the description wrong??

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 3

sorry i think i misread you first entry. What procedure are you using to investigate your catalogs?

their is a sp_fulltext_service 'Cleanup' wich i use to cleanup all fulltext catalogs. their are a number of other sp to investigate your fulltext catalogs. i refer you to bol article "Performing Investigation and Clean-up Tasks for Full-Text Catalogs"

John Kane SSCrazy Points: 2810 More actions · Answer 4

Claudio,

When you say that you "have index sizes (in Mb) of 375, 27, 36 and corresponding log sizes of 100, 101, 201."

Do you mean three separate Full-Text Catalogs (FTC) for three separate FT-enabled tables? If so, could you reply with the output of the following SQL code for each FTC?

use <your_database_name>

go

exec sp_help_fulltext_catalogs

go

-- replace <FT_Catalog_Name> with a valid FTC name from sp_help_fulltext_catalogs

select FullTextCatalogProperty('PubInfoFTC', 'itemcount')

select FullTextCatalogProperty('PubInfoFTC', 'uniquekeycount')

select FullTextCatalogProperty('PubInfoFTC', 'indexsize')

In terms of cleaning up these FT Catalogs, you can use the above referenced "sp_fulltext_service 'Cleanup'", but most likely they are as "clean" as needed, unless they are old FT Catalogs than have been deactivated, but not yet dropped from SQL Server or the physical files removed. Have these FT Catalogs be deactivated?

Finally, the FT Catalogs are maintained by the "Microsoft Search" (MSSearch.exe) service outside of SQL Server and cannot be directly viewed. However, you can use the VBScript utility (gthrlog.vbs) located under ":\Program Files\Common Files\Microsoft Shared\MSSearch\Bin" or ":\Program Files\Common Files\System\MSSearch\Bin" to view the MSSearch Gatherer log files under \MSSQL\FTDATA\SQLServer\GatherLogs where you have SQL Server 2000 installed. You can use this utility via a command window. Below is the synax:

cscript //nologo gthrlog.vbs /?

Microsoft Search Version 1.0

Queries the gatherer status log

Usage: gthrlog.vbs <log file name> [/u URL-filter] [/b] [/n] [/t TransactionType]

[/e error] [/s status]

URL-filter is any URL with wild characters, including * and ?

/b - means order by descending timestamp

/n - means display any messages that don't match the filter

/t - specifies the notification (transaction) type [a,d,m]

during crawls transaction type can only be add or delete

/e - specifies the error mask, like an HRESULT

/s - specifies the transaction status, like an HRESULT

Hope that helps!

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Claudio Gatto Old Hand Points: 341 More actions · Answer 5

Thanks for the info. I will review and post when I have some new information. Going on leave today so might be a while.

Claudio Gatto Old Hand Points: 341 More actions · Answer 6

I've had a chance to digest your info and thank you again. I just wanted to summarize by way of an example and then perhaps pose another question.

select fulltextcatalogproperty ('ix_STS_adespsfe01_1517674739','indexsize') 240 Meg

select fulltextcatalogproperty ('ix_STS_adespsfe01_1517674739','logsize') 404 Bytes

My initial concern regarded the size of the log, which I incorrectly interpreted as Megabytes instead of Bytes. As you've pointed out the content of these logs can be viewed using "gthrlog.vbs".

My next question still has stiil to do with space allocated:

The index size of "ix_STS_adespsfe01_1517674739'" as given by the function above is 240Mb.

The space allocation for the crresponding index at "C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQL0000700005" is 309Mb.

Is there normaly such a large overhead in mantaining the full text catalog?

I have 14 Full text catalogs with a total of 709Mb as given by the function "fulltextcatalogproperty", but all up under "C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA" there is a whopping 1129Mb.

Thanks for any feedback.