Q1.2: How to clear a log suspend

A connection that is in a log suspend state is there because the transaction that it was performing couldn't be logged. The reason it couldn't be logged is because the database transaction log is full. Typically, the connection that caused the log to fill is the one suspended. We'll get to that later.

In order to clear the problem you must dump the transaction log. This can be done as follows:

dump tran db_name to data_device
go

At this point, any completed transactions will be flushed out to disk. If you don't care about the recoverability of the database, you can issue the following command:

dump tran db_name with truncate_only

If that doesn't work, you can use the with no_log option instead of the with truncate_only.

After successfully clearing the log the suspended connection(s) will resume.

Unfortunately, as mentioned above, there is the situation where the connection that is suspended is the culprit that filled the log. Remember that dumping the log only clears out completed transaction. If the connection filled the log with one large transaction, then dumping the log isn't going to clear the suspension.

System 10

What you need to do is issue a SQL Server kill command on the connection and then unsuspend it:

select lct_admin("unsuspend", db_id("db_name"))

System 11

See Sybase Technical News Volume 6, Number 2

Retaining Pre-System 10 Behavior

By setting a database's abort xact on log full option, pre-System 10 behavior can be retained. That is, if a connection cannot log its transaction to the log file, it is aborted by the SQL Server rather than suspended.