log shipping question 2

[TWillard]

My company is setting up log shipping. The production database is in a seperate domain. The secondary database will be as a reporting database. I have established the database maintenance plan with log shipping. The copy job is working fine and the restore job is also working. I have been able to see that changes are being copied and applied.

1) I did notice that restore job will fail if I have an open sql anaylzer connection running to the secondary database. If i close the session, then the logs can restore just fine. Has anyone else seen this happen? Is this normal? To me, this appears that I will need to time my trn restores just perfectly. I may even need to force sql server restarts to bump out connected users.

2) Within the secondary server, I have a log shipping monitor node. It shows a status of the backup is not occuring. It shows the last backup file to be "first_file_000000000000.trn". However, it shows that the copy and restore trn files are being applied. It also shows that Backup, copy, and load deltas are huge in value. This does not seem right to me.

I have read the doucmentation on setting up log shipping. I can not find good example on problems or understanding issues. Any help would be great for both questions one and two.

 

[Catadmin]

Restoration of a database always requires that the DB be in Single User mode. This is to preserve the integrity of the data. So you will either need to edit the Server properties to set it to Single User or use Sp_Config to do it.

Some of the best examples I've seen on Log Shipping have been in the SQL Server DB Admin certification books (the big thick ones, not the Exam Cram ones). Try there or Books Online for further details.

Hope this helps!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[kishy]

Hi,
You can also set the option "Terminate users in database" when you configure Log Shipping using Database Maintainence Plan Wizard.

This option is present in the Add Destination Database where you specify the Destination Server name and Secondary Database State(No Recovery/Standby).

--Kishore

 

[TWillard]

Kishy, I do have the option "Terminate users in database" selected and it is still not working if a user is conneced.

Catadmin, While single user mode may have its benifits, I do not think it will fit for my solution. The log shipped database is going to be used as a reporting database. Therefore, multiple users will connect and run queries. The option stated above shoot terminate connected users, but it does not appear to be doing such.

? - Why do my copy and load delta vary significantly from the backup? The copying and restoring are occuring (provided no users are connected). However, the message still states "Backup Not Occuring" and the last backup files is titled something like "first_file_00000000.trn"

Thanks,

Tim

 

[kishy]

Hi Tim,

Can you provide the following details to help you better :

1. What is the time interval when the log backups are copied to the secondary server ?
2. What is the time interval when the log backups are applied to the secondary server ?
3. When are the old log backups deleted from the Secondary server ?

Once you provied me with the followiong details, I will have a better understanding of the scenario.

Thanks.
--Kishore

 

[Catadmin]

Check out this Microsoft KB Article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;298967

This might be the reason you're running into this problem.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[TWillard]

Catadmin,

Thanks for the link. I also found this link that contains my errors.

http://support.microsoft.com/default.aspx?scid=kb;en-us;294397

Kishy,
1 - 15 minutes
2 - 15 minutes
3 - 2 day retention on trn files on the secondary server. 2 weeks retention on trn files on the primary server.


It appears that there is a bug in SQL Server that occurs when the restore job attempts to restore a trn file and there are connected users. I may have to work around this issue with scheduling stop/starts of sql server.

This provides some insight to the restore errors. I am still not sure why the status says backup is not occuring. Excepcially, when bak files are being generated for the primary server's database. They are being generated in the same maintenace plan that contains the log shipping.


Tim

 

[kishy]

Tim

YOu may check the backup jobs to see why the backups are failing. It provides with more information to help you to investigate the problem.

 

[TWillard]

Kishy,

The backup jobs are occuring on the primary server without error. The restore jobs are also occuring. It is just the log shipping monitor that states that the backup is not occuring. I have been pointed to the following microsoft support article. It appears to describe the symptoms of my error. I have not yet had a chance to full test all of the causes and workarounds.

http://support.microsoft.com/default.aspx?scid=kb;en-us;292586

Cataman,

You were on the right path in regards to terminating users and restore errors. The problem had to due with users accessing the database. To work around this problem, I added two additional steps into my restore job. Now my job restore jobs runs like this:

1) set database to single user mode
2) perform transaction log restores
3) set database to multi user mode

Step 2 is created with the log shipping maintenance plan wizard. I manually added in step 1 and step 3. I have been told that this issue will be resolved in SP4 of SQL Server 2000, which is not out as of 03/02/2005.


Kishy and Cataman, I am giving you both tek-tip stars for your help on this issue.

 

[Catadmin]

I'm glad I could help. I'm also glad to hear the problem is going to be resolved in SP4.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!