Memory pool gets filled up

yrakesh · September 1, 2006 6:49AM

Hello,

I have installed SQL Back in our Production server and scheduled a job for the same through stored procedure. After 3-4 days the memory of the systems gradually increased and ultimately got crashed. This incident has happened twice and does not re-occur once red-gate is uninstalled from the Server.

I have even tried with upgraded version, the situation is same.
Please help me out as there is no use this software in spite of having licensed version!

Regards,
Rakesh

petey · September 1, 2006 10:32AM

Could you pls provide me the following information so that I may try to reproduce the error:

> hardware
- single-processor/multi-processor
- amount of ram

> OS
- version and platform (32-bit/64-bit/Itanium)
- service pack level
- clustered?

> SQL Server
- version and platform (32-bit/64-bit/Itanium)
- service pack level

> SQL Backup details
- pls run sqlbackup from within Query Analyser or SSMS, and tell me the field name that's displayed (SQL Backup DLL v xxx)
- versions of SQL Backup installed so far on the server

> backup details
- number of backups ran before encountering error
- backup script
- frequency of backup schedule
- number of databases backed up per backup run
- size of databases backed up

> error information
- how did you determine that it was a memory error?
- was the memory increase linked to the SQL Server process, or the SQL Backup service process?
- what are the errors recorded in the SQL Server log files
- any errors recorded in the Windows event log

Thanks.

yrakesh · September 13, 2006 12:51PM

Hi Peter,
sorry for delay.. Please find my comments..

Could you pls provide me the following information so that I may try to reproduce the error:

> hardware
- single-processo
- amount of ram (2,095,508 KB RAM)

> OS

Microsoft Windows 2000 service Pack 4
- clustered - YEs

> SQL Server

SQL Server 2000 Ver 8.0

> SQL Backup details

SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005

> backup details

- number of backups ran before encountering error
=> for 5 days..
- backup script
CREATE PROCEDURE spRG_ALL_DB_BACKUP
AS
set nocount on
declare @datestamp varchar(30)
declare @databasename varchar(200)
declare @backuppath varchar(500)
declare @filename varchar(500)
declare @backupstring varchar(1000)
declare @failcount int
declare @failflag int
declare @hresult int /* store OLE automation result */
declare @ole_FileSys int /* filesystemobject */
declare @ole_Folder int /* folder object */
declare @ole_Source varchar (255)
declare @ole_Description varchar (255)
declare @foldername varchar (255)

create table #tmpsqberrors (failcode int)

set @backuppath = 'D:\Backup\'

declare dbs cursor for
select NAME from sysdatabases
where name not in ('tempdb')
order by dbid

open dbs
fetch dbs into @databasename
@fetch_status != -1 begin

set @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)
--set @foldername=@backuppath + @databasename
set @filename = @backuppath + '\' + @databasename + '_db_(full)_' + @datestamp + '.sqb'

set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ') Full'',MAILTO_ONERROR=''BBHATT@PRIMUSTEL.CO.UK'', ERASEFILES = 2, COMPRESSION = 2" -E'

exec @failflag=master..sqlbackup @backupstring
if @failflag=0
BEGIN
insert into #tmpsqberrors (failcode) VALUES (1)
END
fetch dbs into @databasename

END

-- CLEANUP:

close dbs
deallocate dbs

select @failflag=COUNT(*) FROM #tmpsqberrors
if @failflag > 0 begin
RAISERROR ('Backup failed to complete successfully for all databases.', 16, 1)
end
-- EXEC sp_OADestroy @ole_FileSys
DROP TABLE #tmpsqberrors
set nocount off

RETURN 0
GO

- frequency of backup schedule
=> Daily once
- number of databases backed up per backup run
=> 37
- size of databases backed up
=> 85 GB (std SQL backup)

> error information
- how did you determine that it was a memory error?
=> System went down twice. Got to know the error from event monitor stating that Memory pool is full.

- was the memory increase linked to the SQL Server process, or the SQL Backup service process?
=> AS soon as I disabled/ un-install red-gate SQL backup. Server is stable.

- what are the errors recorded in the SQL Server log files
- any errors recorded in the Windows event log

Can't record any errors as I have to restart the system for first time. Second time when same issue occured, system autmatically shut down.

I hope this info is suffice to investigate the prob

petey · September 13, 2006 1:44PM

Thanks for the info. Some additional clarification:

- are you using Windows 2000 Advanced Server?

- what is the service pack level applied to the SQL Server 2000 instance?

- is the 85 GB the size of the databases, or the size of the compressed backup files?

Thanks.

yrakesh · September 14, 2006 10:06AM

Petty,
Please find comments against u'r query

- are you using Windows 2000 Advanced Server?
=> NO

- what is the service pack level applied to the SQL Server 2000 instance?

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

- is the 85 GB the size of the databases, or the size of the compressed backup files?

=> that is normal backup size not the complessed one.

Please can u see why I'm facing this issue as I was running u'r s/w in Prodcution server!!

Thanks.

yrakesh · September 29, 2006 5:47AM

hi Pete,

Can you please update? Let me know if u need more info..

thanks,

Brian Donahue · October 5, 2006 12:47PM

Peter, did you notice that the SQL Backup version is 3.2?

We have done a number of updates to v3.2 that may help, but if you can, please consider upgrading to version 4.6.

If you have a currently valid support contract, the upgrade is free. You'll get a whole lot of new features as well as a more mature product.

yrakesh · October 6, 2006 5:28AM

Hi Brian,

Thanks for the reply. I did upgraded to the latest version as well. Still the situation is same. Memory pool gradually increases until system just shuts down.
Also we do use mirroring software to secondry server. will it have any affect? :roll:

thanks,
Rakesh

petey · October 6, 2006 5:37AM

How are you monitoring the memory pool?

I have set up a server that is similiar to your configuration:

Windows 2000 Server with Service Pack 4
SQL Server 2000 with Service Pack 3a
SQL Backup 3.2
1 Gig RAM

I have used your script to backup all databases on this server. Largest database is 5.5 Gig in size.

The script has ran for over 3 hours now, and has backed up 234 Gig of data. Memory counters as per the Windows performance manager do not show any increasing trend.

Another point:
Since you are using the ERASEFILES option, are there a lot of 'other' files in the backup folder?

Thanks.

Brian Donahue · October 6, 2006 5:40AM

It was worth a try. I think it's over to Pete on this one.

What 'mirroring' software are you using?

Also, you may want to make absolutely sure you've purged the old extended stored procedure from mermory because sometimes upgrading from 3.x to 4.x doesn't flush out the stored procedure. Try these queries:

exec sp_helpextendedproc 'sqlbackup' /* should return xp_sqlbackup.dll w/o file path. If there is a full path ie c:\program files\red gate\sql backup, then run the next two commands */
exec sp_dropextendedproc 'sqlbackup'
exec sp_addextendedproc 'sqlbackup','xp_sqlbackup.dll'
/*Finally, run this: */
DBCC xp_sqlbackup &#40;FREE&#41;

yrakesh · October 6, 2006 9:42AM

Thanks for your solutions Guys.

We use Life-keeper Mirroring software to keep the data up-to date in secondary server. Unfortunately this server being in live production (billing engine runs in it) I can't take risk unless concrete solution is provided.

I would really appreciate if you can give me step by step process to debug this issue which would be beneficial for both of us to drill down further.

Best regards,
Rakesh

Brian Donahue · October 6, 2006 10:00AM

Hello Rakesh,

We can't provide a 'concrete' solution because we don't know for sure what the problem is.

Nothing in the above post would normally cause unavailability on the SQL Server.

Memory pool gets filled up

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk