Standby database stops applying logs then restarts
Kenwoodlad
Posts: 3
I've tried searching the forum and FAQs for this. Apologies if I'm repeating a previous post.
I'm evaluating SQL Backup for use on our live servers to do Log Shipping. I have installed SQL Backup on two test servers and got log shipping up and running about 10:30 this morning. Ran a test harness to drip feed data into the source database. Log shipping seemed to be running fine, left the test servers and went to lunch. Came back at 1400 and the standby server had stopped applying logs. There were 12 logs outstanding (used Log Shipping Monitor which showed this). Event log on standby server showed these warnings:-
SQL Server Scheduled Job 'SQL Backup log shipping job for database LogShipTest created 24/10/2007 10:23:52' (0xBFF118DA4369294BA099ACC70B651CDA) - Status: Failed - Invoked on: 2007-10-24 11:32:00 - Message: The job failed. The Job was invoked by Schedule 1 (Schedule 1). The last step to run was step 1 (execute master..sqlbackup).
I had left Query Analyzer running with a connection to both the production database and the standby database. I shut down Query Analyzer and some time later, the outstanding logs were applied to the standby database.
So was it the fact QA had a connection to the standby database that made SQL Backup stop applying logs? We were hoping the standby database could be used as a reporting database but if this interferes with log shipping, we'll have to have another think.
I'm evaluating SQL Backup for use on our live servers to do Log Shipping. I have installed SQL Backup on two test servers and got log shipping up and running about 10:30 this morning. Ran a test harness to drip feed data into the source database. Log shipping seemed to be running fine, left the test servers and went to lunch. Came back at 1400 and the standby server had stopped applying logs. There were 12 logs outstanding (used Log Shipping Monitor which showed this). Event log on standby server showed these warnings:-
SQL Server Scheduled Job 'SQL Backup log shipping job for database LogShipTest created 24/10/2007 10:23:52' (0xBFF118DA4369294BA099ACC70B651CDA) - Status: Failed - Invoked on: 2007-10-24 11:32:00 - Message: The job failed. The Job was invoked by Schedule 1 (Schedule 1). The last step to run was step 1 (execute master..sqlbackup).
I had left Query Analyzer running with a connection to both the production database and the standby database. I shut down Query Analyzer and some time later, the outstanding logs were applied to the standby database.
So was it the fact QA had a connection to the standby database that made SQL Backup stop applying logs? We were hoping the standby database could be used as a reporting database but if this interferes with log shipping, we'll have to have another think.
Comments
I think you've got this absolutely correct. SQL Server does not let you restore to a database that is in use. If you have a read-only standby database, you may want to add some logic to kill sessions before the restore. We have some examples here.
Many thanks for your reply and pointing me in the right direction. Not sure if it helps us as we wanted both a warm standby and a place to run reports. We'll have to have another think.