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

1

2

3

4

select file_id,type_desc,name

,physical_name--intentionally omitted inresult set

,state_desc

From sys.database_files;

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

1

2

3

4

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.

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

1

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.

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.

Some of the beauty of SQL Server is the complexity that it holds. Some of that complexity is right before our eyes. And some of that requires a little bit of diving into the internals to figure out what is happening or even why it is complex.

Announcing…the book At long last the wait is over. After much blood, sweat and more blood and sweat, the next edition of the SQL Server Recipes book is finished. This edition brings several changes and quite a bit of re-write. It has been updated for many of the new SQL Server 2014 (well, new, at[…]

What is that default setting? SQL server does a fantastic job of having numerous settings at the server level and at the database level predefined for you. Additionally, the OS has a bunch of settings that are predefined. These are notoriously the default settings. Or as some would say: “Set it and Forget it Settings.”[…]

Quick run the other way! PASS Summit will never be the same! Why? Well, because for the first time in the history of me, I have been selected to speak at such a prestigious event. Isn’t that just crazy? In some ways it seems crazy. When I received the email I was exhilarated and completely astonished.[…]

One of my pet-peeves (and consequently frequent topic of discussion) is finding database settings (or any setting that has changed) without knowing about it. Worse yet is finding that the change has occurred and nobody claims to have any knowledge of it or having done the deed. This happened again recently where a database was[…]

Dan “L” Greenland (1935 ~ 2015) Dan “L” Greenland, 80, passed away June 30, 2015. Born June 3, 1935 in Highland, Utah, to William John and Cressie Althera Loveridge Greenland. He grew up in Highland and attended schools in American Fork. Graduated from American Fork High School in 1953. Married his high school sweetheart, Eleanor Bunker, October 21,[…]

As we begin to get a grasp of all that is available with SQL Server 2016 and all of the new features, it is a great idea to see what else has been made available to us in order to help support and troubleshoot these new features. With that, it is well worth a look[…]