December 20, 2010 at 12:29 am
Hi,
We have BizTalk 2006 R2 databases on SQL Server 2005 EE with SP3. We have 3 instances for BizTalk databases as below:
One instance1, we have 3 databases
BizTalkMgmtDb
BizTalkRuleEngineDb
SSODB
On instance2, we have, we have 1 database:
BizTalkMsgBoxDb
On instance3, we have 1 database:
BizTalkDTADb
From instance2, I have configured the BizTalk built-in backup job Backup BizTalk Server (BizTalkMgmtDb), which will backing up all the databases from above 3 instances.
Jon step:exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '\\sqlvs2\BizBackups' /* location of backup files */, 1 /* ForceFullBackupAfterPartialSetFailure */, '22'/* local time hour for the backup process to run */
Job 'Backup BizTalk Server (BizTalkMgmtDb)' : Step 1, 'BackupFull' : Began Executing 2010-12-19 22:00:00
Msg 3013, Sev 16, State 1, Line 15 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]
Msg 3271, Sev 16, State 1, Line 15 : A nonrecoverable I/O error occurred on file "\\sqlvs2\BizBackups\SQLVS2_BIZTalk_BizTalkDTADb_Full_BTS_2010_12_19_22_00_00_680.bak:" 64(The specified network name is no longer available.). [SQLSTATE 42000]
Msg 4035, Sev 16, State 1, Line 15 : Processed 6144 pages for database 'BizTalkMsgBoxDb', file 'BizTalkMsgBoxDb' on file 1. [SQLSTATE 01000]
Msg 4035, Sev 16, State 1, Line 15 : Processed 3 pages for database 'BizTalkMsgBoxDb', file 'BizTalkMsgBoxDb_log' on file 1. [SQLSTATE 01000]
Msg 3014, Sev 16, State 1, Line 15 : BACKUP DATABASE successfully processed 6147 pages in 8.690 seconds (5.793 MB/sec). [SQLSTATE 01000]
Msg 4035, Sev 16, State 1, Line 15 : Processed 159304 pages for database 'BizTalkDTADb', file 'BizTalkDTADb' on file 1. [SQLSTATE 01000]
Msg 4035, Sev 16, State 1, Line 15 : Processed 3 pages for database 'BizTalkDTADb', file 'BizTalkDTADb_log' on file 1. [SQLSTATE 01000]
Please advice..
December 20, 2010 at 12:57 am
Probably a temporary network glitch. SQL won't retry if the network drops. That's one reason the usual recommendation is to backup locally then copy the backup files across the network. More reliable and faster to backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2010 at 5:49 am
I concur with Gail. The network drive is inaccessible in the middle of the backup. Please try taking the database backup on the local server and then move it to the network share using tools like ROBOCOPY.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 20, 2010 at 10:13 am
The database is backing up to local disk (clustered disk) only. But the job step requires the backup path should be in the format of network share.
Jon step:exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '\\sqlvs2\BizBackups' /* location of backup files */, 1 /* ForceFullBackupAfterPartialSetFailure */, '22'/* local time hour for the backup process to run */
The BizTalk backup job runs every 30 mins, starting step1 "full backup" at 10 PM and step2 "log backup" every 30 mins. The full backup job is failing at 10 PM but when the job runs again at 10:30 PM, its taking the full backup of the failed databases at 10 PM and log backup of of the rest of the databases (The BizTalk backup job logic is implemented to run a full backup again if it fails for any reason before running the log backup)
So, It's failing daily exactly at 10PM only and running fine rest of the day (i.e every 30 mins)
Please advice..
December 20, 2010 at 10:39 am
What else do you have running on that server at 10PM?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 20, 2010 at 1:33 pm
gmamata7 (12/20/2010)
The database is backing up to local disk (clustered disk) only. But the job step requires the backup path should be in the format of network share.
What are you using to back up the database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2010 at 2:54 pm
It's a built-in BizTalk job which uses the below stored procedure to backup the databases from all of the 3 instances.
USE [BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupAllFull_Schedule] Script Date: 12/20/2010 13:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_BackupAllFull_Schedule] @Frequency nchar(1), @MarkName nvarchar(8), @BackupPath nvarchar(128), @ForceFullBackupAfterPartialSetFailure bit = 0, @BackupHour int=NULL
as
begin
set nocount on
declare @localized_string_sp_BackupAllFull_Schedule_Failed nvarchar(100)
set @localized_string_sp_BackupAllFull_Schedule_Failed = N'sp_BackupAllFull_Schedule failed'
declare @localized_string_sp_BackupAllFull_Schedule_Failed_Unknown_Frequency nvarchar(100)
set @localized_string_sp_BackupAllFull_Schedule_Failed_Unknown_Frequency = N'Unknown value for the parameter @Frequency'
declare @localized_string_sp_BackupAllFull_Schedule_Failed_Executing_Backup nvarchar(100)
set @localized_string_sp_BackupAllFull_Schedule_Failed_Executing_Backup = N'Failed running sp_BackupAllFull'
/*
Start new log shipping strings
*/
declare @localized_string_sp_BackupAllFull_Schedule_Failed_SelectingForceFull nvarchar(100)
set @localized_string_sp_BackupAllFull_Schedule_Failed_SelectingForceFull = N'Failed selecting the ForceFull value from the adm_ForceFullBackup table'
declare @localized_string_sp_BackupAllFull_Schedule_Failed_UpdatingForceFull nvarchar(100)
set @localized_string_sp_BackupAllFull_Schedule_Failed_UpdatingForceFull = N'Failed updating the ForceFull value in the adm_ForceFullBackup table'
declare @localized_string_sp_BackupAllFull_Schedule_Failed_SelectHighestPartial nvarchar(100)
set @localized_string_sp_BackupAllFull_Schedule_Failed_SelectHighestPartial = N'Failed searching for partial backup sets'
/*
End new log shipping strings
*/
declare@retint
,@errorint
,@rowcountint
,@errorDescnvarchar(128)
,@ForceFullbit
,@CurrDT DateTime
if (@BackupHour is NULL) or (@Frequency = 'h' or @Frequency = 'H')
begin
select @CurrDT = getutcdate()
end
else
begin
select @CurrDT = getdate()
if (@BackupHour<0) or (@BackupHour>23)
begin
select @BackupHour=0
end
end
/*
Check to see if we need to backup
*/
/*
First check if a forced full backup is requested regardless of any other state
*/
selecttop 1 @ForceFull = [ForceFull]
from[dbo].[adm_ForceFullBackup]
select@error= @@ERROR
,@rowcount= @@ROWCOUNT
if @error <> 0
begin
set @errorDesc = @localized_string_sp_BackupAllFull_Schedule_Failed_SelectingForceFull
goto FAILED
end
if @ForceFull = 1
goto DO_BACKUP
/*
Now check if we need to do a full backup based on partial set conditions
*/
if @ForceFullBackupAfterPartialSetFailure = 1
begin
/*
If a partial set exists with no complete full backup set after it do a full backup
*/
declare @HighestPartial bigint
select @HighestPartial = max(BackupSetId)
from[dbo].[adm_BackupHistory]
whereSetComplete = 0
if @@ERROR <> 0
begin
set @errorDesc = @localized_string_sp_BackupAllFull_Schedule_Failed_SelectHighestPartial
goto FAILED
end
if @HighestPartial is not null
and not exists(select1
from[dbo].[adm_BackupHistory]
where[BackupType] = 'db'
and[BackupSetId] > @HighestPartial
and[SetComplete] = 1
group by[BackupSetId] )
begin
goto DO_BACKUP
end
end
declare @MaxDT DateTime
select @MaxDT = max(BackupDateTime)
from[dbo].[adm_BackupHistory]
whereBackupType='db'
/*
If there's nothing in the table do the backup
*/
if @MaxDT is null
goto DO_BACKUP
/*
Check to see if we already have a full backup for the specified frequency interval
*/
if @Frequency = 'h' or @Frequency = 'H'
begin
if datepart( year, @MaxDT ) = datepart( year, @CurrDT ) and
datepart( month, @MaxDT ) = datepart( month, @CurrDT ) and
datepart( day, @MaxDT ) = datepart( day, @CurrDT ) and
datepart( hour, @MaxDT ) = datepart( hour, @CurrDT )
goto DONE
end
else if @Frequency = 'd' or @Frequency = 'D'
begin
if datepart( year, @MaxDT ) = datepart( year, @CurrDT ) and
datepart( month, @MaxDT ) = datepart( month, @CurrDT ) and
datepart( day, @MaxDT ) = datepart( day, @CurrDT )
goto DONE
end
else if @Frequency = 'w' or @Frequency = 'W'
begin
if datepart( year, @MaxDT ) = datepart( year, @CurrDT ) and
datepart( month, @MaxDT ) = datepart( month, @CurrDT ) and
datepart( week, @MaxDT ) = datepart( week, @CurrDT )
goto DONE
end
else if @Frequency = 'm' or @Frequency = 'M'
begin
if datepart( year, @MaxDT ) = datepart( year, @CurrDT ) and
datepart( month, @MaxDT ) = datepart( month, @CurrDT )
goto DONE
end
else if @Frequency = 'y' or @Frequency = 'Y'
begin
if datepart( year, @MaxDT ) = datepart( year, @CurrDT )
goto DONE
end
else
begin
select @errorDesc = @localized_string_sp_BackupAllFull_Schedule_Failed_Unknown_Frequency
goto FAILED
end
--lets check the hour to backup
if (@BackupHour is NOT NULL) and
(@Frequency <> 'h' and @Frequency <> 'H') and
(datepart(hour,@CurrDT) <> @BackupHour)
goto DONE
DO_BACKUP:
declare @UseLocalTime bit
if (@BackupHour is NOT NULL) and
(@Frequency <> 'h' and @Frequency <> 'H')
begin
select @UseLocalTime = 1
end
else
begin
select @UseLocalTime =0
end
exec @ret = [dbo].[sp_BackupAllFull] @MarkName, @BackupPath, @CurrDT,@UseLocalTime
if @@ERROR <> 0 or @ret <> 0 or @ret IS NULL
begin
select @errorDesc = @localized_string_sp_BackupAllFull_Schedule_Failed_Executing_Backup
GOTO FAILED
end
else
begin
/*
If this is a forced backup reset the flag so we don't force another
*/
if @ForceFull = 1
begin
update[dbo].[adm_ForceFullBackup]
set[ForceFull] = 0
if @@ERROR <> 0
begin
select @errorDesc = @localized_string_sp_BackupAllFull_Schedule_Failed_UpdatingForceFull
GOTO FAILED
end
end
GOTO DONE
end
FAILED:
if @errorDesc is null
select @errorDesc = @localized_string_sp_BackupAllFull_Schedule_Failed
raiserror( @errorDesc, 16, -1 )
return -1
DONE:
return 0
end
December 23, 2010 at 12:47 am
But the backup job is running fine for the next run running at 10:30 PM (The backup job runs every 30 mins. Full backup at 10 PM and log backup at every 30 mins). Why it's failing for the 1st time when full backup runs and running fine after 30 mins?
I also ran dbcc checkdb (BizTalkDTADb) with all_errormsgs and dbcc checkdb (BizTalkDTADb) with no_infomsg and everything is fine.
So why the backup job is reporting the IO error?
December 23, 2010 at 9:20 am
What else do you have running at 10Pm on that server? It sounds like something may be causing a conflict with the 10PM run.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2010 at 10:54 am
The backup drive is clustered file share and I created the share as BizBackups and the path is Z:\BizBackups.
But in the backup path, I have given it as \\sqlvs2\BizBackups so that backup will run fine when the node failovers.
It worked fine but from last 1 days it's failing..
I tried by changing the full backup time as 11 PM as below but still the job failes with same message:
Jon step:exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '\\sqlvs2\BizBackups' /* location of backup files */, 1 /* ForceFullBackupAfterPartialSetFailure */, '23'/* local time hour for the backup process to run */
I tried running the job using backup database command to the same path \\sqlvs2\BizBackups and getting the same error.
I tried running the job using backup database command to the path Z:\BizBackups and is successful.
So the database is healthy but some problem with the backup path. please advice..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply