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.