T-SQL Tuesday #040: File and Filegroup Wisdom

Backstory

Each month the SQL community comes together for an important party.  This is the blog party that was the brain child of Adam Machanic (Twitter) known as T-SQL Tuesday.

The party is a very good collaboration among data professionals on a pre-determined topic.  This month, for TSQL Tuesday #40, the topic is on Files and Filegroups.  The host du mois is Jen McCown (Twitter).

This month, I had the luck of encountering something this past week that is right up the alley of this topic.  I love it when sysadmins help create learning opportunities for me (e.g. blog material).

Production Down

I was recently given the following concerning a client server issue:

The log file for database ‘xxx’ is full. Back up the transaction log for the database to free up some log space.

That was followed by a short description stating that the sysadmin had tried to expand the log file and that they also tried to run a full backup.  The output of the full backup was as follows.

BACKUP DATABASE [xxx] To Disk=’blah’ WITH NOFORMAT, NOINIT, NAME = N’blah’, SKIP, REWIND, NOUNLOAD, STATS = 10

” failed with the following error: “The backup of the file or filegroup “sysft_FTS” is not permitted because it is not online.

BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Now this makes things more interesting.  The sysadmin at least tried to do a full backup and then handed off when it got too deep.

The client server is a SQL 2005 box.  Fulltext was enabled for the database on that box. And we have seen plenty of issues related to Fulltext in SQL 2005.  Somehow, I feel that none of them really pertained to this opportunity.  From all appearances, there was either a disk issue (no history in the logs but client said there was) or somebody deleted the directory (there was a login at the time the issue started and there was a service restart at that time).  In either case, the folder for the fulltext filegroup was no longer present.  But I am getting a little ahead of myself.

When querying the sys.database_files catalog view, I was able to confirm the directory path that should have been in place for the filegroup and that the filegroup was indeed OFFLINE.  Results and query to follow, with filepaths redacted intentionally.

[codesyntax lang=”tsql”]

select file_id,type_desc,name
,physical_name --intentionally omitted in result set
,state_desc
From sys.database_files;

[/codesyntax]

So, indeed I do have a problem with the filegroup and I need to get it back online in order to resume backups and get this database back online and able to perform backups.

Some suggestions out there would be to rebuild the fulltext catalog in order to bring it back online.  Well, the files are no longer present on the filesystem, so this didn’t work too well.

[codesyntax lang=”tsql”]

ALTER FULLTEXT CATALOG [FTS] REBUILD
/*
Full-text catalog 'FTS' is in an unusable state. Drop and re-create this full-text catalog.
*/

[/codesyntax]

The notes in the code block represent the outcome.  And the output makes sense if you ask me.  But when trying to drop and recreate, I ran into some more fun.

[codesyntax lang=”tsql”]

DROP FULLTEXT CATALOG [FTS]
--or
sp_fulltext_database @action= 'disable'
/*
Cannot drop full-text catalog 'FTS' because it contains a full-text index.
*/

[/codesyntax]

Once again, that makes sense.  I had hoped that it would drop everything for me.  So, time to try dropping the indexes and recreating them.  For this, I took screenshots of each index in question.  Then tried to drop them.  Once again – another error.

property fulltextindexsize is not available

Despite that error, the indexes were gone and the catalog dropped.  Since I had disabled FT on the database, I needed to re-enable it in order to recreate the catalog and indexes (I had scripts for the catalog and screenshots for the indexes).

[codesyntax lang=”tsql”]

sp_fulltext_database @action= 'enable'

[/codesyntax]

Now issuing a rebuild against that catalog works as expected.  Additionally, backups work as expected.  And to confirm that all is well, query sys.database_files once again to see that the filegroup is online.

Between the Lines

I breezed through what got this filegroup back online so database activity could resume.  One thing that I skipped over was a step I took trying to recover without dropping and recreating.  Since the directory was not present, and there was a full backup from the same day that had the filegroup in a working state, I tried to recover the filegroup manually.  Restore the database, copy the folder structure into the appropriate filepath and run an alter database statement.  Since it didn’t work, I am not going into deep details on it.  The short of it is that since the structure disappeared off disk, there was some corruption related to it internally in the database.  That needed fixed and in this case it meant to drop the indexes and catalog in order to recreate it.