locked
Manual failover error with read-only filegroup in SQL Server 2005 RRS feed

  • Question

  • I have a big database (>1TB) with two servers doing mirroring for high availability. DBServer1 is the Principal and DBServer2 is the Mirror . They both have SQL Server 2005 Standard 64-bit Version with Service Pack 3 (9.0.4226) installed on Windows Server 2003 R2 x64 with Service Pack 2 . All the hardware and software configurations are exactly the same. They are using High safety without automatic failover (synchronous) mirroring operating mode. There is a read-only filegroup bigfile in the database. When I tried a manual failover from DBServer1 to DBServer2, DBServer2 resumed the Principal successfully, but DBServer1 as Mirror showed “Synchronizing” not “Synchronized”. I launched the Database Mirroring Monitor, and it showed trouble with mirroring. I checked SQL Server Log on DBServer1 , it was filled up with these error messages:

    Error: 823, Severity: 24, State: 3.

    The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0000000000000000 in file 'D:\MSSQL\DATA\bigfile.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    I realized this might be something related with the read-only filegroup. So I did some testing on my test server with a small database which also has read-only filegroup. It had the same errors when doing a manual failover. I did some research on Microsoft KB and found out this KB973897 for SQL Server 2008 which is exactly the same problem. It says “The fix for this issue was first released in Cumulative Update 4 for SQL Server 2008 Service Pack 1 ”. But it doesn’t mention this problem applies to SQL Server 2005. And I didn’t find anything about the fix for SQL Server 2005.

    Right now my solution is: before doing the failover, make all read-only filegroups to readwrite, perform manual failover, then turn them back to read-only after the failover. Kind of pain, is there any fix for SQL Server 2005? Thank you very much!
    Friday, February 12, 2010 6:01 PM

Answers

  • Hi,

    If you have installed the last service pack for SQL Server 2005 and still failed in performing the manual failover with the read-only file groups, I’d like to suggest you submit a feedback to our product team and file the issue as a bug on the site: https://connect.microsoft.com/. The product team will look into the issue and give you a feedback as soon as possible.

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, February 17, 2010 9:41 AM

All replies

  • Hi,

    If you have installed the last service pack for SQL Server 2005 and still failed in performing the manual failover with the read-only file groups, I’d like to suggest you submit a feedback to our product team and file the issue as a bug on the site: https://connect.microsoft.com/. The product team will look into the issue and give you a feedback as soon as possible.

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, February 17, 2010 9:41 AM
  • I submitted it as a bug. Thank you!
    Wednesday, February 17, 2010 7:01 PM