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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy