Post reply

Log Shipping - Exclusive access could not be obtained because the database is in use

  • April 18, 2017 at 4:36 am

    #329570

    Hi,

    We have been facing an issue with Log Shipping. Few databases struck in Restoring status. If we check the properties of the database It is showing as

    If we try to restore next sequence log manually It is throwing an error.


    I have tried to check the execution status of restoring log file of the database
    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
    FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
    WHERE r.command in ('RESTORE LOG')

    It shows as

    To over come this issue, we have been restarting the server and  reinitiating the log shipping. But I feel it is not the solution. Please help me in to understanding the issue and resolve the issue

    🙂

  • April 18, 2017 at 4:59 am

    #1938474

    On the  Restore Transaction Log tab on the log-shipping configuration screen there is an option to "Force disconnect users". If you select it it will mean that anybody querying the secondary database will be kicked out so that the log backup can be restored.

  • April 18, 2017 at 5:41 am

    #1938478

    Beatrix Kiddo - Tuesday, April 18, 2017 4:59 AM

    On the  Restore Transaction Log tab on the log-shipping configuration screen there is an option to "Force disconnect users". If you select it it will mean that anybody querying the secondary database will be kicked out so that the log backup can be restored.

    We have configured this while configuring logshipping. I hope It is not the problem with connected users. I have checked the connection with sp_who2 command. There are no connections exists to this Database.

    🙂

  • April 18, 2017 at 7:15 am

    #1938505

    Ram:) - Tuesday, April 18, 2017 5:41 AM

    Beatrix Kiddo - Tuesday, April 18, 2017 4:59 AM

    On the  Restore Transaction Log tab on the log-shipping configuration screen there is an option to "Force disconnect users". If you select it it will mean that anybody querying the secondary database will be kicked out so that the log backup can be restored.

    We have configured this while configuring logshipping. I hope It is not the problem with connected users. I have checked the connection with sp_who2 command. There are no connections exists to this Database.

    For more information, I have checked sysprocesses, I found that lastwaittype as "REQUEST_DISPENSER_PAUSE" and status as "suspended"

    🙂

  • April 18, 2017 at 10:46 am

    #1938613

    This appears to be an issue with IO not being able to be frozen: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

    Did previous log restores complete? Are you restoring in standby or norecovery?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 18, 2017 at 11:54 pm

    #1938721

    Steve Jones - SSC Editor - Tuesday, April 18, 2017 10:46 AM

    This appears to be an issue with IO not being able to be frozen: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

    Did previous log restores complete? Are you restoring in standby or norecovery?

    Yes, Previous Logs has been restored and It struct at one log file restore and being in restoring mode for couple of days. We are restoring in Standby mode.

  • April 19, 2017 at 7:16 am

    #1938796

    Is it possible you have some type of external backup software running (ex. Backup Exec, a backup of the whole virtual machine if it is one, etc.)?  If so, that could definitely pose a problem.  Its hold on the file would be released when you restart the server and would explain why a restart works.

    Please let us know.

  • May 3, 2017 at 1:11 am

    #1940830

    I have waited to replicate the issue once again. Issue is replicated once again. When I see the status of log file restore , it is shows 100% and it is still in restore mode. The log file size is just 103 KB. There are no third party tools being used. I need to know whats going after 100% completion of restore log since 3days from now.

    Can somebody please help me identify the issue . I don't want to restart the sql server.

    🙂

  • May 3, 2017 at 1:43 pm

    #1940987

    When you say that restore process is complete and the database is in restoring more, that's what it's supposed to be in. In log shipping, your secondary is always in restoring mode.

    The original question noted you couldn't restore logs. Is that what happened?

    whenever you check something or note a setting, you need to explain exactly where/how you go this. It can be too confusing otherwise. What did you check, what code did you run, what is the state of a particular setting. Verbiage and exact labels are important.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 4, 2017 at 2:55 am

    #1941054

    Steve Jones - SSC Editor - Wednesday, May 3, 2017 1:43 PM

    When you say that restore process is complete and the database is in restoring more, that's what it's supposed to be in. In log shipping, your secondary is always in restoring mode.

    The original question noted you couldn't restore logs. Is that what happened?

    whenever you check something or note a setting, you need to explain exactly where/how you go this. It can be too confusing otherwise. What did you check, what code did you run, what is the state of a particular setting. Verbiage and exact labels are important.

    Sorry, I feel I have confused here. Here is the situation. We have configured the log shipping in standby mode.
    When I generate Transaction Log Report, I had an alert as below

    Status Primary Database
    -- Secondary Database    		 Time Since Last Threshold Alert Enabled Time Since Last Time Since Last Latency of Last File Threshold Alert Enabled
    Alert  -- MyDB 7 min 1492 min 15 min 45 min True

    When I see the status of restore log with
    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
    FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
    WHERE r.command in ('RESTORE LOG')

    It is showing as

    SPID command Query start_time percent_complete estimated_completion_time
    186 RESTORE LOG RESTORE LOG [ProutilityMyDB] FROM DISK = N'F:\LSRestore\ProutilityMyDB\ProutilityMyDB_20170503083002.trn' WITH FILE = 1, STANDBY = N'F:\SQL Server Data Files\ProutilityMyDB_20170503084522.tuf' 2017-05-03 14:15:22.640 100 2017-05-04 14:17:00.663


    I feel something happening after this in somewhere "Redo phase" and "Undo phase" which I cannot exactly identify the issue. For immediate fix we were restarting the sql engine and it is immediately restoring all the log files.
    I want to avoid restating the server and exciting know what exactly happening.

    Please help.

    🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply