June 25, 2004 at 2:27 pm
Ok. Hopefully I can explain this well enough so that everyone understands the problem. We have a Primary SQL Server machine with 1 production database and a Secondary SQL Server machine that serves as our hotspare. (FYI: Databases are all setup correctly.. i.e. the db on the Secondary is setup WITH STANDBY option) These are both Standard Edition, so we don't have the included log shipping tools. What one of our developers did was create 2 applications. One called SQLLogBackup (runs every 15 mins on the Primary) and SQLLogRestore (runs every 15 mins on the Secondary). This setup runs like a champ. However, roughly once a month, the logs were getting out of synch and we couldn't seem to get them back.. it would either think the log was too early or too late. So basically, I was stuck with rebuilding our SQL Server hotspare.
Then today, I had to do it again, and noticed that the last time this happened was exactly 1 month ago. Thought it was kinda weird, but decided to look into it. Sure enough, I've had to rebuild it exactly 1 month apart. In talking with another tech here, I found out he was doing a DTS Export of 1 table on the production db on the Primary. We went back through everytime he's done it, and it coincided with when the logs quit being applied. Now, to the question. Why would the export of 1 table in our prod db cause the logs to get out of synch?
Thanks for any info, and if you need any clarification on my ramblings above, just ask.
June 27, 2004 at 2:26 am
Tim,
a couple of questions:
1) what i sthe error msg you get from the log shipping restore?
2) is the DTS export just a straight export or are there other bits? To where is the data exported?
Cheers,
- Mark
June 28, 2004 at 2:19 am
An other question: Is he doing the Export with a bulk copy with no_log-option?
Best regards
karl
June 28, 2004 at 4:45 am
I am doing manual 'log shipping' a slightly different way to you, so I don't know if my experience will help. I have got the equivalent of log shipping by doing a slight modification to my transaction log maintenance plan backup. But, what I do is more or less the same as you, so..
I have a full database backup that runs once a day (at 4am) with a matching restore onto the warm start server. If something happens to throw the hourly log backup/restores out of synch (warm start server off line, etc) during the day, I just run the full backup/restore again, and the log backups then carry on as normal. I haven't needed to do a rebuild of the warm start server. Perhaps I am lucky that the full backup only takes 10 minutes and doesn't impact on the users much so I can run it during the day.
I am suprised the DTS is causing the problem, unless the DTS is copying data onto the warm start server. Or the DTS is locking tables. Most likely, the DTS is running for a long time using a transaction, causing the backup job to wait. The backup job may be delayed long enough to cause it to run after the equivalent scheduled restore job. The restore job may then be trying to restore the same backup it restore 15 minutes earlier. I get around this problem by controling the backup and restore in the same job on the live server.
I have manually modified the full and transaction log backup jobs on the live server to run the restore on the warm start server as a second step (it starts a restore procedure running on the warm start server - I just pass it the name of the backup file as a parameter) and have added a third step to email me if the backup or restore steps fails. For information, I get the backup file name just created (on a shared folder) for each database using:
select top 1 @file_name = '\\servername' + substring(physical_device_name,
charindex('\', physical_device_name), 4000) from msdb.dbo.BackupMediaFamily bmf
left join msdb.dbo.BackupSet bs on bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'databasename' and type = 'D'
ORDER BY bs.backup_start_date desc
type = 'L' for log file backups
I also have a windows scheduled weekly dos batch file that stops SQL server on both servers and copys all the databases across to the warm start PC (including Master, MSDB, etc). I can do this as my system isn't 24/7. We do have a web site for customer information, but it is only updated by snapshot replication during working hours. I have seen comments that say databases shouldn't be copied from server to server without being detatched first, but I am not sure why. I don't seem to be having a problem. Is there a gotcha I should watch out for? I am running SQLserver 2K sp3a.
I know it is inadvisable to modify the backup jobs created automatically by the maintenance plans, but I have the modifications documented for when they need to made again.
Sorry for rambling on. But try a full backup and restore onto the warm start server. It should resynchronise the logs again.
Peter
June 28, 2004 at 7:31 am
And if your Backups go out of sync you might use something like:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.HexToInt Skriptdatum: 28.06.2004 15:26:53 ******/
--HexToInt (Sign)
create function HexToInt(@vsData char(8))
RETURNS int AS
begin
declare @iDataLength int
declare @iDataLengthM1 int
declare @iResult int set @iResult = 0
declare @i int set @i = 0
declare @iTemp int set @iTemp = 1
declare @iTempMax int
set @iDataLength=len(@vsData)
if not @vsData like replicate('[0-9a-fA-F]',@iDataLength) return NULL
set @iDataLengthM1=@iDataLength-1
while @i < @iDataLength begin
if @i=@iDataLengthM1 begin
set @iTempMax=(ASCII(substring(@vsData, @iDataLength - @i, 1))&79)%55
set @iResult =(@iResult+(@iTempMax&7)*@iTemp)
if (@iTempMax&8)=8 set @iResult=@iResult-(Power(16,@iDataLengthM1)-1)*8-8
end else begin
set @iResult =@iResult+((ASCII(substring(@vsData, @iDataLength - @i, 1))&79)%55)*@iTemp
end
set @i = @i + 1
if @i<8 set @iTemp = 16 * @iTemp
end
Return @iResult
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Benutzerdefinierte Funktion dbo.Lsn2Dec Skriptdatum: 28.06.2004 15:26:53 ******/
CREATE FUNCTION Lsn2Dec(@hexlsn nvarchar(22))
RETURNS decimal(38,0) AS
BEGIN
declare @lsn decimal(38,0)
-- select @hexlsn = (select min([Current LSN]) from master.dbo.dblog where Operation = 'LOP_BEGIN_RECOVERY')
select @lsn = master.dbo.HexToInt(convert(nchar,left(@hexlsn,patindex('%:%',@hexlsn)-1)))
select @lsn = @lsn * 10000000000
select @lsn = @lsn + master.dbo.HexToInt(substring(@hexlsn,
patindex('%:%',@hexlsn)+1,
charindex(':', @hexlsn,patindex('%:%',@hexlsn)+1)-patindex('%:%',@hexlsn)-1))
select @lsn = @lsn * 100000 + master.dbo.HexToInt(substring(@hexlsn,charindex(':', @hexlsn,patindex('%:%',@hexlsn)+1)+1,len(@hexlsn)))
return @lsn
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Objekt: Gespeicherte Prozedur dbo.user_trennen Skriptdatum: 28.06.2004 15:26:52 ******/
/* **********************************************************************************
Procedure to kick users out of database
Params: name of the database
author: Karl Klingler (actually got it from somewhere else...)
date: 22. April 2004
********************************************************************************** */
CREATE PROCEDURE user_trennen
@dbname sysname
AS
DECLARE @dbid int, @spid int, @execstr varchar(15), @waittime varchar(15), @final_chk int
--Getting the database_id for the specified database
SET @dbid = DB_ID(@dbname)
--Get the lowest spid
TryAgain:
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid)
WHILE @spid IS NOT NULL
BEGIN
--To avoid the KILL attempt on own connection
IF @spid @@SPID
BEGIN
--Killing the connection
SET @execstr = 'KILL ' + LTRIM(STR(@spid))
EXEC(@execstr)
END
--Get the spid higher than the last spid
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
END
SET @final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid = @dbid)
--New connections popped up, or killed connections aren't cleaned up yet, so try killing them again
IF (@final_chk > 1)
BEGIN
RAISERROR ('Job User rausschmeissen war nicht komplett erfolgreich.', 16, 1)
GOTO TryAgain
END
/* ++++++++++++++++++++++++ END of Procedure user_trennen +++++++++++++++++ */
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Objekt: Gespeicherte Prozedur dbo.restore_log_backups Skriptdatum: 28.06.2004 15:26:52 ******/
/* **********************************************************************************
Procedure to restore TA-Logs into the Database
Params: name of the file containing the TA-log(s)
timelag in minutes for the recovery-database
name of the database to restore into
author: Karl Klingler
date: 22. April 2004
********************************************************************************** */
CREATE PROCEDURE restore_log_backups
@backupfilepath sysname,
@DBN sysname,
@stop int
AS
-- declare variables
declare @file smallint
declare @lsn decimal(38,0)
declare @minlsn decimal(38,0)
declare @maxlsn decimal(38,0)
declare @stopat sysname
declare @msg nvarchar(2000)
declare @cmd nvarchar(2000)
DECLARE @oldfile int
declare @backupstartdate datetime
declare @hexlsn nvarchar(22)
-- set defaults
select @lsn = 0
select @minlsn = 0
select @maxlsn = 0
select @oldfile = 0
Print '--- Beginne Transaktionslogs zu Restoren...'
-- convert timelag in minutes to datetime
select @stopat= dateadd(mi,-@stop,getdate())
-- temporäre Tabellen erstellen
--drop table #dblog1
--drop table #backupfile_header
select top 0 * into #dblog1 from ::fn_dblog( default, default )
CREATE TABLE #backupfile_header
(
BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed tinyint, Position smallint, DeviceType tinyint,
UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0),
FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime,
SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int,
SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier,Collation nvarchar(128)
)
WAITFOR DELAY '00:00:05'
-- get headerinfo from backupfile into table #backupfile_header
insert #backupfile_header exec ('restore headeronly from disk = ''' + @backupfilepath + ''' ' )
WAITFOR DELAY '00:00:05'
-- get current LSN of recovery-database
insert #dblog1 exec (' use '+@dbn+' select * from ::fn_dblog( default, default )')
select @hexlsn = (select min([Current LSN]) from #dblog1)
select @lsn = master.dbo.lsn2dec(@hexlsn)
Print 'Aktuelle LSN der Datenbank '+@dbn+' ist ' + convert(nvarchar,@lsn) + '.'
-- test if backupfile contains the needed TA-backup
select @minlsn = (select top 1 FirstLsn from #backupfile_header where BackupName = 'SITESQL' order by FirstLsn)
select @maxlsn = (select top 1 LastLsn from #backupfile_header where BackupName = 'SITESQL' order by LastLsn desc)
select @msg = 'Die aktuelle LSN der DB '+@dbn+' lautet ' + rtrim(convert(char,@lsn)) + ', in der Backupdatei ' + @backupfilepath + ' sind Lsn''s von ' + rtrim(convert(char,@minlsn)) + ' bis ' + rtrim(convert(char,@maxlsn)) + ' vorhanden!'
-- if not raise hell about it
if @lsn @maxlsn
RAISERROR ( @msg,16,1) with LOG, NOWAIT
else print @msg
-- for all valid TA-backups in the backupfile: recover them, but only up until timelag
while not ((select Position from #backupfile_header where BackupName = 'SITESQL' and FirstLSN@lsn)) is NULL
begin
-- get current LSN of recovery-database
delete from #dblog1
insert #dblog1 exec (' use '+@dbn+' select * from ::fn_dblog( default, default )')
-- select @hexlsn = (select min([Current LSN]) from master.dbo.dblog where Operation = 'LOP_BEGIN_RECOVERY')
select @hexlsn = (select min([Current LSN]) from #dblog1)
select @lsn = master.dbo.lsn2dec(@hexlsn)
Print ''
Print 'Aktuelle LSN der Datenbank '+@dbn+' ist ' + convert(nvarchar,@lsn) + '.'
-- get fileposition
select @file = (select Position from #backupfile_header where BackupName = 'SITESQL' and FirstLSN@lsn)
-- same position twice means problems, break
if @oldfile = @file begin
Print '--- Restore fertig, es wurde zwei mal versucht, die selbe Fileposition zu restoren!'
break
end
-- get and print some info about current recovery
select @minlsn = (select FirstLsn from #backupfile_header where BackupName = 'SITESQL' and position = @file)
select @maxlsn = (select LastLsn from #backupfile_header where BackupName = 'SITESQL' and position = @file)
select @backupstartdate = (select BackupStartDate from #backupfile_header where BackupName = 'SITESQL' and position = @file)
Print 'Aktuelle Backup-Fileposition ist '+rtrim(convert(char,@file))+', Anfangs-LSN ist '+rtrim(convert(char,@minlsn)) + ', End-LSN ist ' + rtrim(convert(char,@maxlsn)) + '.'
Print 'Stopat ist "' + @stopat + '", BackupStartDate ist "' + rtrim(convert(varchar,@backupstartdate)) + '".'
if @backupstartdate > dateadd(mi,-@stop,getdate()) Begin
Print '--Dieser Backup muss nicht mehr Restored werden, er wurde um "' + rtrim(convert(varchar,@backupstartdate)) + '" erstellt, '
Print 'die Rücksicherung geht nur bis "' + @stopat + '". Restore wird beendet...'
break
end
-- kick all users out of the recovery-db
exec master..user_trennen @DBN
WAITFOR DELAY '00:00:05'
-- do the actual recovery
set @cmd = 'RESTORE LOG '+@dbn+' FROM DISK = ''' + @backupfilepath + '''
WITH DBO_ONLY, STANDBY = ''e:\undo_'+@dbn+' .ldf'', STOPAT = ''' + @stopat + ''', FILE = ' + convert(varchar,@file)
Print 'SQL-Command: "' + @cmd + '".'
EXEC (@cmd)
WAITFOR DELAY '00:00:05'
-- memorize fileposition
select @oldfile = @file
end
drop table #dblog1
drop table #backupfile_header
-- now no more backups in this backupfile
Print 'In der aktuellen Backupdatei ' + @backupfilepath + ' sind keine weiteren TA-Logs mehr nachzuziehen!. ' + convert(nvarchar,getdate())
Print '--- Ende des Transaktionslog-Restore...'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Best regards
karl
June 28, 2004 at 12:20 pm
Thanks for all the replies everyone. I was a bit misinformed in my first post, so I'll clarify a few things.
First, here are the servers involved. SERVER1 and SERVER2 (these are the 2 servers that are replicated). SERVER3 is a server that contains our billing database.
The process that causes log shipping to fail between SERVER1 and SERVER2 is a local DTS package on SERVER3. The following are the options that are CHECKED in the package as well as a .bas file containing the code:
Create destination objects (tables, views, stored procedures, constraints, etc)
-Drop destination objects first
-Include extended properties
Copy data
-Replace existing data
Use Collation
Then clicking on the Options button the only thing that is checked is:
Use quoted identifiers when copying objects
Here is the code from the .bas file of the package:
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = "SERVER3_TO_SERVER1"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSTransferObjectsTask_1"
oStep.Description = "SERVER3_TO_SERVER1"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSTransferObjectsTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task DTSTask_DTSTransferObjectsTask_1 (SERVER3_TO_SERVER1)
Call Task_Sub1( goPackage )
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
'------------- define Task_Sub1 for task DTSTask_DTSTransferObjectsTask_1 (SERVER3_TO_SERVER1)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.TransferObjectsTask2
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSTransferObjectsTask_1"
oCustomTask1.Description = "SERVER3_TO_SERVER1"
oCustomTask1.SourceServer = "SERVER3"
oCustomTask1.SourceLogin = "sa"
oCustomTask1.SourceUseTrustedConnection = False
oCustomTask1.SourceDatabase = "DATABASE1"
oCustomTask1.DestinationServer = "SERVER1"
oCustomTask1.DestinationLogin = "sa"
oCustomTask1.DestinationUseTrustedConnection = False
oCustomTask1.DestinationDatabase = "DATABASE2"
oCustomTask1.ScriptFileDirectory = "C:\Program Files\Microsoft SQL Server\80\Tools"
oCustomTask1.CopyAllObjects = False
oCustomTask1.IncludeDependencies = False
oCustomTask1.IncludeLogins = False
oCustomTask1.IncludeUsers = False
oCustomTask1.DropDestinationObjectsFirst = True
oCustomTask1.CopySchema = True
oCustomTask1.CopyData = 1
oCustomTask1.ScriptOption = -2147069211
oCustomTask1.ScriptOptionEx = 4198416
oCustomTask1.SourceTranslateChar = True
oCustomTask1.DestTranslateChar = True
oCustomTask1.DestUseTransaction = False
oCustomTask1.UseCollation = True
oCustomTask1.AddObjectForTransfer "TABLE1", "dbo", 8
oCustomTask1.AddObjectForTransfer "TABLE2", "dbo", 8
oCustomTask1.AddObjectForTransfer "TABLE3", "dbo", 8
oCustomTask1.AddObjectForTransfer "TABLE4", "dbo", 8
oCustomTask1.AddObjectForTransfer "TABLE5", "dbo", 8
oCustomTask1.AddObjectForTransfer "TABLE6", "dbo", 8
oCustomTask1.AddObjectForTransfer "TABLE7", "dbo", 8
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
If you need any more info, let me know.
June 28, 2004 at 2:17 pm
For what it's worth, I have log shipping set up and ran into a similar issue but not with DTS. Another data extracting tool was developed in-house using SQLDMO and every time it ran it reset the source database's dboptions, effectively blowing my tlog shipping to pieces. I arranged to have it fixed and log shipping has been happily purring away since.
Good luck.
June 28, 2004 at 3:26 pm
Tim,
Further to Ken's post, I set up and traced a DTS object transfer similar to yours, and noted the reset of the db options for the target database (which, in your case, is subsequently the source database for log shipping)...
use master exec sp_dboption N'TargetDB', N'select into/bulkcopy', N'true'
and later...
use master exec sp_dboption N'TargetDB', N'select into/bulkcopy', N'false'
It is most likely this change of recovery model for your log shipping source that is affecting things.
If you look at job results for the first transaction log backup that runs after your DTS job, you may see a message like:
"There is no current database backup. This log backup cannot be used to roll forward a preceding database backup."
Cheers,
- Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply