1

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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
1
  • On top, that is a nerror that should rin alarm bells on the OM system in use (Operations Management). WIth email, SMS etc. - not WHEN it occurs, but WAY earlier (20% mark?).
    – TomTom
    May 29, 2012 at 15:36
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .