More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
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.
v
System 10
What you need to do is issue an ASE kill command on the connection and then un-suspend it:
select lct_admin("unsuspend", db_id("db_name"))
(Continued on next question...)
