Post reply

Table sysfiles out of date

  • November 17, 2015 at 5:42 am

    #399339

    We are using 2008R2. Due to a problem in a Full Text Catalog I dropped and recreated it - problem fixed.

    However, when I interrogate sysfiles to view the space allocation for the DB, it still lists the old FTC and there is no mention of the new one.

    Do I need to do something to update sysfiles? If so what?

    Or am I just being impatient (will it sort itself out overnight)?

    Any advice appreciated,

    Nick

  • November 17, 2015 at 5:44 am

    #1840290

    sys.sysfiles is only included in the product for backwards compatibility.

    You should be using sys.database_files.

    Does the new system view still show the old FTC?

  • November 17, 2015 at 5:53 am

    #1840294

    Hi,

    Thanks for the prompt reply.

    sys.database_files also lists the old FTC and size info and no mention of the new one.

    If I use SSMS and look at storage under DB properties is shows the new FTC correctly.

    The FTC is up and running, fully populated and in use without error.

    Thanks,

    Nick

  • November 17, 2015 at 6:02 am

    #1840296

    Can you provide your scripts used?

  • November 17, 2015 at 6:10 am

    #1840298

    The queries use are below but all they do is list the entries for the current DB. The first gives all data, the second formats the data to go into my reporting system

    Use dbname

    go

    Select * from sys.databasefile

    or

    select getdate() as [Date],DB_NAME() as [Database],name as [File],convert(decimal(12,2),round(size/128.000,2)) as FileSizeMB

    ,convert(decimal(12,2),round(fileproperty(name,'SpaceUsed')/128.000,2)) as SpaceUsedMB

    ,convert(decimal(12,2),round((size-fileproperty(name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB

    from sys.database_files

    Both show the old FTC in 'name' and don't list the new one.

    Nick

  • November 17, 2015 at 6:14 am

    #1840299

    Sorry I meant your script for the FTC drop and recreate.

    Did you add in a new filegroup&file when you recreated the FTC or did you use the existing filegroup&files already in place?

    FTC's don't have their own specific filegroup, they generally default to the <default> filegroup for the database, that being said it can be over-ridden in the creation of the FTC if you have created a filegroup and file in the database settings.

    So if you right click and script out the FTC, you will re-create it on the filegroup thats already been defined, you would need to remove the old file and filegroup from the DB, recreate them, then recreate the FTC

  • November 17, 2015 at 6:24 am

    #1840303

    Hi,

    Did it through SSMS...

    Created new FTC with totally new name.

    Removed the tables from the old FTC

    Set the tables/columns up in new FTC

    Tested 'contains' queries were working again.

    Waited for the population to complete

    Dropped old FTC

    Reran the database_files query to see the result...and the rest is history...

    Thanks,

    Nick

  • November 17, 2015 at 7:01 am

    #1840313

    Might be a case of waiting for the ghost cleanup task to kick in, but I wouldn't of thought it would take to long to start the cleanup but depends on how transactional your system is.

    However that being said are you sure the FTC was on the filegroup you are monitoring and it wasn't placed on the default filegroup for the database, and as such when you recreated the FTC the space hasn't changed as the old and new FTC's where both on the same filegroup and thus no space change has occurred.

  • November 17, 2015 at 8:42 am

    #1840373

    Hi,

    Typical SSMS!

    There are two ways to create FT indexes.

    1) Through Storage\Full Text Catalogs\'Catname'\Properties under the Tables/Views.

    2) By right clicking on the table, then Full-Text index\Define Full-Text Index...

    The first method does not allow you to select where the catalog is created, but the second does!

    It appears my predecessor created a separate file/file group, and then created the FT Index on the Primary anyway, doh! So when I deleted it did not change the space used in the FT file group shown in database_files. Then, when I recreated the Index, I used method (1) above and it used the default file group (Primary) again.

    I have removed the unused FT file group created by my predecessor and created a new one. Then set up the FT Index using method (2), and it all looks a lot more sensible.

    Thanks for your help.

    Nick

  • November 17, 2015 at 8:49 am

    #1840376

    The catalog is filegroup agnostic, its the FTI which is stored on the filegroup, you could have 1 FTC with 20 FTI's and 10 of the FTI's in one filegroup and 10 in another

Viewing 10 posts - 1 through 10 (of 10 total)

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