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.

March 2013 LV UG Meeting

Categories: News, Professional, SSC, SSSOLV
Comments: No Comments
Published on: March 11, 2013

Another month and we have another installment (meeting) for the Data Professionals of the Las Vegas area.

This month we will be getting back on schedule with the usual meeting time and place.  We had a slight divergence last month due to that heartsy holiday.

We are excited about some of the upcoming speakers and feedback we have been getting of late.  We are feverishly working to get some presenters on Big Data and Hadoop.  If you are in the market to present on these topics, ping me.

Here are some of the details for the meeting.


Presenting is Jason Kassay

Jason Will be presenting to us on the topic of “The Accidental Programmer.

Most people in the database world have heard of the term, “The Accidental DBA”, those programmers who have to work with databases. How about the other side of the coin? What about DBA’s an Dev’s who have to write code or have to work closely with programmers? This presentation is a best practices guide for working with SQL Server in a .Net environment. You will learn how to recognize when bad code is written that interacts with the database, how to track it down, and most importantly how to fix it. On top of that you will also receive an introduction to object oriented programming concepts such as data abstraction, encapsulation, tier architecture, and class objects so that you can better communicate with your programmers.

BIO

Jason has been a software developer for over 10 years and currently works at EZFacility, providing scheduling, management, full-service billing, and membership solutions for the sports, health, and fitness industries. He has worked primarily with .Net (C# and VB) and SQL Server as well as HTML, CSS, and Javascript. When he is not coding or spending time with his awesome family, you will find him either running (to train for the zombie apocalypse) or stopping hockey pucks as a goaltender.

 

LiveMeeting Information:

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=DC769N&role=attend
Meeting ID: DC769N

 

VERY IMPORTANT

The meeting location has changed.  We will no longer be meeting at The Learning Center.  New meeting location is M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128.
We are also moving to meetup for meeting management. Please join us for free at http://www.meetup.com/SQL-Server-Society-of-Las-Vegas/

page 1 of 1






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

SQLHelp


Welcome , today is Saturday, October 25, 2014