 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / November 2006Surely a bug in SQL Server 2005 Full Text Search (FTS) or Backup?
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 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.  SignatureHilary 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 > We are live (production system) running > - Windows 2003 Data Centre Edition R2 x64 [quoted text clipped - 59 lines] > Regards > Richard 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. SignatureHilary 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 > Hilary > [quoted text clipped - 7 lines] > 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! Rich 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. SignatureHilary 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 > It has failed 3 nights in a row now with the same error > [quoted text clipped - 13 lines] > > 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 Richard 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 > After the change to pause indexing before the backup and unpause indexing > after the backup has completed the backup didn't fail last night. [quoted text clipped - 8 lines] > Regards > Richard 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 > Steve > [quoted text clipped - 19 lines] > > 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 Richar 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). > Steve > [quoted text clipped - 15 lines] > Regards > Richar 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 Rich |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.