Improve automatic failover scenarios in SQL Server 2008

Question

We have SQL Server 2008 set up in with a principal, mirror and monitor server, the mirror is synchronized and set to automatic failover. We recently saw a situation where we ran out of space on the principal server, which crippled the application using the database but didn't cause a failover, even though each query that the application ran was answered with an error:

The transaction log for database 'XXXX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases 

I'm trying to figure out why failover didn't occur. Is because the mirror/witness still consider that the server is up as it is responding to pings and queries (all be it with an error)? Or is it something we have mis-configured? Do we need to add code our application layer to detect database errors like this and initiate a failover?

In parallel we are improving our monitoring so to try and avoid disk space problems, but we would still like to improve the reliability of the automatic failover.

Answer 1

In this scenario your database was not "down", just had a log fill up due to either space or allocation issues. The failover will only occur if the primary database/instance goes offline and then the witness and mirror can decide that the mirror should be the new primary.

You could run a script periodically (or use a SQL Alert) that checks for this condition that will then initiate a failover, but this could be a little tricky.

Answer 2

You misconfigured - but not on SQL Server.

Errors: * USing expanding files without watching the OS level (available disc space) * Obvious no operations management procesdures in place the moment the disc starts getting into a defined critical level. * Then wondering why things fail.

Basically: A discc subsystem on a database is NEVER full - the error was not "full disc" it was "incompetent admin".

One can argue whether SQL Sever shuld have failed over, but given taht this is a senario that is totally a problem of operating the servers - sorry, I dont expect SQ LServer to handle that.

Btw., production load databases hould never expand files ;) Size them properly, resize them possiby, but never auto expand them.