Category Archives: SQL Server

Creating SQL Server mirror fails with 1488 / 1499 error.

1 Reply

Recently, the AIS support engineers encountered an interesting (and very perplexing) issue with mirroring under SQL Server.  The customer involved experienced a complex failure involving both custom software and hardware on their mirrored SQL Servers.  Our engineers recovered the hardware and principal database within minimal issues or fanfare.

The fun really began during the rebuild of the mirror:

  1. Logons created and associated with the instances?      CHECK!
  2. Principal database online and functioning?                    CHECK!
  3. Backups created from Principal                                      CHECK!
  4. Backups restored with NORECOVERY onto Mirror         CHECK!
  5. Partner configured against Mirror instance                     FAIL!

Database mirroring error: status 1488, severity 16, state 1, string [DB Name]. (Microsoft SQL Server, Error: 1499)

A quick look at
SELECT * FROM sysmessages WHERE msglangid = 1033
in the master database (and finding error 1488) revealed:

Database mirroring cannot be enabled because the “%.*ls” database is in single user mode.

But wait:  Our primary IS in MULTI_USER (and actually servicing customers)

Oh Joy!

[Extensive Research Happens Here]

Root Issue (at least in our case)
It turns out the Principal database was kinda-sorta in single-user mode and kinda-sorta in multi-user mode.  Somewhere buried deep in the metadata stored in the system tables within the database, a bit was still set signaling single-user mode which was respected by the backup/restore engine but not the database engine itself.

We proved this by restoring the backup created from the principal to a test instance without mirroring.  The backup recovered into single-user mode every time.

Basically, the Principal database was in multi-user mode but would create backups sets which were tagged as single-user mode resulting in a mirror failure.

Corrective Action

  1. Disconnected all clients from the Principal database
  2. Forced the Principal database into single-user mode
  3. Counted to 10 (No really, we did)
  4. Forced the Principal back into multi-user mode
  5. Created backup set for the mirror instance
  6. Rebuilt the mirror with no additional issues