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.

Advertisements

Poor Man’s Log Shipping

We wanted to have off-site backups for our database. SQL Server 2000 provides Log Shipping. With log shipping, a server can automatically copy and restore its logs to a stand-by server. However, it can only be done on a local network.

We do have log shipping enabled at our data center. However, we wanted to keep log backups – as backups, without restoring them – to a remote site. So we implemented a poor man’s log shipping using Windows scheduling, zip application from Info-Zip, and Xlight FTP server.

We chose one full backup as our starting point. Subsequent backups of transaction logs were to be shipped offsite. We used 7-Zip to chop up and compress the approximately 40 GB of database backup into approximately 8 GB of a regular zip file. The settings were kept at their default values, except the part where the resulting zip file was to be split up into 650 MB sizes.

Using batch files (code at the end) scheduled to run once a day, we copy all files created from midnight the previous day up to the point the batch file is run to a temporary location on the local machine. This collection of files is compressed and then sent off-site via FTP. Since we do not have an instance of SQL Server at the remote site, we do not restore these logs or apply them to the full backup taken.

Main Batch File

:: Resources that provided the code or helped write it:
:: http://209.85.165.104/search?q=cache:BWcLwCl7CtgJ:www.experts-exchange.com/Operating_Systems/MSDOS/Q_21135047.html+batch+file+concat+string&hl=en&ct=clnk&cd=1&gl=us&client=firefox-a
:: http://64.85.16.166/adb/adbdos.htm
:: http://malektips.com/xp_dos_0013.html
:: http://malektips.com/xp_dos_0002.html
:: http://www.computerhope.com/batch.htm#4
:: http://www.computing.net/programming/wwwboard/forum/14356.html
echo %time%
c:
mkdir “C:\BKPS-TEMP”
@FOR /F “tokens=*” %%i IN (‘yesterday.bat’) DO set ystrday=%%i
set todaydate=-%date:~10,4%-%date:~4,2%-%date:~7,2%
xcopy “C:\BKPS\*.*” “C:\BKPS-TEMP” /D:%ystrday% /s /q
zip -r “C:\BKPS-TEMP\db1tlogbkp.zip” “C:\BKPS-TEMP”
cd..
cd..
cd..
c:
cd “C:\BKPS-TEMP”
rename bkp.zip bkp%todaydate%.zip
c:
FTP -s:ftp.txt ftp.foobar.com
c:
rmdir “C:\BKPS-TEMP” /s /q
c:
echo %time%

FTP File

username
password
lcd “C:\BKPS-TEMP”
prompt
mput bkp*.zip
quit

yesterday.bat

:: This file gives yesterday’s date, that is, today minus one
:: The code was taken as is from the following web page
:: Get Yesterday date in MS DOS Batch file posted by srini_vc
:: If you are the author of the code and would like the code taken down, please leave a comment and we will get back to you

@echo off

set yyyy=

set $tok=1-3
for /f “tokens=1 delims=.:/-, ” %%u in (‘date /t’) do set $d1=%%u
if “%$d1:~0,1%” GTR “9” set $tok=2-4
for /f “tokens=%$tok% delims=.:/-, ” %%u in (‘date /t’) do (
for /f “skip=1 tokens=2-4 delims=/-,().” %%x in (‘echo.^|date’) do (
set %%x=%%u
set %%y=%%v
set %%z=%%w
set $d1=
set $tok=))

if “%yyyy%”==”” set yyyy=%yy%
if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% – 100

set CurDate=%mm%/%dd%/%yyyy%

set dayCnt=%1

if “%dayCnt%”==”” set dayCnt=1

REM Substract your days here
set /A dd=1%dd% – 100 – %dayCnt%
set /A mm=1%mm% – 100

:CHKDAY

if /I %dd% GTR 0 goto DONE

set /A mm=%mm% – 1

if /I %mm% GTR 0 goto ADJUSTDAY

set /A mm=12
set /A yyyy=%yyyy% – 1

:ADJUSTDAY

if %mm%==1 goto SET31
if %mm%==2 goto LEAPCHK
if %mm%==3 goto SET31
if %mm%==4 goto SET30
if %mm%==5 goto SET31
if %mm%==6 goto SET30
if %mm%==7 goto SET31
if %mm%==8 goto SET31
if %mm%==9 goto SET30
if %mm%==10 goto SET31
if %mm%==11 goto SET30
REM ** Month 12 falls through

:SET31

set /A dd=31 + %dd%

goto CHKDAY

:SET30

set /A dd=30 + %dd%

goto CHKDAY

:LEAPCHK

set /A tt=%yyyy% %% 4

if not %tt%==0 goto SET28

set /A tt=%yyyy% %% 100

if not %tt%==0 goto SET29

set /A tt=%yyyy% %% 400

if %tt%==0 goto SET29

:SET28

set /A dd=28 + %dd%

goto CHKDAY

:SET29

set /A dd=29 + %dd%

goto CHKDAY

:DONE

if /I %mm% LSS 10 set mm=0%mm%
if /I %dd% LSS 10 set dd=0%dd%

echo %mm%-%dd%-%yyyy%