Backup successful (with warnings)

KristoferWKristoferW Posts: 10 Bronze 2
edited February 10, 2011 9:30AM in SQL Backup Previous Versions
Hi,

can someone please help me with these type of errors?

Sometimes, the backup job fails with an error message, but seems to write the backup file nevertheless. The Outcome in the "Activity History" is "Successfull (with warnings)

The logfile shows the following errors:
10.02.2011 11:30:00: Backing up model (transaction log) on MYDB instance to: 
10.02.2011 11:30:00:   E:\SQLData\Backup\LOG\LOG_MYDB_model_20110210_113000.sqb
 
10.02.2011 11:30:00: BACKUP LOG [model]  TO VIRTUAL_DEVICE = 'SQLBACKUP_80FBED0A-F8F4-45F5-985C-5F9AFE18A3AA', VIRTUAL_DEVICE = 'SQLBACKUP_80FBED0A-F8F4-45F5-985C-5F9AFE18A3AA01' WITH BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = 
 N'Database (model), 10.02.2011 11:30:00', DESCRIPTION = N'Backup on 10.02.2011 11:30:00  Server: MYDB\MYDB  Database: model', FORMAT
 
10.02.2011 11:30:00: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. SQL Backup required 12.582.912 bytes of free SQL Server memory, which was not available.  You can reduce the memory requirements 
 by reducing the number of threads used in the backup.  VDI error: An abort request is preventing anything except termination actions.
10.02.2011 11:30:00: SQL error 3013: BACKUP LOG wird fehlerbedingt beendet.
10.02.2011 11:30:00: SQL error 18210: BackupVirtualDeviceSet::Initialize: Fehler bei Request large buffers beim Sicherungsmedium 'SQLBACKUP_80FBED0A-F8F4-45F5-985C-5F9AFE18A3AA'. Betriebssystemfehler = 0x8007000e(Für diesen Vorgang ist nicht genügend 
 Speicher verfügbar.).
10.02.2011 11:30:00: 
10.02.2011 11:30:00: Memory profile
10.02.2011 11:30:00: Type             Maximum     Minimum     Average     Blk count   Total      
10.02.2011 11:30:00: ---------------- ----------- ----------- ----------- ----------- -----------
10.02.2011 11:30:00: Commit           263565312   4096        701701      2768        1942310912 
10.02.2011 11:30:00: Reserve          8077312     4096        279154      628         175308800  
10.02.2011 11:30:00: Free             7397376     4096        82089       363         29798400   
10.02.2011 11:30:00: Private          263565312   4096        770885      2564        1976549376 
10.02.2011 11:30:00: Mapped           1323008     4096        123892      174         21557248   
10.02.2011 11:30:00: Image            24858624    4096        181630      658         119513088  
10.02.2011 11:30:00: 
--------------------------------------------------------------------------------
10.02.2011 11:30:03: Deleting old backup file: E:\SQLData\Backup\LOG\LOG_MYDB_model_20110208_113000.sqb

So, the log tells me to reduce the number of threads used in the backup, but the number is already at 1!
What should i do?

Thanks for your help..
Kristofer
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    The issue SQL Backup is running into is the lack of free memory to perform a backup using the default settings. When it encounters such a situation, it automatically reattempts the backup again using smaller memory blocks, which is why the backup succeeded on the next attempt.

    If you would like to prevent SQL Backup from raising such warnings, you can use the MAXTRANSFERSIZE option to specify a smaller buffer size value (in bytes) to use on the first backup attempt. The default value is 1048576 (1 MB). You could try using 512 Kb e.g.
    EXEC master..sqlbackup '-sql "BACKUP ... WITH MAXTRANSFERSIZE = 524288..." '
    The core issue is that your free SQL Server memory is running low (~29 MB) and is heavily fragmented (363 blocks). We have an extended stored procedure that could help you in determining what's causing the fragmentation. If you run the following:
    EXEC master..sqbmemory
    you will see an output of SQL Server's memory space similar to that in the SQL Backup log. The line of interest is the 'Free' memory element e.g.

    9/23/2010 3:40:04 AM: Free 6729728 4096 108183 250 27045888

    What you could do is to run sqbmemory periodically e.g. every 30 minutes, and monitor the 'Free' memory values. Do you see a trend in the values? Do the values fluctuate wildly during a certain period of time, or when a certain process is running in the database?

    Here's a script you could run in Management Studio, that will log the output of sqbmemory into a temporary table every 30 minutes:
    CREATE TABLE ##sqbmemorylog (EntryTimestamp datetime, Type varchar(8), Minimum numeric(25, 0), Maximum numeric(25, 0), Average numeric(25, 0), BlkCount numeric(25, 0), Total numeric(25, 0))

WHILE 1 = 1
BEGIN
	INSERT INTO ##sqbmemorylog (Type, Minimum, Maximum, Average, BlkCount, Total) EXEC master..sqbmemory
	UPDATE ##sqbmemorylog SET EntryTimestamp = GETDATE() WHERE EntryTimestamp IS NULL
	WAITFOR DELAY '00:30:00'
END
    Hopefully, this helps in determining the cause of the fragmentation.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • KristoferWKristoferW Posts: 10 Bronze 2
    Hi Peter,

    many thanks for your answer.
    I will try that with the stored procedure.


    Kind regards
    Kristofer
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    There's also the possibility that the memory usage pattern is usual for your setup/environment, and using SQL Backup just puts more demands on free memory space.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.