TransWikia.com

Sql Server Database 2008 R2 is in recovery mode still after 16 hours

Database Administrators Asked by RS Ramgiri on December 10, 2021

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)?

One Answer

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.

Answered by HandyD on December 10, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP