29 August, 2012

Full Text Search in SQL: Error Full-text catalog ('Catalog Name') in database ('DBNAME') is low on disk space. Pausing all populations in progress until more space becomes available. Reason code: 1. Error: 0x80041828(The disk has reached its configured space limit.). To resume populations, free up disk space.

We all know our server systems are highly alerted with all different kind of alerts. Today we got an LMS alert (configured with the low disk space counter).  Whistle looking at the disk space it was the SQL Log file which used almost 98 % if the disk space. 
On further investigating we got to know it was SQL FULL Text search log file 'SQLFTXXXXXX.LOG'.

Now the next question click to the mind is what Full Text Search is:
Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM.

We were unable to check what is logged in the file due to heavy size. While working in troubleshooting with further on this we decided to check which data base is configured to use the Full Text Search.

How to check which DB is using the Full Text Search:

Log on to SQL Management console -> Expand Databases -> Go into the Database properties of the  database -> Select File -> and check if ‘Use full-text indexing  box is checked which is related to full text indexing.
We decided to turn off- the full text indexing, however needed it to validate if this is the same setting used in other SharePoint Environment.   After validation, we decided that we need to have this enabled to have the events logged. 

If you decide to turn off the full text indexing you can enable and disable the Full Text Search on the a database. 

We decided to delete the file by creating a new reference with the same name and have the logs generated from the starting.
One issue was resolved to clear up the space on the SQL Server drive. It leads to another issue of indexing not happening.

We noticed that File Size (SQLFTXXXXXX.LOG) again started to grow with the Error message.
Full-text catalog ('Catalog Name') in database ('DBNAME') is low on disk space. Pausing all populations in progress until more space becomes available. Reason code: 1. Error: 0x80041828(The disk has reached its configured space limit.). To resume populations, free up disk space.

Tried to run the different catalogs views to check the missing file reference from the Full-text index file.

On checking the event viewer logs, the below event was recorded in the logs after every 10 min. This seems to be a serious problem.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9901
Date:  8/29/2012
Time:  3:28:53 PM
User:  N/A
Computer: WHGVSQL1
Description:
Full-text catalog ('Catalog Name') in database ('DBNAME') is low on disk space. Pausing all populations in progress until more space becomes available. Reason code: 1. Error: 0x80041828(The disk has reached its configured space limit.). To resume populations, free up disk space.

Checking further on the error message, we found that this error message is related to Log file not available.

Whereas on finding reference to the error message. Error: 0x80041828(The disk has reached its configured space limit.) This was pointing to the low space on the disk.


All disks were having enough disk and we did check the DB automatic growth from DB properties. It was set to unrestricted. 

The first reference article was pointing to the situation similar of us as log file is not available. So concentrated the search with one point in mind. As you all know Microsoft is only own of their kind, we found explanation on the occurrence of the error message, which explains:

An I/O error related to data integrity has occurred for the specified database. Either the log or data portion of the database could be damaged. SQL Server has made the log for that database unavailable to prevent further data integrity problems. The I/O error that led to the 9001 message should be reported in the SQL Server error log and/or the Windows event logs.
This error could occur because of a failed ROLLBACK operation. In rare circumstances, the ROLLBACK could fail because of the server is completely out of memory, out of buffer pages, or out of log space.

 further details.

Hope this piece of information help you all in troubleshooting issue with Full- Text Search. 

No comments:

Post a Comment

Your feedback is always appreciated. I will try to reply to your queries as soon as possible- Amol Ghuge

Note: Only a member of this blog may post a comment.