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

Question

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

Ram:)

SSCrazy

Points: 2246
More actions
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
🙂

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

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

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.

Ram:) SSCrazy Points: 2246 More actions · Answer 2

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.

🙂

Ram:) SSCrazy Points: 2246 More actions · Answer 3

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"

🙂

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

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?

ramprasad.prokarma SSC Veteran Points: 298 More actions · Answer 5

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.

MicahNikkel SSC-Addicted Points: 476 More actions · Answer 6

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.

Ram:) SSCrazy Points: 2246 More actions · Answer 7

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.

🙂

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 8

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.

Ram:) SSCrazy Points: 2246 More actions · Answer 9

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

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.

🙂