• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1019
  • Last Modified:

Microsoft SQL Database - sys.database_files table

I have inherited a SQL server that has about 15 databases that won't backup.  The problems stem from old Full Text Catalogs that have been lost and the databases are now confused about which full text catalogs exists and where they are.

I managed to fix most of them by reindexing all the tables - which for some reason then allowed me to run DROP FULLTEXT CATALOG catalogname.

However, some of the databases aren't reporting that a Full Text Catalog exists, even though in sys.database_files a record FULLTEXT sysft_DATABASENAME s:\oldpath\ exists.

How can I get rid of the record for the Full Text Catalog in the sys.database_files table?  Can the record just be deleted?

Thanks

0
-DJL-
Asked:
-DJL-
  • 5
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> How can I get rid of the record for the Full Text Catalog in the sys.database_files table?

Try running

USE ur_db_name
GO
DROP FULLTEXT CATALOG ur_catalog_name

for all your databases to drop the corresponding Full Text Catalogs..

>>  Can the record just be deleted?

No.. You have to issue DROP FULLTEXT CATALOG to remove those entries in that table.
0
 
-DJL-Author Commented:
Thanks

DROP FULLTEXT CATALOG xxx doesn't work - it states "Full-Text catalog 'sysft_NAME' does not exist in database 'MYDATABASE' or user does not have permission to perform this action."

ALTER DATABASE REMOVE FILE reports "One or more files listed in the statement could not be found or could not be initialized"
0
 
-DJL-Author Commented:
Ok - i've fixed all but one.

I restored backups to a SQL 2005 server, recreated the Full Text Catalog using the same name and then deleted it.  The extra file entry in sys.database_files was then deleted.  I then migrated the DBs back to SQL 2008

The problem is that the ones i've fixed are read-only databases - so it was possible to restore from before they were migrated to SQL 2008.

The last one is editable so I can't move it to sql 2005 and do the same.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Glad to see you fix that Catalog file entry issue..
If you would share that, it would be more helpful to others who might be visiting this thread later on..

>> The problem is that the ones i've fixed are read-only databases - so it was possible to restore from before they were migrated to SQL 2008.

Kindly explain what you meant by Read-only databases..
Do you mean that database went into read-only state after you restored it..

>> The last one is editable so I can't move it to sql 2005 and do the same.

Can you kindly explain on what you meant by this..
0
 
-DJL-Author Commented:
All the databases were originally on a half dead SQL 2005 server.  When backing up the databases with Backup Exec 12.5 I was getting error messages stating that the databases were corrupt and the volume or file is no longer available.

I had begun migrating the databases to a couple of virtualised SQL 2008 servers, however the backup issue was still there after the migration.

That's when I noticed the entries in sys.database_files referring to a very old fulltext catalogs.  Although these entries existed in sys.database_files, SELECT * FROM sysfulltextcatalogs returned nothing and neither DROP FULLTEXT CATALOG or ALTER DATABASE REMOVE FILE would get rid of the entry.

The next thing I wanted to try was recreate a fulltext catalog with the same name and then try and delete it - hoping it would take the entry in sys.database_files with it.  However, SQL 2008 handles fulltext catalogs differently to SQL 2005 - it doesn't create a separate file and it to the sys.database_files table.

Fortunately all but one of the databases run in read-only mode - ALTER DATABASE SET READ_ONLY - as they contain archive information.  This meant I could restore all but one of the databases back onto SQL 2005, recreate and delete the fulltext catalog and then migrate them back to SQL 2008.  (You can't downgrade from a SQL2008 to SQL2005)

However, the last database is read/write and is updated constantly - so I therefore can't move it back to SQL 2005 to fix the problem.  I think my only option now will be to fix an old copy of the database on SQL 2005, migrate it to SQL 2008, delete all the data, and then repopulate it from the live, but corrupt database.

Unless anyone else know how to remove an entry from the sys.database_files table manually?
0
 
-DJL-Author Commented:
Does any one know which tables the view sys.database_files is pulling data from?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Unless anyone else know how to remove an entry from the sys.database_files table manually?

sys.database_files is a system view which holds that some files are present for that database..
You can't delete a record in a view or the associated system tables starting SQL Server 2005.. ( In SQL Server 2000, its possible)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly try changing your Read-only database to online by running

ALTER DATABASE ur_database_name SET MULTI_USER
0
 
-DJL-Author Commented:
rrjegan - Read-only was not the problem - i had the databases in R/W when modifying them.  The point was that I could restore them from old backups as the data never changes.

You can delete from entries from system tables in SQL 2005/8 you have to restart the instance single user mode with a few switches I can't remember off the top of my head.  The problem was the sys.database_files view was showing records that didn't exist in the underlying system tables.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, I am aware of how to modify System tables in SQL Server 2005 / 2008 but its not recommended as the way we work with SQL Server 2000 where we can directly change from Query Analyzer itself.

In SQL Server 2000, users can modify the entries present in system tables.
But in SQL Server 2005 and 2008, you need to get into Single user mode and do the changes.. And it is totally not recommended..

>> The problem was the sys.database_files view was showing records that didn't exist in the underlying system tables.

This might have occurred because of incomplete operations or corruptions in your database..
Try restoring your master database backup if you have any so that it might fix the problem..
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now