T-SQL Tuesday #040: File and Filegroup Wisdom

Comments: 3 Comments
Published on: March 11, 2013

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"]

[/codesyntax]

file_id type_desc name state_desc
1 ROWS Somefile ONLINE
2 LOG Somefile_log ONLINE
3 ROWS Somefile_data ONLINE
4 ROWS Somefile_index ONLINE
65537 FULLTEXT sysft_FTS OFFLINE

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"]

[/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"]

[/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"]

[/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.

file_id type_desc name state_desc
1 ROWS Somefile ONLINE
2 LOG Somefile_log ONLINE
3 ROWS Somefile_data ONLINE
4 ROWS Somefile_index ONLINE
65537 FULLTEXT sysft_FTS 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.

3 Comments - Leave a comment
  1. Looks like your client for fortunate that they didn’t actually have any fulltext data in the database. If they’re not storing any fulltext, why not just disable it on that database?

    • Jason Brimhall says:

      They were using fulltext. I confirmed with them. The indexes were rebuilt just fine and the website resumed after I recreated the fulltext. Oh yeah, that was the other piece – the server outage caused their website to be nonfunctional.

  2. [...] Jason Brimhall: T-SQL Tuesday #040: File and Filegroup Wisdom [...]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
March 2013
M T W T F S S
« Feb   Apr »
 123
45678910
11121314151617
18192021222324
25262728293031
Content
SQLHelp

SQLHelp


Welcome , today is Monday, October 20, 2014