SeanDav's questions

We have a few huge databases we would like to log ship on SQL Server 2008. Unfortunately the link between primary and secondary servers is fast enough to handle log shipping but the initial backup/copy would tie up bandwidth for days, if not weeks, which we would like to avoid if at all possible.

We have made a copy (now a few days old) by backing up the primary database to a hard disk and physically taking the hard disk to the secondary machine and restoring the database there.

Primary and Secondary Servers are in different geographical locations. Primary Server is on SQL Server 2008, Secondary on 2008 R2 (Don't ask why!)

There seems to be no way we can find to get log shipping (or mirroring) to work without starting the backup/copy/restore process from scratch.

Any ideas/solutions would be greatly appreciated.

Using MS SQL SERVER 2005. I have log shipped the ReportServer database to the secondary server in Standby/Readonly. I seem to be unable to use Reporting Services on the secondary database because it complains about the ReportServer database being Read Only.

Is there some way of doing this?

Problem Solved (Kind of...)

I am slightly embarrassed to admit that this behaviour was caused by a stored procedure called as part of an inhouse app. However, even having fixed the bug I still don't know why it was triggered by a backup!

This was the offending piece of code:

SELECT DISTINCT T.*, TL.RunDate
FROM TaskLog TL
INNER JOIN Task T ON TL.TaskID = T.ID
WHERE TL.RerunFlag = 1

UPDATE TaskLog SET RerunFlag = 0 

Changed last line to:

UPDATE TaskLog SET RerunFlag = 0 WHERE RerunFlag = 1

Update to Problem

I did a full backup of the database and that caused the log file to start growing uncontrollably. So it is not the log shipping process per se, but simply the first step - namely backing up the database that causes the issue. I checked the number of VLF's and it was over 400 yesterday, currently nearly 200 so that is definitely an issue.

Simple Statement of Problem:

Log file of a database normally around 80mb expands continually to 10’s of gigabytes with no sign of stopping if I try to initiate log shipping on the database on SQL Server 2005. DBCC SHRINKFILE has no effect.

Detailed Statement:

I have been implementing log shipping successfully on several databases but having strange behaviour on 1 particular database on SQL Server 2005. Normally this database is around 80mb data with 80mb log file for a total size of around 160mb. It does not usually vary a lot in size during the course of the day. The problems start when I try to initiate log shipping on this database using the log shipping wizard.

The wizard gets to the first step which is backup the database and reaches 100% complete. It then stops/pauses here and never reaches the next step. No error message is generated and SSMS itself is responsive. It simply appears that the wizard is taking literally forever. I stop and kill the process, but the damage has already begun. From this moment on until the time the log file is backed up and shrunk the log file keeps on expanding at the rate of around 100mb every 5 minutes.

The users are not seemingly effected (thank goodness).

Any ideas of causes and for solutions?