January 22, 2015 at 11:00 pm
Dear All,
I have a Primary Site & trying to setup a DR site. I created a Log shipping between the two database, Backup & Copy works fine. Restore also works fine for the 1st time however after that restore fails & database goes to Recovery mode (showing restoring) from the Standby Mode.
The Backup & Copy folders have correct permissions & i am using AD account to run the Agent service on both the servers.
Please advise what could be wrong.
Below is the restore log.
Message
current state is closed.(System.Data) ***
2015-01-23 11:50:13.28*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.37Skipping log backup file 'E:xxxxxxxx.trn' for secondary database 'Databasename' because the file could not be verified.
2015-01-23 11:50:13.37*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.37*** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.37*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.37*** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.37*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.37Deleting old log backup files. Primary Database: 'databasename'
2015-01-23 11:50:13.37*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.37*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.39The restore operation completed with errors. Secondary ID: 'eb149c41-c657-4f42-925c-6cd635addcb9'
2015-01-23 11:50:13.39*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.39*** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2015-01-23 11:50:13.39*** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2015-01-23 11:50:13.39----- END OF TRANSACTION LOG RESTORE -----
January 23, 2015 at 8:45 am
Maybe it is just your efforts to anonymize the name, but you are missing a backslash in the file name? 'E:xxxxxxxx.trn'
Outside of that try running a profiler trace on the secondary to see the exact sql server calls that are done by log shipping?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 10, 2015 at 2:15 am
Sanjay,
It could be your reporting/SSMS component has an open connection to the database therefore when restoring the log file it is unable to obtain an exclusive connection to the database due to the open connections. There would be an option when setting up log shipping to disconnect any connections to allow it to restore the log backup.
When setting up a log shipping secondary as a read-only standby, there is an option to disconnect any existing connections before attempting log shipping restores. This option is available whether you use the Management Studio GUI or T-SQL commands.
Else you can try in 'NoRecovery' mode where user connections and read only mode is prohibited, test it and let me know the output.
Regards,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply