Memory Footprint in MemToLeave SQL Memory

ChrisAVWood

Hi,

I have asked this information to be included in the documentation but I am not sure if it has been included.

SQLBackup needs somewhere around 6Mb of contiguous memory in the MemToLeave SQL memory space to run. On a 32-bit server this can cause problems when you have CLR objects and linked servers using inprocess providers all fighting for the same memory.

I think that it is essential to know the exact memory footprint that each new version of SQLBackup uses as a site migrating from say 5.4 to 6.2. If the memory footprint increases even by a few KB then the chance of VAS memory problems increases without changing the MemToLeave setting with the -g startup parameter. We may have a number of backup jobs running at the same time because of the number of databases that we have so even a few extra KB per backup job puts pressure on the MemToLeave and could cause VDI 1010 errors.

Please include in both your new version announcements and supporting documentation the exact memory footprint so that an SQLBackup upgrade does not cause major memory problems.

Thanks

Chris

peter.peart

Hi Chris,

We have the knowledgebase document detailed on the below link:

http://www.red-gate.com/supportcenter/C ... wledgebase\SQL_Backup\KB200708000117.htm

May I ask, where else do you think this detailed? I can think of some places myself, however I don't want to influence your thought process ;-)

Pete

ChrisAVWood

Thanks Peter.

I have seen this before. Maybe I am confused here but it mentions:-

When SQL Backup performs a backup using the default settings and one file, it requires a block of free contiguous memory of about 6 MB.

It is this reference to about 6Mb that I am concerned about. Our systems could be running numerous SQLBackup jobs at the same time putting pressure on the MemToLeave memory so just an increase of a few KB per job would start to give errors by just upgrading from one version to another.

Is there an actual number that can be put into this knowledge base article on a release by release basis or is it always the same?

Thanks

Chris

Eddie D

Hi Chris

The amount of continuous memory in the MemToLeave SQL memory space has remain unchanged from SQL Backup V4 through to the current latest Version 6.3.

The amount of continuous memory is currently 6x MAXTRANSFERSIZE value x the number of threads.

By default the MAXTRANSFERSIZE value is approximately 1MB.

So for a single threaded backup, 6MB of continuous memory has been required.

However the has been one change that is easily missed when configuring new backup tasks using either the backup wizard or scheduled backup jobs wizard.

In SQL backup V4 and V5, the option to perform a multi-threaded backup is turned off by default.

In SQL Backup V6, the option to perform a multi-threaded backup is turned on by default and the value set is determined by the number and type of CPU installed on the machine.

If you had existing backup jobs, after upgrading to SQL Backup V6 these jobs thread settings will remain unchanged. So if your job uses a single thread it will continue to use a single thread.

I hope this helps.

Many Thanks
Eddie

ChrisAVWood

Great piece of info Eddie. We run SQL Agent jobs created originally from the GUI using the Script tab. So if my 5.4 script dores not have a THREADCOUNT = parameter and it gets ungraded to 6.? and is run on a mutli core will it default to multiple threads and then need a bigger memory footprint?

Thanks

Chris

petey

By default, SQL Backup only uses 1 backup device. It only uses multiple backup devices when the THREADCOUNT option is explicitly used, or when multiple DISK options are used.

So if your original script does not use the THREADCOUNT option, nor multiple DISK options, then it will not automatically use multiple backup devices regardless of the version of SQL Backup that is used.

Thanks.

ChrisAVWood

Petey,

Just what I needed to know. I would have added a THREADCOUNT = 1 to the T-SQL statement in the job just to make sure.

Thanks

Chris

Eddie D

Hi Chris

If you wish to create your backup files using a single thread, do not need to specify the THREADCOUNT = keyword in your existing backup scripts. When SQL Backup peforms the backup and the THREADCOUNT keyword is not specified it will create or perform the backup using a single thread.

In my previous post to this topic, I wanted to make you aware of a slight change that could easily be missed when using either the Backup or Schedule Backup Job Wizards via the SQL Backup GUI. In that SQL Backup V6 the option to multiple threads is now enabled by default where in previous versions it was disabled.

Many Thanks
Eddie