| Groups | Blog | Home |
sql server full text search : Surely a bug in SQL Server 2005 Full Text Search (FTS) or Backup?
We have had a very similar problem in RTM. We opened a support incident with
Microsoft on this one (SRX060201600683) and were told that it would be fixed in SP1. They asked us to debug it (they gave us instructions on how to set up the debugger) but we were unable to get a debug dump. We have applied SP1 and it is still not fixed. However our catalogs do not seem to get corrupted the way they did previously. Here is our error message: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'CreateFile' on 'G:\SQLFTS\SonarData_9\SQL.HDR' at 'fulltext.cpp'(529). Note is is the same line number. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Richard Yeo" <RichardYeo@discussions.microsoft.com> wrote in message news:820EE490-7AC5-42CD-8EBA-449F69285144@microsoft.com... > We are live (production system) running > - Windows 2003 Data Centre Edition R2 x64 > - SQL Server 2005 Enterprise Edition x64 SP 1 > > The system has been operating fine for 7 days and all of a sudden we have > started getting this message every time we backup our database. The backup > fails!!! > > We get this event in the logs... > > Event Type: Error > Event Source: MSSQLSERVER > Event Category: (2) > Event ID: 3633 > Date: 13/11/2006 > Time: 00:00:16 > User: <removed>\<removed> > Computer: <removed> > Description: > The operating system returned the error '32(error not found)' while > attempting 'CreateFile' on > 'F:\MSSQL.1\MSSQL\FTDATA\fish4_Z_Search_Freetext\SQL.HDR' at > 'fulltext.cpp'(529). > > > followed by this event... > > Event Type: Error > Event Source: MSSQLSERVER > Event Category: (6) > Event ID: 3041 > Date: 13/11/2006 > Time: 00:00:16 > User: <removed>\<removed> > Computer: <removed> > Description: > BACKUP failed to complete the command BACKUP DATABASE fish4. Check the > backup application log for detailed messages. > > The full text index is set to Automatically track changes > > According to Books Online... > > ===> During a full database backup in SQL Server 2005, full-text data is > backed up together with other database data. > > During the backup, the catalog is put into a read-only mode, so that > "crawl" > activity (the process creating and maintaining a full-text index) is > suspended until the backup completes. <=== > > So why are we getting this error message? Sure this is a bug in FTS (Full > Text Search) / SQL Server Backup? > > Any assitance would be much appreciated as this is causing the backups of > our production system to fail therefore putting our business at risk. Our > business depends on FTS so we can't just turn it off. > > Feel free to email me at... > > richard_s_yeo at hotmail dot com > > Regards > Richard
We are live (production system) running
- Windows 2003 Data Centre Edition R2 x64 - SQL Server 2005 Enterprise Edition x64 SP 1 The system has been operating fine for 7 days and all of a sudden we have started getting this message every time we backup our database. The backup fails!!! We get this event in the logs... Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 3633 Date: 13/11/2006 Time: 00:00:16 User: <removed>\<removed> Computer: <removed> Description: The operating system returned the error '32(error not found)' while attempting 'CreateFile' on 'F:\MSSQL.1\MSSQL\FTDATA\fish4_Z_Search_Freetext\SQL.HDR' at 'fulltext.cpp'(529). followed by this event... Event Type: Error Event Source: MSSQLSERVER Event Category: (6) Event ID: 3041 Date: 13/11/2006 Time: 00:00:16 User: <removed>\<removed> Computer: <removed> Description: BACKUP failed to complete the command BACKUP DATABASE fish4. Check the backup application log for detailed messages. The full text index is set to Automatically track changes According to Books Online... ===> During a full database backup in SQL Server 2005, full-text data is backed up together with other database data. During the backup, the catalog is put into a read-only mode, so that "crawl" activity (the process creating and maintaining a full-text index) is suspended until the backup completes. <=== So why are we getting this error message? Sure this is a bug in FTS (Full Text Search) / SQL Server Backup? Any assitance would be much appreciated as this is causing the backups of our production system to fail therefore putting our business at risk. Our business depends on FTS so we can't just turn it off. Feel free to email me at... richard_s_yeo at hotmail dot com Regards
Hilary
Is this support case still open? SRX060201600683 Do you have any contacts within the MS FTS / SQL Server team that might be able to help? Regards Richard
It is probably archived. My contacts on the full-text team are not helpful.
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Richard Yeo" <RichardYeo@discussions.microsoft.com> wrote in message news:AB222597-68AF-4F92-8D41-8B6AD9AE4E38@microsoft.com... > Hilary > > Is this support case still open? > > SRX060201600683 > > Do you have any contacts within the MS FTS / SQL Server team that might be > able to help? > > Regards > Richard >
It has failed 3 nights in a row now with the same error
We are experimenting tonight with a work around which pauses indexing before the backup job and restarts indexing after the backup job -- Pause FTS indexing exec sp_fulltext_service @action = 'pause_indexing', @value = 1 -- Do backup -- UnPause FTS indexing exec sp_fulltext_service @action = 'pause_indexing', @value = 0 Not ideal!
We do a file group back up. We have partitioned so we know that we can
rebuild our indexes within a day we are willing to live with the downtime while our indexes are rebuilt. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Richard Yeo" <RichardYeo@discussions.microsoft.com> wrote in message news:B3698B41-F42C-46E7-8D0A-5AA00A7E6C89@microsoft.com... > It has failed 3 nights in a row now with the same error > > We are experimenting tonight with a work around which pauses indexing > before > the backup job and restarts indexing after the backup job > > -- Pause FTS indexing > exec sp_fulltext_service @action = 'pause_indexing', @value = 1 > > -- Do backup > > -- UnPause FTS indexing > exec sp_fulltext_service @action = 'pause_indexing', @value = 0 > > Not ideal! > > Rich
After the change to pause indexing before the backup and unpause indexing
after the backup has completed the backup didn't fail last night. The jury is out as to whether this has really worked around the issue. The Backup commands should automatically pause the indexing for the period of time to backup the full text catalogs not for the entire backup period. Our backup takes 30 mins of which 2 mins are probably spent backing up the full text catalogs. Regards
We are aware of a problem when log backup and data backup are running
concurrently. Can you determine if your log backups are running at the same time as your failed backup? A few suggestions: - check the event log for log backups finishing around the same time as the failed data backup - use trace flags 3004, 3605 to see if there is overlap between the backups The fix for this problem has not yet been released, but is in the pipeline for the SP2 release. Please contact SQL Support. I agree that this is almost certainly a bug. However, I can not confirm that the overlapped log/data backups problem is the same as your issue. Thanks, -Steve Schmidt SQL Server Developement [quoted text, click to view] "Richard Yeo" wrote:
> After the change to pause indexing before the backup and unpause indexing > after the backup has completed the backup didn't fail last night. > > The jury is out as to whether this has really worked around the issue. > > The Backup commands should automatically pause the indexing for the period > of time to backup the full text catalogs not for the entire backup period. > Our backup takes 30 mins of which 2 mins are probably spent backing up the > full text catalogs. > > Regards
Steve
Thanks for taking the time to reply to my newsgroup post. With fulltext indexing paused during backup the backup was successful for the second night in a row. If it makes it to the end of the week I will consider the workaround to have worked. I can confirm that our backup is kicked off at 00:00 and that our transaction log backups take place every 15 minutes starting at 00:00 so they are running at the same time. Is there a KB article number / bug ref i should quote when i contact support. If there isn't a fix available i guess there is little point me contacting support. Are you saying that this has nothing to do with FullText? Is our work around of pausing fulltext indexing the right thing to be doing? Regards Richard richard_s_yeo at hotmail dot com
I suspect your workaround is simply altering the timing, such that the log
backup is completed prior to the data backup starting. FYI: The support case number is as Hilary pointed out: SRX060201600683 Internally the bug number is 433064. The fix is already in our product, but the fix has not yet been released. For defects that are impacting your business, you can request a "QFE" which can be released prior to the next service pack. Another workaround would be to run with traceflag 3227. That traceflag disables concurrent log/data backups. Backups against a given database serialize using "U" locks. Thanks for your patience. -Steve [quoted text, click to view] "Richard Yeo" wrote:
> Steve > > Thanks for taking the time to reply to my newsgroup post. > > With fulltext indexing paused during backup the backup was successful for > the second night in a row. If it makes it to the end of the week I will > consider the workaround to have worked. > > I can confirm that our backup is kicked off at 00:00 and that our > transaction log backups take place every 15 minutes starting at 00:00 so they > are running at the same time. > > Is there a KB article number / bug ref i should quote when i contact > support. If there isn't a fix available i guess there is little point me > contacting support. > > Are you saying that this has nothing to do with FullText? > Is our work around of pausing fulltext indexing the right thing to be doing? > > Regards > Richard > > richard_s_yeo at hotmail dot com >
Steve
Thanks for replying again. I like the sound of using a traceflag as apposed to applying a QFE although this is affecting our business so we definitely need to get it fixed either way and can't wait for SP2 to be released. Are there any side effects from using the traceflag 3227, e.g. locking? Users access our system 24 hrs x 365 days so we cannot afford for them to be interrupted. The only documentation on traceflag numbers i can find is here http://msdn2.microsoft.com/en-us/library/ms188396.aspx Regards
There will be no locking impact for normal access.
Only the backups will block each other (as full backup and log backup did in sql2000). [quoted text, click to view] "Richard Yeo" wrote:
> Steve > > Thanks for replying again. > > I like the sound of using a traceflag as apposed to applying a QFE although > this is affecting our business so we definitely need to get it fixed either > way and can't wait for SP2 to be released. > > Are there any side effects from using the traceflag 3227, e.g. locking? > > Users access our system 24 hrs x 365 days so we cannot afford for them to be > interrupted. > > The only documentation on traceflag numbers i can find is here > > http://msdn2.microsoft.com/en-us/library/ms188396.aspx > > Regards
Thanks
I changed the transaction log backup so it didn't happen at the same time as the full backup until I had you confirmation. I also re-enabled fulltext indexing during backup. This seems to have cured the problem, i.e. it wasn't to do with FTS after all. Regards
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |