September 27, 2016 at 6:25 am
I have configured LS for 39 databases and scheduled to restore daily once in DR server and kept secondary DB in standby mode.All of sudden 5 databases started giving below errors and failing to restore after two days of configuration.And all 5 databases are now in restoring state.Tried restoring manually with standby mode but no luck. And these 5 databases cnages are not happening/rarely happening.
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39Skipping log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160925194230.trn' for secondary database 'QA_14_STG' because the file could not be verified.
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39*** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39*** Error: Could not apply log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160926194230.trn' to secondary database 'QA_14_STG'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39Skipping log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160926194230.trn' for secondary database 'QA_14_STG' because the file could not be verified.
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39The restore operation was successful. Secondary Database: 'QA_14_STG', Number of log backup files restored: 0
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39Deleting old log backup files. Primary Database: 'QA_14_STG'
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39The restore operation was successful. Secondary ID: 'e2f94e04-04d2-40e3-8f9d-7721425f949d'
2016-09-26 23:02:06.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.39*** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: Could not apply log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160923194230.trn' to secondary database 'QA_14_STG'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: An error occurred while processing the log for database 'QA_14_STG'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
An error occurred during recovery, preventing the database 'QA_14_STG' (7:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'QA_14_STG', file 'QA_14_STG' on file 1.
Processed 0 pages for database 'QA_14_STG', file 'QA_14_STG_DF1' on file 1.
Processed 1 pages for database 'QA_14_STG', file 'QA_14_STG_log' on file 1.(.Net SqlClient Data Provider) ***
2016-09-26 23:02:06.36*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36Skipping log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160923194230.trn' for secondary database 'QA_14_STG' because the file could not be verified.
2016-09-26 23:02:06.36*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: Could not apply log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160924194230.trn' to secondary database 'QA_14_STG'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36Skipping log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160924194230.trn' for secondary database 'QA_14_STG' because the file could not be verified.
2016-09-26 23:02:06.36*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2016-09-26 23:02:06.36*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2016-09-26 23:02:06.36*** Error: Could not apply log backup file 'd:\copy\QA_14_STG\QA_14_STG_20160925194230.trn' to secondary data
Msg 9004, Level 16, State 6, Line 1
An error occurred while processing the log for database 'OLAP'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'OLAP'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'OLAP' (10:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
September 27, 2016 at 11:17 am
Looks like you are losing a connection. Perhaps something is forcibly closing them. Or your server is undergoing some weird overload. I wonder there is more than one restore happening and there is a setting to forcibly disconnect sessions?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 28, 2016 at 5:28 am
Hi Kevin,
there is no network issues ,there is no load at all on the server at present we are not using for reporting too.Logs got restored successfully for 3 days and after that started throwing this errors.
September 28, 2016 at 1:07 pm
prem.m38 (9/28/2016)
Hi Kevin,
there is no network issues ,there is no load at all on the server at present we are not using for reporting too.Logs got restored successfully for 3 days and after that started throwing this errors.
Sorry, I've got nothing (other than the obvious of making sure the files it needs actually exist). I think you may need to get someone on the box so they can poke around and look at a few things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 28, 2016 at 2:05 pm
There is a known issue when restoring to standby. You might want to check the CU for 2014 in the KB article to see if you have that in place.
https://support.microsoft.com/en-us/kb/2987585
Sue
September 29, 2016 at 6:30 am
will try that one Sue ...thanks for the advice.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply