locked
Database mirroring error: status 1460, severity 16, state 1 RRS feed

  • Question

  • I stopped my mirror server for some maintenance. When I restarted it, I found that one of two mirrored databases would not reconnect mirroring. I ended up breaking mirroring between the two databases, and I'm trying to set it up again.

    When I execute this command:

    ALTER DATABASE MyDBName

    SET PARTNER = 'TCP://MyServer:5022';

    I get this error:

    Msg 1499, Level 16, State 1, Line 1
    Database mirroring error: status 1460, severity 16, state 1, string MyDBName.

    I get a similar error if I try to set up database mirroring with the UI in SSMS.

    What does this error mean? I can't find documentation for it anywhere. It implies that my command isn't correct, but I don't see where the error is.

    Thursday, April 29, 2010 4:48 PM

All replies

  • Hi,

    To troubleshoot the issue, try the following steps:
    1. Dose the principal server and mirrored server in ths same domain? If not, please check the principal fully qualified domain name (FQDN) on mirror host file exists. If not, add the entry for principal FQDN on mirror host file.
    2. Make sure the mirrored database hasn’t already been listened to another the principal database. Run the following on the mirror:
    Use master;
    GO
    ALTER DATABASE INKPOP SET PARTNER OFF;
    GO
    ALTER DATABASE INKPOP SET PARTNER = 'TCP://MyServer:5022';
    GO

    If it still fails, please provide me the error message logged into the error log on the mirrored server. For more information about viewing the SQL Server Error Log, please see:
    http://msdn.microsoft.com/en-us/library/ms187885.aspx


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


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, April 30, 2010 3:25 AM
  • The mirror, the witness, and the master are not on a domain. They're all in the same workgroup, though.

    The mirrored database shouldn't be lisening to another principal database, just prior to this problem I removed mirroring. There's no way that they'd be mirroring another database. I also don't understand how that could happen, since the mirror would have to be initialized from a backup of that database, and so on.

    Since I had to do what I could to bring the system back up, I ended up deleting the database, taking a new backup, and re-initializing mirroring. This took several hours, during which time my database was not mirrored and therefore vulnerable.

    The error log entries that I see on the master server when trying to reconnect the mirror is here. It says the error is transient, but of course it wasn't. When I tried again, I got the same error. I can find no documentation for htis error, so I had no alternative but to drop the mirror database and completely start over.

     

    Date        4/29/2010 9:52:37 AM
    Log        SQL Server (Current - 5/1/2010 3:00:00 PM)

    Source        spid527

    Message
    Error: 1499, Severity: 16, State: 1.

    Date        4/29/2010 9:52:37 AM
    Log        SQL Server (Current - 5/1/2010 3:00:00 PM)

    Source        spid527

    Message
    Database mirroring error: status 1460, severity 16, state 1, string MyDatabaseName.

    Date        4/29/2010 9:52:37 AM
    Log        SQL Server (Current - 5/1/2010 3:00:00 PM)

    Source        spid23s

    Message
    The server instance Partner rejected configure request; read its error log file for more information. The reason 1460, and state 1, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

     

     

    Sunday, May 2, 2010 2:04 AM
  • Hi,


    Based on the error messages, it’s hard to identify the root cause of your issue. Let’s troubleshoot the issue by using the following steps:
    1. Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement.
        SELECT state_desc FROM sys.database_mirroring_endpoints.
    2. If the endpoint doesn’t start, you need to rung the following statements to start it:
    ALTER ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = <port_number>)
    FOR database_mirroring (ROLE = ALL);
    GO
    3. Check that the ROLE is correct. On each server instance use the following Transact-SQL statement.
    SELECT role FROM sys.database_mirroring_endpoints;
    GO
    4. Verify that the mirror database is prepared for mirroring:
    If you create a new mirror database, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. Also, all log backups created after that backup was taken must also be applied, again WITH NORECOVERY.
    If database mirroring has been stopped, make sure all subsequent log backups taken on the principal database are applied to the mirror database WITH NORECOVERY before mirroring can be restarted.
    For more information, please see http://technet.microsoft.com/en-us/library/ms189047.aspx.
    5. Execute the ALTER DATABASE database_name SET PARTNER = 'partner_server' statements against the mirror instance.
    6. Execute the ALTER DATABASE database_name SET PARTNER = 'partner_server' statements against the principal instance.
       Note that the order in which the ALTER DATABASE database_name SET PARTNER = 'partner_server' statements are issued is very important.

    References:

    Please refer to the following link to check your database mirroring setup in a workgroup.
    http://blogs.msdn.com/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx
    Troubleshooting Database Mirroring Deployment
    http://technet.microsoft.com/en-us/library/ms189127.aspx

     

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


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, May 3, 2010 4:09 AM
  • Thanks for your note, Xiao Min.

    Indeed, the error message doesn't make any sense. It's hard to understand why the server reports a parsing error for completely valid SQL.

    As I mentioned, I repaired the issue by dropping the mirror database, re-creating it from a backup, and re-establishing mirroring. I think this means that the suggestions you made were not appropriate; for example, the endpoints were already started and we knew they were working because another database on the same pair of machines was being mirrored correctly.

    Sunday, May 9, 2010 12:00 AM
  • Mike, Did you get the response back from Xiao Min.?

    I also start encountering the same issue now.

     

     

    Monday, May 10, 2010 3:55 PM
  • I have nothing more than what you see here, Prashant.
    Monday, May 10, 2010 11:05 PM
  • You said you stopped mirroring for maintenance.  Was it long enough to cause there to be a need to reconfigure the mirror from start in order to bring it into a state in which it could sync back up?  You can only stop a mirror for so long until you need to restore to bring in the tail end of the log to restart the initial synchronization

    1460 is also related to timeouts.  If your mirror and principal are not in the correct state of logging, you will get errors that are not related and or not truly helpful (like this one).

    I don't think I actually saw if reinitializing fixed this problem.  Again, this is the same concept of why the tail-end log needs to be applied before mirroring can start. 

    Also, why are you taking down the mirror for maintenance? What type of maintenance was this? You should never be required to remove mirroring for any type of database maintenance.  If this was for server maintenance, you should plan around peak times so the logs do not get so far behind the problem comes up.  Being vulnerable like that is far too risky and defeats the purpose of the mirror landscape

     


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Monday, May 10, 2010 11:58 PM
  • I'm taking down the mirror for maintenance because that's the point of mirroring: so that I don't have to take down the whole system when applying Windows patches or doing other work. In this case, I was adding more spindles to the logical volume where the log lives, and moving some other spindles to be available for our backup jobs. The mirror was stopped long enough to copy the log file from file to another -- perhaps ten or fifteen minutes.

    If the problem was that the mirror wasn't in a consistent log state, I'd expect the error I get when attaching the mirror in other circumstances which describes the issue pretty clearly. This error is claiming that there's a syntax error in my TSQL statement at the name of my database, which certainly isn't the case, and is a bug even if you think I'm incorrectly performing maintenance on my mirrored server instances.

     

    Tuesday, May 11, 2010 2:25 AM
  • Please use the below on the secondary if using TDE & it works great.

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    Tuesday, November 22, 2011 8:10 PM