September 15, 2008 at 12:42 pm
hi,
I got an error for backup job as beloe in error log:
spid58 BackupMedium::ReportIoError: write failure on backup device '\\backup_db\d$\Backup\abc\abc01\abc01_09-14-2008.bak'. Operating system error 112(There is not enough space on the disk.).
spid58 Internal I/O request 0x306D21E0: Op: Write, pBuffer: 0x08310000, Size: 983040, Position: 11132018176, UMS: Internal: 0x0, InternalHigh: 0xF0000, Offset: 0x97851E00, OffsetHigh: 0x2, m_buf: 0x08310000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: \\backup_db\d$\Backup\abc01\abc01\abc01_09-14-2008.bak
2008-09-14 02:18:12.37 backup BACKUP failed to complete the command
Exec DB_BACKUP_abc 'abc01'
But I have 60gb free space. Could you plz tell me what will be the reason for failing the backup?
September 15, 2008 at 1:04 pm
The error is pretty specific. How big is the database, including the log?
-- You can't be late until you show up.
September 15, 2008 at 3:39 pm
The size of the database 14gb.
September 15, 2008 at 3:47 pm
Could have also been caused by a network error. If you have the space on the server itself, it would be better to backup to disk locally, then move the backup file to a network share. That way if there is a network failure, the backup still completes.
😎
September 15, 2008 at 4:04 pm
What version of Sql Server are you using? Has this job run successfully before?
Your backing up over a admin share (D$) so I would verify the security permissions although that is out of line with error. If that looks good, I would dbcc checkdb the database. If you have access and space, the backup command might be choking on something internal to the db.
David
September 15, 2008 at 5:15 pm
Thanks David,
Iam using sql server 2000. The backup failed is a log backup. The job is scheduled to run every hour, at that particular time like 3:12am it failed, but the next hour the logbackup of same database is fine. The job is running fine from past 3 months. I suddenly get this error last night?
September 15, 2008 at 6:48 pm
Is that share local to the server your running the backup on?
If so please post the log file for the last few days and the exact backup command you running. This command will get the log file:
exec sp_readerrorlog;
Just to verify something run this command too and indicate what database you are trying to backup.
dbcc sqlperf(logspace)
David
September 15, 2008 at 10:25 pm
Hello David,
Iam taking the backups of server A to the 'D' drive of server B, because Server B has lots of space on it. Iam doing this from past several months. Everything working fine. But only last night i got this error.
September 16, 2008 at 5:53 am
Are you absolutely certain the log hasn't grown to some enormous size, preventing it from backing up to the share? See David O's reply....
-- You can't be late until you show up.
September 16, 2008 at 8:35 am
I suspect your backups are not running the way you think they are. To aid you further ,I need the results of this script:
exec sp_helpdb 'your db name between quotes'
go
exec sp_readerrorlog
go
dbcc sqlperf(logspace)
go
Save the output to a text file and attach it using the attach button at the bottom of the post screen. Zip it if you have to.
I also need the exact backup command that is failing. We can get you up and running again but we are going to have to go a level deeper.
David
September 16, 2008 at 8:45 am
hey,
As Lynn has already mentioned, sounds like a network blip. When was last successful full backup and where was this stored? Have you tried re-running backup and if so, what was result?
Thanks,
Phillip Cox
September 16, 2008 at 8:56 am
Thanks a lot David. Here are the details in attachments
exec sp_helpdb
exec sp_readerrorlog
go
dbcc sqlperf(logspace)
Thanks
September 16, 2008 at 9:05 am
Here is the script we are using for backups.
CREATE PROCEDURE DB_BACKUP
--DECLARE
@Dbname VARCHAR(30)
AS
BEGIN
--DECLARE @Dbname VARCHAR(30)
DECLARE @RestoreString Varchar(500)
DECLARE @File VARCHAR(200)
DECLARE @File1 VARCHAR(200)
DECLARE @Device_Name VARCHAR(200)
DECLARE @FileExist INT
--SELECT @Dbname='Test'
IF NOT EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = @Dbname)
BEGIN
RAISERROR('DATABASE NOT FOUND', 16, 1)
RETURN
END
SET @File = 'D:\Backup\ADVFUJI\'+@Dbname+'\'+@Dbname+'_'+
CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
PRINT @File
SET @Device_Name = @Dbname+'_'+
CONVERT(VARCHAR(10),GETDATE(),110)
PRINT @Device_Name
SET @File1 = 'DIR "D:\Backup\ADVFUJI\'+@Dbname+'"'
Print 'Folder ' + @File1
CREATE TABLE #OUTPUT(CONTENTS VARCHAR(300))
INSERT #OUTPUT EXEC MASTER.DBO.XP_CMDSHELL @File1
IF EXISTS(SELECT 1 FROM #OUTPUT WHERE CONTENTS LIKE 'FILE NOT FOUND')
BEGIN
RAISERROR('FOLDER DOES NOT EXIST!', 16, 1)
PRINT 'FOLDER DOES NOT EXIST!'
END
ELSE
BEGIN
PRINT 'FOLDER DOES EXIST!'
END
DROP TABLE #OUTPUT
IF NOT EXISTS (SELECT NAME FROM dbo.sysdevices WHERE NAME = @Device_Name)
BEGIN
--SELECT getdate()
EXEC sp_addumpdevice 'DISK', @Device_Name,@File
PRINT @Dbname +' Backup Device Created'
END
--SET @File1 = 'D:\Backup\ADVFUJI\'+@Dbname+'\'+@Device_Name
--Print @File1
/*
--File Existence
Exec Master..xp_fileexist @File,@FileExist Output
IF @FileExist=0
BEGIN
RAISERROR('FILE NOT FOUND', 16, 1)
RETURN
END*/
-----------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM msdb..backupset WHERE database_name=@Dbname
AND type='D' AND backup_start_date >=CONVERT(VARCHAR,GETDATE(),101))
BEGIN
DECLARE @BackupString VARCHAR(500)
SET @BackupString = 'BACKUP DATABASE ' + @Dbname + '
TO "' + @Device_Name + '" WITH
NOFORMAT
, NOINIT
, NAME = '''+@Dbname+'--FULL Database Backup''
, SKIP
, NOREWIND
, NOUNLOAD
, STATS = 10
-- , CHECKSUM '
PRINT @BackupString
EXECUTE ( @BackupString )
PRINT @Dbname + ' FULL BACKUP CREATED'
IF NOT EXISTS
(SELECT * FROM msdb..backupmediafamily
WHERE logical_device_name=@Device_Name)
BEGIN
SET @File='VERIFY FAILED. BACKUP INFORMATION FOR DATABASE '+@Dbname+' NOT FOUND.'
SELECT @File
RAISERROR(@File, 16, 1)
END
ELSE
BEGIN
SET @RestoreString = 'RESTORE VERIFYONLY FROM "'+ @Device_Name+'"'
PRINT @RestoreString
EXECUTE (@RestoreString)
PRINT 'RESTORE FULL BACKUP VERIFY IS SUCESS'
END
PRINT ' '
RETURN
END
-----------------------------------------------------------------------------
DECLARE @Date DATETIME
SELECT @Date=MAX(backup_start_date) FROM msdb..backupset WHERE database_name=@Dbname
AND type IN ('D','I') and backup_start_date>=CONVERT(VARCHAR,GETDATE(),101)
IF datediff(hh,@Date,getdate()) <=6
BEGIN
DECLARE @LogBackupString VARCHAR(500)
SET @LogBackupString = 'BACKUP LOG ' + @Dbname + '
TO "' + @Device_Name + '" WITH
NOFORMAT
, NOINIT
, NAME = '''+@Dbname+'--LOG Database Backup''
, SKIP
, NOREWIND
, NOUNLOAD
, STATS = 10
--, CHECKSUM '
PRINT @LogBackupString
PRINT 'LOG BACKUP IS GOING ON '
EXECUTE ( @LogBackupString )
PRINT 'LOG BACKUP IS SUCCESS'
END
ELSE
BEGIN
DECLARE @DiffBackupString VARCHAR(500)
SET @DiffBackupString = 'BACKUP DATABASE ' + @Dbname + '
TO "' + @Device_Name + '" WITH DIFFERENTIAL
, NOFORMAT
, NOINIT
, NAME = '''+@Dbname+'--DIFF Database Backup''
, SKIP
, NOREWIND
, NOUNLOAD
, STATS = 10
-- , CHECKSUM '
PRINT @DiffBackupString
PRINT 'DIFF BACKUP IS GOING ON '
EXECUTE (@DiffBackupString )
PRINT 'DIFF BACKUP IS SUCCESS'
END
--SET @File = 'D:\Backup\ADVFUJI\'+@Dbname+'\'+@Dbname+'_'+CONVERT(VARCHAR(10),DATEADD(dd,-2,GETDATE()),110)+'.bak'
SET @Device_Name = @Dbname+'_'+ CONVERT(VARCHAR(10),DATEADD(dd,-2,GETDATE()),110)
PRINT @File
PRINT @Device_Name
IF EXISTS (SELECT NAME FROM dbo.sysdevices WHERE NAME = @Device_Name)
BEGIN
--SELECT getdate()
EXEC sp_dropdevice @Device_Name,'DELFILE'
PRINT @Device_Name + 'CF_AQS_Integrator_PROD BACKUP DEVICE DROPPED'
END
END -- Final
GO
September 16, 2008 at 10:45 am
To give exact picture.. There is a job running this stored procedure at every one hour between 2AM to 8PM. And initially I thought logbackup is failing but going through error log its seems full backup itself is failing. Plz clarify on this issue....
Thank You
September 16, 2008 at 11:06 am
madhu.arda (9/16/2008)
To give exact picture.. There is a job running this stored procedure at every one hour between 2AM to 8PM. And initially I thought logbackup is failing but going through error log its seems full backup itself is failing. Plz clarify on this issue....Thank You
Hey,
First thing first...I would suggest you run a local full backup now, as your whole recovery effort is at risk.
Secondly, what is error message for full-backup?
Thanks,
Phillip Cox
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply