SQL Backup fails and causes virtual memory issues

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

SQL Backup fails and causes virtual memory issues

Postby tungshan » Wed Jan 31, 2007 1:37 am

I am currently using SQL Backup 4 for my backups on SQL Server 2005. After like 10 days, my backups fails and I have to reboot the server. Here are snippets of the SQL server log:

2007-01-19 17:15:00.59 spid565 Error: 18210, Severity: 16, State: 1.
2007-01-19 17:15:00.59 spid565 BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_C9D0A658-E6F1-4A03-8CC7-AC8466A5B202'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).
2007-01-19 17:15:00.59 spid565 Error: 18210, Severity: 16, State: 1.
2007-01-19 17:15:00.59 spid565 BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_C9D0A658-E6F1-4A03-8CC7-AC8466A5B202'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).
2007-01-19 17:15:00.59 Backup Error: 3041, Severity: 16, State: 1.
...
2007-01-20 02:10:42.14 spid570 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 327680
...
2007-01-20 02:10:42.15 spid570 Error: 701, Severity: 17, State: 123.
2007-01-20 02:10:42.15 spid570 There is insufficient system memory to run this query.
2007-01-20 02:10:42.17 spid570 Error: 701, Severity: 17, State: 123.
2007-01-20 02:10:42.17 spid570 There is insufficient system memory to run this query.
2007-01-20 02:10:42.20 spid570 Error: 701, Severity: 17, State: 123.
2007-01-20 02:10:42.20 spid570 There is insufficient system memory to run this query.

For these log backups, I'm also using the log shipping function from SQL Backup. I face the exact same errors on the 2nd DB machine with restoring the logs.

Has anyone faced similar problems? Is this a SQL Backup issue or a SQL Server issue?

Thanks.
tungshan
 
Posts: 6
Joined: Wed Jan 31, 2007 1:28 am

Postby petey » Wed Jan 31, 2007 4:41 am

It appears that SQL Server has run out of large memory blocks to allocate to the backup/restore process. If you run the sqbmemory extended stored procedure on both servers, what is the result that's displayed e.g.

EXEC master..sqbmemory
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby tungshan » Wed Jan 31, 2007 4:46 am

petey wrote:It appears that SQL Server has run out of large memory blocks to allocate to the backup/restore process. If you run the sqbmemory extended stored procedure on both servers, what is the result that's displayed e.g.

EXEC master..sqbmemory


DB #1
Type Minimum Maximum Average Blk count Total
Commit 4096 1073414144 2117388 1178 2494283776
Reserve 4096 158007296 3504834 116 406560768
Free 4096 110034944 743191 431 320315392
Private 4096 1073414144 4936024 571 2818469888
Mapped 4096 1060864 50991 334 17031168
Image 4096 24711168 167978 389 65343488

DB #2
Type Minimum Maximum Average Blk count Total
Commit 4096 60817408 209310 859 179798016
Reserve 4096 1012596736 23315597 116 2704609280
Free 4096 131465216 1544736 218 336752640
Private 4096 1012596736 6043590 465 2810269696
Mapped 4096 1060864 71982 122 8781824
Image 4096 24723456 168442 388 65355776
tungshan
 
Posts: 6
Joined: Wed Jan 31, 2007 1:28 am

Postby petey » Wed Jan 31, 2007 5:14 am

It appears that there is adequate free memory at this point. Do the backups and restores currently work?
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby tungshan » Wed Jan 31, 2007 5:17 am

petey wrote:It appears that there is adequate free memory at this point. Do the backups and restores currently work?


Yeah, they work and then over some period time I get the errors above. It seems like it degrades over time and it renders my production DB server useless. This has happened twice over the past month.
tungshan
 
Posts: 6
Joined: Wed Jan 31, 2007 1:28 am

Postby petey » Wed Jan 31, 2007 5:21 am

I would suggest monitoring the memory usage pattern over time, with and without SQL Backup running. The numbers to look out for are the 'free' memory values.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby tungshan » Wed Jan 31, 2007 5:50 am

petey wrote:I would suggest monitoring the memory usage pattern over time, with and without SQL Backup running. The numbers to look out for are the 'free' memory values.


How do I prevent this error from happening? Would changing the MAXTRANSFERSIZE or MAXDATABLOCK fix this error?
tungshan
 
Posts: 6
Joined: Wed Jan 31, 2007 1:28 am

Postby petey » Wed Jan 31, 2007 6:10 am

When a backup fails due to the lack of a large enough configuous block of free memory, SQL Backup automatically retries the backup using smaller MAXTRANSFERSIZE values, until it hits the minimum of 65536 bytes. Even this does not seem to work in your case.

Could you pls check the SQL Backup log file for the failed backup, to see if the backups were re-attempted, and if so, how many times?

Also, are you backing up to multiple files?

Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby tungshan » Wed Jan 31, 2007 5:34 pm

petey wrote:When a backup fails due to the lack of a large enough configuous block of free memory, SQL Backup automatically retries the backup using smaller MAXTRANSFERSIZE values, until it hits the minimum of 65536 bytes. Even this does not seem to work in your case.

Could you pls check the SQL Backup log file for the failed backup, to see if the backups were re-attempted, and if so, how many times?

Also, are you backing up to multiple files?

Thanks.



I just took a look at the SQL Backup log files and it looks like it gradually comes to a halt. For example, when the problem first occurs, the backup fails, but it succeeds on the 1st retry. It continues like this for like an hour. Then the backup starts failing on the 1st retry and will succeed on the 2nd retry. So and so forth until it fails for all 4 retries.

And we are not backing up to multiple files.
tungshan
 
Posts: 6
Joined: Wed Jan 31, 2007 1:28 am

Postby petey » Thu Feb 01, 2007 3:03 am

Could you pls post the values for 'Free' memory for the first and last failures? Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby tungshan » Thu Feb 01, 2007 3:45 am

petey wrote:Could you pls post the values for 'Free' memory for the first and last failures? Thanks.


For the 1st time the backup needs to retry, the Free memory is:
Type Maximum Minimum Average Blk count Total
Free 5550080 4096 28891 4166 120360960

Then for the last time where backup is failing and we need to reboot the machine:
Type Maximum Minimum Average Blk count Total
1st retry:
Free 196608 4096 27162 4413 119869440
4th retry:
Free 196608 4096 27162 4413 119869440
tungshan
 
Posts: 6
Joined: Wed Jan 31, 2007 1:28 am

Postby petey » Thu Feb 01, 2007 4:23 am

On the first failure, the largest contiguous block of memory (~5.3 MB) was adequate to perform backups using smaller MAXTRANSFERSIZE values, but by the last failure, the largest contiguous block of memory (~192 Kb) was inadequate to run any backups.

The free memory space of your SQL Server process is severely fragmented, as indicated by the large number of free memory blocks.

Can you test if this is caused by SQL Backup, by not running SQL Backup for a week or so, and run sqbmemory periodically to monitor the memory allocation patterns?

Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

VDI 1010 failure as well

Postby bill.wehnert » Mon Mar 26, 2007 7:56 pm

I'm trying to backup a single database on my server (here's the command):

master..sqlbackup N'-SQL \"BACKUP DATABASE [LLReports] TO DISK = ''E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', COMPRESSION = 1\"'

The SQL Server instance is running, the account I was logged in with as well as the account that the service uses are both Admin accounts.

I've tried this about five times now and I get the same thing each time.

I've seen a few posts that this may have to do with contiguous free memory and there not being enough of it. That doesn't sound like a good solution to me. This is a production server and It's under load at all times (yes I know I need more hardware - that isn't going to happen at the moment) - so this backup needs to run in the environment I have.

A SQL Server backup ran just fine. I saw another request asking that SQL Backup be turned off for a week or so to see if that is the problem. That's a nice idea, but I'm dependent now on the compression and encryption I get from this product, I don't want to be without it for a week (I know - whine whine whine <G>).


Here is the log file that was generated:

SQL Backup log file
3/26/2007 1:47:57 PM: Backing up LLReports (full database) to:
E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb

3/26/2007 1:47:57 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', COMPRESSION = 1

3/26/2007 1:48:12 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
3/26/2007 1:48:12 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
3/26/2007 1:48:12 PM:
3/26/2007 1:48:12 PM: Memory profile
3/26/2007 1:48:12 PM: Type Maximum Minimum Average Blk count Total
3/26/2007 1:48:12 PM: ---------------- ----------- ----------- ----------- ----------- -----------
3/26/2007 1:48:12 PM: Commit 891027456 4096 1704659 1053 1795006464
3/26/2007 1:48:12 PM: Reserve 1073676288 4096 6375593 181 1153982464
3/26/2007 1:48:12 PM: Free 164048896 4096 1163123 234 272171008
3/26/2007 1:48:12 PM: Private 1073676288 4096 4549053 639 2906845184
3/26/2007 1:48:12 PM: Mapped 1536000 4096 178806 52 9297920
3/26/2007 1:48:12 PM: Image 5808128 4096 60489 543 32845824
3/26/2007 1:48:12 PM:
3/26/2007 1:48:12 PM: Warning 300: Backup failed. Retry attempt: 1
3/26/2007 1:48:14 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

3/26/2007 1:48:29 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
3/26/2007 1:48:29 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
3/26/2007 1:48:29 PM:
3/26/2007 1:48:30 PM: Memory profile
3/26/2007 1:48:30 PM: Type Maximum Minimum Average Blk count Total
3/26/2007 1:48:30 PM: ---------------- ----------- ----------- ----------- ----------- -----------
3/26/2007 1:48:30 PM: Commit 891027456 4096 1704659 1053 1795006464
3/26/2007 1:48:30 PM: Reserve 1073676288 4096 6375593 181 1153982464
3/26/2007 1:48:30 PM: Free 164048896 4096 1163123 234 272171008
3/26/2007 1:48:30 PM: Private 1073676288 4096 4549053 639 2906845184
3/26/2007 1:48:30 PM: Mapped 1536000 4096 178806 52 9297920
3/26/2007 1:48:30 PM: Image 5808128 4096 60489 543 32845824
3/26/2007 1:48:30 PM:
3/26/2007 1:48:30 PM: Warning 300: Backup failed. Retry attempt: 2
3/26/2007 1:48:32 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

3/26/2007 1:48:47 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
3/26/2007 1:48:47 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
3/26/2007 1:48:47 PM:
3/26/2007 1:48:47 PM: Memory profile
3/26/2007 1:48:47 PM: Type Maximum Minimum Average Blk count Total
3/26/2007 1:48:47 PM: ---------------- ----------- ----------- ----------- ----------- -----------
3/26/2007 1:48:47 PM: Commit 891027456 4096 1699886 1056 1795080192
3/26/2007 1:48:47 PM: Reserve 1073676288 4096 6343061 182 1154437120
3/26/2007 1:48:47 PM: Free 164048896 4096 1170873 232 271642624
3/26/2007 1:48:47 PM: Private 1073676288 4096 4521576 643 2907373568
3/26/2007 1:48:47 PM: Mapped 1536000 4096 178806 52 9297920
3/26/2007 1:48:47 PM: Image 5808128 4096 60489 543 32845824
3/26/2007 1:48:47 PM:
3/26/2007 1:48:47 PM: Warning 300: Backup failed. Retry attempt: 3
3/26/2007 1:48:49 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

3/26/2007 1:49:04 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
3/26/2007 1:49:04 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
3/26/2007 1:49:04 PM:
3/26/2007 1:49:05 PM: Memory profile
3/26/2007 1:49:05 PM: Type Maximum Minimum Average Blk count Total
3/26/2007 1:49:05 PM: ---------------- ----------- ----------- ----------- ----------- -----------
3/26/2007 1:49:05 PM: Commit 891027456 4096 1699886 1056 1795080192
3/26/2007 1:49:05 PM: Reserve 1073676288 4096 6343061 182 1154437120
3/26/2007 1:49:05 PM: Free 164048896 4096 1170873 232 271642624
3/26/2007 1:49:05 PM: Private 1073676288 4096 4521576 643 2907373568
3/26/2007 1:49:05 PM: Mapped 1536000 4096 178806 52 9297920
3/26/2007 1:49:05 PM: Image 5808128 4096 60489 543 32845824
3/26/2007 1:49:05 PM:
3/26/2007 1:49:05 PM: Warning 300: Backup failed. Retry attempt: 4
3/26/2007 1:49:07 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\\Backups\\MSSQL\\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

3/26/2007 1:49:22 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
3/26/2007 1:49:22 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
3/26/2007 1:49:22 PM:
3/26/2007 1:49:22 PM: Memory profile
3/26/2007 1:49:22 PM: Type Maximum Minimum Average Blk count Total
3/26/2007 1:49:22 PM: ---------------- ----------- ----------- ----------- ----------- -----------
3/26/2007 1:49:22 PM: Commit 891027456 4096 1696797 1058 1795211264
3/26/2007 1:49:22 PM: Reserve 1073676288 4096 6343061 182 1154437120
3/26/2007 1:49:22 PM: Free 164048896 4096 1170308 232 271511552
3/26/2007 1:49:22 PM: Private 1073676288 4096 4507759 645 2907504640
3/26/2007 1:49:22 PM: Mapped 1536000 4096 178806 52 9297920
3/26/2007 1:49:22 PM: Image 5808128 4096 60489 543 32845824
3/26/2007 1:49:22 PM:
bill.wehnert
 
Posts: 22
Joined: Tue Feb 20, 2007 4:20 pm

Postby petey » Tue Mar 27, 2007 3:59 am

The error is occcuring at a point when SQL Backup issues a BACKUP command to SQL Server. That command doesn't seem to have been picked up by SQL Server. Could you pls run Profiler and check if the BACKUP command was issued?

Start Profiler, filter on the application name SQBCoreService.exe. Then start a backup using SQL Backup. You should see 2 backup commands, one identical to the one you issued, and another similar to the first, but using VIRTUAL_DEVICE = ... instead of DISK = ...
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby bill.wehnert » Wed Mar 28, 2007 2:59 pm

petey wrote:Start Profiler, filter on the application name SQBCoreService.exe. Then start a backup using SQL Backup. You should see 2 backup commands, one identical to the one you issued, and another similar to the first, but using VIRTUAL_DEVICE = ... instead of DISK = ...


The command is getting to the server, however it just won't work. I tried stopping and restarting the SQBCoreService and that didn't work either.

Here is the command coming to the server:

BACKUP DATABASE [model] TO VIRTUAL_DEVICE = 'SQLBACKUP_1AF7352C-C01D-4FD1-8DBA-D5C93F3FE4D7' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 65536, NAME = N'Database (model), 3/28/2007 8:54:14 AM', DESCRIPTION = N'Backup on 3/28/2007 8:54:14 AM Server: PULSITY-SQL1 Database: model', FORMAT

I do see that it is testing to make sure the account it is using has rights to backup and it does. (This was working until a few days ago).

This is getting really upsetting - I haven't had a backup now since the 24th. That is NOT good. I'm going to try and manually run the SQLBackup program to see if it has any more luck backing up a db than the automated job.
bill.wehnert
 
Posts: 22
Joined: Tue Feb 20, 2007 4:20 pm

Next

Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest