Memory pool gets filled up

yrakeshyrakesh Posts: 10
edited October 6, 2006 10:00AM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • hi Pete,

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

    thanks,
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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 (FREE)
    
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
Sign In or Register to comment.