1

Sql Server 2008 R2 Database is in recovery mode still after 16 hours for one of the database which is is Simple Recovery model.

The data file is 200 GB and Log file 90 GB.

We have to do Crash Recovery(Restarting the sql server engine) and the particular Database of size as mentioned above gets in recovery mode for past 16 hours.

There are other 5 database of equal or more size, but these other 5 database recovers faster. the reason why we do stopping the service is due to one of the Session is not getting killed in the aforesaid database, even we tried to kill it explicitly.

This session is blocking and causes long operation for backups for the above mentioned database. we checked the VLF's it has as many as 719 records(vlf file) with lot more marked as inactive and everytime the log usage is above 99% near to 100% (DBCC SQLPERF(logspace)).

we tried to increase the autogrowth to 30% for the log, but to no avail, we are unable to the kill the Session, unable to the backup due to extended longer duration and unable to the put the database online and ready(after restarting the 2008 R2 sql service).

What needs to be done for :

  1. to Kill that particular Session which is creating the block ( Header Block set to 1 in activity monitor)?
  2. how do i resize the log file and see to it that it has good enough space?
  3. is it possible to delete the inactive VLF?
  4. how do i make the database to become online faster ( after restart of 2008 R2 service)?
1
  • 1
    Check the error log for the recovery status. It should be left alone to recover. Vlfs should be the precaution before you encounter such scenarios. I would advise to just let it recover and keep monitoring the error log. Mar 14, 2019 at 10:58

1 Answer 1

1

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],
    wait_resource, 
    wait_time, 
    wait_type, 
    blocking_session_id
FROM sys.dm_exec_requests
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 :

  1. to Kill that particular Session which is creating the block ( Header Block set to 1 in activity monitor)?
  2. how do i resize the log file and see to it that it has good enough space?
  3. is it possible to delete the inactive VLF?
  4. how do i make the database to become online faster ( after restart of 2008 R2 service)?
  1. 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.
  2. 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).
  3. 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.
  4. 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.

2
  • Once you've taken the backup, the VLFs will be marked inactive and cleared (provided there are no open transactions) so creating a new log won't provide any benefit.
    – HandyD
    Mar 15, 2019 at 6:01
  • yes, this shall be ok, but thought of one scenario wherein that after the database is online and i take a fresh backup without giving access to anybody, shall i delete/remove the database log and recreate a fresh log file with new location as well.i know this is not a solution as the question of growing log and session not getting killed remains still, but at-least this would give some temporary relief. i would like to know about the recreation of log file a fresh as the said DB is on simple recovery model ?
    – RS Ramgiri
    Mar 15, 2019 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.