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 :
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 :
- 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 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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP