insufficient system memory during Tbackup (maintenanceplan)

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 fo
r
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 ter
minating
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 right
s
reserved.
11     LegalTrademarks NULL           Microsoft® is a registered trademark
of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporatio
n
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

Hi

Did you check
http://msdn.microsoft.com/library/d...serr_1_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 transactionlogfi
le
> backups fail with the following message:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)]
> Error 701: [Microsoft][ODBC SQL Server Driver][SQL Server]Ther
e is
> insufficient system memory to run this query.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is t
erminating
> 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 rig
hts
> reserved.
> 11     LegalTrademarks NULL           Microsoft® is a registered trademar
k
> of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporat
ion
> 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
>

Hi John,

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


michel

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 recover
y
> model state.
>
>
> michel

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 pla
n
(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

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 sqlserv
er
> 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 usin
g
> backup devices in my maintenance plan.  the properies of the maintenance p
lan
> (translogbck page) are  : backup to disk ,... use directory, ....create su
b
> 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,

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

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