Server hangs and never comes back during full backup (10.0.1)

[Former Member]

I have been able to repeat this behavior in version 10.0.1.3887 in a 32 bit environment and the most recent EBF (I was testing the upgrade on a 64 bit server). If I'm dumb enough to do anything during the full backup (which is set up as a triggered event), it usually hangs and never comes back. Usually what I'm doing is attempting to connect at approximately the same time as the backup kicks off, but occasionally I'm doing something else. Today the thing I did ran several minutes after the backup started, but was the first thing I did that touched replication. (I'm doing a disaster recover routine, so I was creating remote users for extraction).

When I say hangs and never comes back, I'm talking about a database and backup file that usually finishes in less than six minutes being unresponsive (you can't connect to it, whatever you were doing is hung) for over an hour before I give up on it. We're running "transaction log rename match" and it looks like the whole 41+G database file has finished copying, but the log file never copies to the mirror directory.

Is this a known problem? Is there some way to set the server so it will disconnect anyone connected when the backup is about to run and then won't let anyone connect until it's finished?

[VolkerBarth]

Do you use "BACKUP DATABASE ... WAIT AFTER END" and may it be that any transactions are still active (or new transactions are started during the backup)? - To disable new connections (but not drop existing ones) before the backup, you can use "CALL SA_SERVER_OPTION( 'ConnsDisabled', 'Yes' );". As a sample, the latter is done by the default event for validation that is created for maintenance plans.

[Former Member]

I'm gonna go test all those suggestions. That sounds like just the thing. I'll let you know what I find!

[MarkCulp]

@Carol: Any results from your tests yet?

[Former Member · Accepted solution]

As a result of the testing, we have added "wait before start" and "wait after end" to our daily backup. It has only been live for one backup so far, but in my testing, this gave us the behavior we really wanted and didn't go out to lunch without coming back. We'll play a bit with the sa_server_option later, as this appears to be exactly what we need for now, but I bet that would be very useful to solve some other little problems that creep up on us from time-to-time.

Edited 11/30/2011 to add: This has been working reliably now for months. We're very happy with it, even when we think the server is hung!

 backup database directory 'C:\\\\Database Server\\\\backups' transaction log rename match wait before start wait after end