Standby Database Out of Sync

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):

NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
Logged on to SQL Server 'MYDBSERVERNAME' as 'NT AUTHORITY\SYSTEM' (trusted)
NULL
NULL
Attempting to load file e:\tlog_200805242100.TRN to database MYDBNAME
NULL
[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.
NULL
SQLMAINT.EXE Process Exit Code: 1 (Failed)
NULL

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:

sp_who

alter database MYDBNAME set single_user with rollback immediate

sp_who

And now if any user was connected to MYDBNAME, we could just log them off forcefully using

kill SPID

where SPID of the user is gotten from the sp_who command.

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.

About these ads

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 34 other followers

%d bloggers like this: