Red Gate forums :: View topic - SQL Backup fails and causes virtual memory issues
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

SQL Backup fails and causes virtual memory issues

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2, 3  Next
Author Message
tungshan



Joined: 31 Jan 2007
Posts: 6

PostPosted: Wed Jan 31, 2007 1:37 am    Post subject: SQL Backup fails and causes virtual memory issues Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Wed Jan 31, 2007 4:41 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
tungshan



Joined: 31 Jan 2007
Posts: 6

PostPosted: Wed Jan 31, 2007 4:46 am    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Wed Jan 31, 2007 5:14 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
tungshan



Joined: 31 Jan 2007
Posts: 6

PostPosted: Wed Jan 31, 2007 5:17 am    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Wed Jan 31, 2007 5:21 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
tungshan



Joined: 31 Jan 2007
Posts: 6

PostPosted: Wed Jan 31, 2007 5:50 am    Post subject: Re: Reply with quote

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?
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Wed Jan 31, 2007 6:10 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
tungshan



Joined: 31 Jan 2007
Posts: 6

PostPosted: Wed Jan 31, 2007 5:34 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Thu Feb 01, 2007 3:03 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
tungshan



Joined: 31 Jan 2007
Posts: 6

PostPosted: Thu Feb 01, 2007 3:45 am    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Thu Feb 01, 2007 4:23 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
bill.wehnert



Joined: 20 Feb 2007
Posts: 22

PostPosted: Mon Mar 26, 2007 7:56 pm    Post subject: VDI 1010 failure as well Reply with quote

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:
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Tue Mar 27, 2007 3:59 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
bill.wehnert



Joined: 20 Feb 2007
Posts: 22

PostPosted: Wed Mar 28, 2007 2:59 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2, 3  Next
Page 1 of 3

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group