MS SQL Server - insufficient system memory during Tbackup (maintenanceplan)

Author

insufficient system memory during Tbackup (maintenanceplan)

Michel van Marion

2006-03-24, 7:26 am

I'm using maintenance plans to make backups of our databases. I've created 2
type of maintenace plans, one for simple recovery model databases and one for
full recovery model databases.
My plan for full recovery databases includes a hourly Transactionlogfile
backup. This all works wel for about 3 weeks and then the transactionlogfile
backups fail with the following message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 701: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
insufficient system memory to run this query.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating
abnormally.

this is the output of xp_msver:
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 524288 8.00.2039
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2000.080.2039.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © 1988-2004 Microsoft Corp. All rights
reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark
of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 133627904 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 15 0000000f
18 ProcessorType 586 PROCESSOR_INTEL_PENT
IUM
19 PhysicalMemory 2047 2047 (2146926592)
20 Product ID NULL NULL

i've googled for many hours but with no result.
can someone help me with this issue?

Thanks in advance.
Michel van Marion

John Bell

2006-03-24, 9:24 am

Hi

Did you check
http://msdn.microsoft.com/library/d...26r5
.asp

John

"Michel van Marion" wrote:

> I'm using maintenance plans to make backups of our databases. I've created 2
> type of maintenace plans, one for simple recovery model databases and one for
> full recovery model databases.
> My plan for full recovery databases includes a hourly Transactionlogfile
> backup. This all works wel for about 3 weeks and then the transactionlogfile
> backups fail with the following message:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)]
> Error 701: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> insufficient system memory to run this query.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating
> abnormally.
>
> this is the output of xp_msver:
> 1 ProductName NULL Microsoft SQL Server
> 2 ProductVersion 524288 8.00.2039
> 3 Language 1033 English (United States)
> 4 Platform NULL NT INTEL X86
> 5 Comments NULL NT INTEL X86
> 6 CompanyName NULL Microsoft Corporation
> 7 FileDescription NULL SQL Server Windows NT
> 8 FileVersion NULL 2000.080.2039.00
> 9 InternalName NULL SQLSERVR
> 10 LegalCopyright NULL © 1988-2004 Microsoft Corp. All rights
> reserved.
> 11 LegalTrademarks NULL Microsoft® is a registered trademark
> of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
> 12 OriginalFilename NULL SQLSERVR.EXE
> 13 PrivateBuild NULL NULL
> 14 SpecialBuild 133627904 NULL
> 15 WindowsVersion 248381957 5.2 (3790)
> 16 ProcessorCount 4 4
> 17 ProcessorActiveMask 15 0000000f
> 18 ProcessorType 586 PROCESSOR_INTEL_PENT
IUM
> 19 PhysicalMemory 2047 2047 (2146926592)
> 20 Product ID NULL NULL
>
> i've googled for many hours but with no result.
> can someone help me with this issue?
>
> Thanks in advance.
> Michel van Marion
>

Michel van Marion

2006-03-31, 3:23 am

Hi John,

I checked the article you mentioned. The databases are all in full recovery
model state.

michel

John Bell

2006-03-31, 3:23 am

Hi michel

You may want to check out the following:

Can you manually run the job sucessfully?
Have you checked any disc space or quotas?
Do you re-initialise the backup device?
Are jobs overlapping?
Does setting changing max server memory have any effect?

John

"Michel van Marion" wrote:

> Hi John,
>
> I checked the article you mentioned. The databases are all in full recovery
> model state.
>
>
> michel

Michel van Marion

2006-03-31, 3:23 am

Hi john,

first i want to explain that when the jobs fail, i have to restart sqlserver
and my jobs are able to run succesfully again.
At this time my jobs are running fine every hour, but i know that within 3
weeks the jobs are failling again some day.

anwsers:
1) when my job is failing and i'try to make a transactionalLog backup
manually this also fails.
2) we have no restrictions set on disk quota or memory qouta to any users on
our sqlserver server.
3) i've no idea what you mean by re-initialise backup device. i'm not using
backup devices in my maintenance plan. the properies of the maintenance plan
(translogbck page) are : backup to disk ,... use directory, ....create sub
dir for each.....
4) we have 4 maintenance plans on this server (one plan for full location 1,
one for simple location 1, and the same for location 2)
-- full backups are daily made on 19.15 (lboth ocations)
-- Tlogbackups are made every hour on whole hour ( 12.00, 13.00, etc)
5) memory sqlserver setting: dynamicly (4 mb - 2047 mb).
i've tried to use a fixed memory setting for sqlserver (1024 mb max), but
this was not fixing my issue , the problem wasn't fixed.

sorry if my english writing correct, but i hope you understand my anwsers.

thx for helping me
Michel

John Bell

2006-03-31, 7:24 am

Hi

The part about initialising the backup device would occur if you used the
INIT keyword in a backup command. As a maintenance plan creates a new backup
file with the dbname_log_yyyymmddh
hmm.BAK filename you should be ok.

Have you checked for memory leaks and see if there is anything else running
on the server is eating up the memory?

Failing that I can only suggest that you write your own jobs and see if that
has any success.

John

"Michel van Marion" wrote:

> Hi john,
>
> first i want to explain that when the jobs fail, i have to restart sqlserver
> and my jobs are able to run succesfully again.
> At this time my jobs are running fine every hour, but i know that within 3
> weeks the jobs are failling again some day.
>
> anwsers:
> 1) when my job is failing and i'try to make a transactionalLog backup
> manually this also fails.
> 2) we have no restrictions set on disk quota or memory qouta to any users on
> our sqlserver server.
> 3) i've no idea what you mean by re-initialise backup device. i'm not using
> backup devices in my maintenance plan. the properies of the maintenance plan
> (translogbck page) are : backup to disk ,... use directory, ....create sub
> dir for each.....
> 4) we have 4 maintenance plans on this server (one plan for full location 1,
> one for simple location 1, and the same for location 2)
> -- full backups are daily made on 19.15 (lboth ocations)
> -- Tlogbackups are made every hour on whole hour ( 12.00, 13.00, etc)
> 5) memory sqlserver setting: dynamicly (4 mb - 2047 mb).
> i've tried to use a fixed memory setting for sqlserver (1024 mb max), but
> this was not fixing my issue , the problem wasn't fixed.
>
> sorry if my english writing correct, but i hope you understand my anwsers.
>
> thx for helping me
> Michel
>

Michel van Marion

2006-03-31, 7:24 am

John,

the INIT keyword is used in the backup statement, so that ok.

When the problem occurd sqlserver.exe was claiming 1,6 Gb of RAM. The
sqlserver server is dedicated to sqlserver, so nothing else is using this
machine.

thanx for your help
Michel

John Bell

2006-03-31, 7:24 am

Hi Michel

Apart from writing you own jobs you may want to see if upgrading to version
2187 will help. Failing that the only other thing I can suggest is to add
more memory.

John

"Michel van Marion" wrote:

> John,
>
> the INIT keyword is used in the backup statement, so that ok.
>
> When the problem occurd sqlserver.exe was claiming 1,6 Gb of RAM. The
> sqlserver server is dedicated to sqlserver, so nothing else is using this
> machine.
>
> thanx for your help
> Michel

Sponsored Links