Post reply

Backups and memory question

  • July 15, 2016 at 7:25 am

    #151464

    All,

    We are running SQL Server 2000 Enterprise on a Server 2003 32 bit VM. Our backups are done through maintenance plans. Full backups at 7pm and transaction logs every 15 minutes.

    I've done some research on this try and solve the problem myself but would appreciate some help with some questions.

    The problem is that we are starting to get out of memory errors for the transaction logs and full backups. That doesn't surprise me because the database is getting bigger.

    To create more memory I enabled PAE on the server, set page locks in memory and enabled AWE. However I'm still getting the memory errors. There is 5GB set as max memory for SQL which should be more than enough. Are the backups constraint by another memory limit? I ran the reconfigure option after setting max memory.

    I found that a problem can be the number of buffers used. I took the backup command from the maintenance job and manually set buffercount =1 and the backups ran. However I'm not sure if that was just luck that there was less memory required for other things as the same time?

    Assuming that changing the buffer count is a solution then I need to move the backups to scripts instead of maintenance plans?

    I think the script here would be OK http://www.sqlservercentral.com/Forums/FindPost1450433.aspx?

    I would need to change the getdate to use format 120 and use BACKUP LOG for the transaction logs?

    I think the sequencing of the transaction logs would still be maintained? This is maintained by the backup log system not the maintenance plan?

    Could I use a Windows process to remove the old logs as I'm no longer using a maintenance plan?

    Sorry for the long post and all the questions. I'm trying to fix my own problem but would appreciate some help with the above. Any criticisms/ideas/'did you check ...' type questions are welcome.

    Thanks

    Andrew

  • July 15, 2016 at 7:33 am

    #1890307

    PAE and more memory won't help at all. The problem is that you're running 32-bit SQL 2000.

    Is upgrading to something released this decade on the cards?

    If not, then you'll need to add the -g parameter to the startup parameters to increase the MemToLeave reservation (the portion of the lower 2GB of addressable memory that's not used by the buffer pool). The default is 384MB

    Keep in mind that by doing this you're reducing the memory allocated to the plan cache, query memory reservations, etc, etc. Only the data cache can use the memory above 2GB.

    Changing the buffer size and count may help, but will likely have the effect of making the backups slower. Yes, you'll need to use scripts if you do that. You can still use a maintenance plan to do the file cleanup, if you wish.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 15, 2016 at 8:11 am

    #1890325

    Hello,

    Yes an upgrade is planned and, hopefully, soon.

    Thanks for your help. Now I understand.

    I thought that maybe the backup wasn't using the PAE and extra memory but I hadn't found some clear information.

    Thanks again

    Andrew

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply