Standby Database Out of Sync
May 26, 2008 Leave a comment
Our setup has SQL Server 2000 running in Log Shipping mode for high availability. One day we received a message saying: “Standby database has been out-of-sync for x minutes”. To check for the job which was running, open Your Database then Management then SQL Server Agent then Jobs. Right-click the job which is giving error then View Job History then check Show Step Detail. Read through the messages and then click close. Again right-click the job and then Properties. Go to Steps tab and then choose each step and then click Edit. Copy the Command and paste in a text file. Do this for all steps in the job and close everything.
In Query Analyzer, run these commands one by one. Essentially what you are doing is running the job manually. Whatever message you get, just copy and paste it in a text file. Go through these messages to see exactly what the problem is.
The message we received was as follows (changed names of DB for reasons):
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
Logged on to SQL Server 'MYDBSERVERNAME' as 'NT AUTHORITY\SYSTEM' (trusted)
Attempting to load file e:\tlog_200805242100.TRN to database MYDBNAME
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101: [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
SQLMAINT.EXE Process Exit Code: 1 (Failed)
After researching error 310, we came across a very good thread: sqlmaint.exe failed. Some issues were:
- Disk space is full
- Trying to create transaction log backups for Master database
- ‘NT AUTHORITY\SYSTEM’ doesn’t have dbo rights
- Other things are using the DB
We did not have the first three problems so we thought maybe someone else was using the database. So we ran the following:
alter database MYDBNAME set single_user with rollback immediate
And now if any user was connected to MYDBNAME, we could just log them off forcefully using
where SPID of the user is gotten from the
We did and when we ran the commands in the job again, we got the same error. So if it wasn’t another user connected, what else could it be? Also, you have to run the job commands under Master database, not under the database for whom the jobs are being run.
We thought it might be a process. So in Enterprise Manager we looked at Management then Current Activity then Locks / Objects and then MYDBNAME. Right-click the process and see if any job is running. Kill the last process run.
We ran the commands in the job again and this time it ran without any errors. So the issue was solved.
Remember to set the DB back to multiple users:
alter database MYDBNAME set multi_user
This is how we fixed the problem. Remember, this was a standby server where databases were Read Only. If your database is live and not on standby, these instructions are not for you.