It sounds like the session you tried to kill was possibly a runaway and all the work it was doing now has to be rolled back as part of crash recovery. A large log file like that will always take time to finish recovery, especially if it is trying to rollback a large transaction.
This query may give you an estimated time for recovery, but it's not always accurate so use as a guide only.
SELECT command AS [Command],
DB_NAME(database_id) AS [DB],
CAST(percent_complete AS DECIMAL(5,2)) AS [PercentComplete],
DATEADD(SECOND, estimated_completion_time, GETDATE()) AS [ETA],
WHERE command = 'DB STARTUP'
Once the database is recovered, your temptation may be to shrink that log to remove all the VLFs, however, remember that too few VLFs can cause problems as well. Take a look at this article by Kimberly Tripp where she outlines the algorithm SQL uses to determine the number of VLFs to create during a growth, and some guidelines on sizing your log files correctly.
What needs to be done for :
- to Kill that particular Session which is creating the block ( Header Block set to 1 in activity monitor)?
- how do i resize the log file and see to it that it has good enough space?
- is it possible to delete the inactive VLF?
- how do i make the database to become online faster ( after restart of 2008 R2 service)?
- You probably can't. If it is trying to rollback work it has previously undertaken, you simply have to wait for it to finish. You can try KILL spid WITH STATUSONLY to get the status of the roll back, but the majority of time this doesn't return much useful information.
- You need to back it up (to mark log portions inactive), shrink it and then regrow it to your optimal size (see Kimberly's article).
- Yes, shrinking will do that, but remember to adjust your growth settings and manually grow the file to a suitable size to control VLF numbers in future. Do not use percentage growth factors and do not use too large an MB growth factor. Remember that log files cannot be instantly initialised like data files can be, so every growth has to be zeroed before it can be used.
- You really can't. Recovery takes as long as it takes, and you have to wait it out. You don't want inconsistent data in your database, and part of consistency is the rolling back of failed transactions such as this. You can monitor the DB STARTUP session and check for waits or blockers and see what you can do to remove those obstacles, but you won't likely be able to do much.
You can protect against slow recovery in the future by fixing your log growth settings to prevent excess VLFs and implementing monitoring for runaway sessions. Capping your log files can prevent them from growing excessively, but there is always a risk that legitimate transactions get rolled back due to insufficient log space in that scenario.
Take a look at this article regarding the database recovery process to get a full understanding of what is happening.