Could 'Maximum Server Memory' be too low for large batched delete with captured output?

I'm trying to improve the performance of a SQL server database backup program that will create a new database to hold one years worth of report records, delete report records out of the main database, and capture the delete output and use it to insert the deleted records into the new 'year named' database. Essentially I am doing a move of the records from one main database, to separate databases for each year. On my test database, that is about 2.5 GB worth of report records, this process is taking around 3:00-3:40 minutes to complete. I have the 'Maximum Server Memory' set to the max, which is 2147483647 MB. As I understand it, SQL server uses whatever memory is available up to that maximum limit, and will release any memory that it does not need if there is memory pressure from other applications that require more memory. On the client SQL servers, we have the 'Maximum Server Memory' set to 2048 MB, because we thought that since SQL server was holding on to too much memory when it was set to the max value. The client databases are usually much larger than my test database, ranging anywhere from 30 GB to over 60 GB in a couple cases. Running the backup utility on the databases on these servers is taking at *least* 10 hours, and we usually tell the client that it could take anywhere from 18-24 hours for the utility to run to completion and create the 'year named' backup databases. We constantly see PAGEIOLATCH_EX and PAGEIOLATCH_SH waits, which I think means that either the disk subsystem is having problems with the IO required, or there is not enough memory available for the operation. Would limiting the 'Maximum Server Memory' like we have cause excessive latch wait times like we are seeing? We don't currently disable the indexes on the Reports table, which I'm sure is causing some slowness, but I'm not sure how much. We actually create an additional index over the date field, because that is the used in the where clause of our delete, in an attempt to speed up finding the appropriate records to delete. Which I now think might not be necessary, because the records should already be in date order, because that is how they are created daily. The process is like this: 1. Drop the full text index on the report table. 2. Create a non-clustered index on the report table over the date field and ID. 3. For each year of records selected 1. Create a new database to move the records to. 2. Create the reports table that has the same fields as the base database. 3. Perform the delete of that years records in 500 record batches, capturing the output and inserting it in the 'year named' database. 4. Create the full text index on the new database. 5. Shrink the main database. (This is because of disk space issues. Most of the clients disks can't finish the operation without running out of space unless we shrink the main database to release the space) 4. Re-enable the full text index on the main reports database. I'll try to include some samples of the script that I think might be relevant to the question. Delete with output clause = DECLARE @Rows INT SET @Rows = 1 WHILE (@Rows > 0) BEGIN DELETE TOP (500) FROM [" + opDbName + @"].[dbo].[Reports] OUTPUT DELETED.[ID] ,DELETED.[datafile] ,DELETED.[report] ,DELETED.[date] ,DELETED.[istext] ,DELETED.[version] ,DELETED.[dateimported] ,DELETED.[archivelocation] INTO [" + newDbName + @"].[dbo].[Reports]([ID],[datafile],[report],[date],[istext],[version],[dateimported],[archivelocation]) WHERE [Date] BETWEEN '" + i + @"-01-01' AND '" + i + @"-12-31' SET @Rows = @@ROWCOUNT END GO Create year database = IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'" + newDbName + @"') BEGIN PRINT('Creating database " + newDbName + @" ...') CREATE DATABASE [" + newDbName + @"] ON PRIMARY ( NAME = N'" + newDbName + @"', FILENAME = N'" + dataDir + newDbName + @".mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'" + newDbName + @"_log', FILENAME = N'" + logsDir + newDbName + @"_log.ldf', MAXSIZE = 2048GB , FILEGROWTH = 10%) END GO Alter year database = I don't even really know what all these settings are, but they come from generating the script to create the base database from above, and I just apply it to the newly created database to keep them the same. PRINT('Altering database " + newDbName + @" ...') GO ALTER DATABASE [" + newDbName + @"] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [" + newDbName + @"].[dbo].[sp_fulltext_database] @action = 'enable' END GO ALTER DATABASE [" + newDbName + @"] SET ANSI_NULL_DEFAULT OFF ALTER DATABASE [" + newDbName + @"] SET ANSI_NULLS OFF ALTER DATABASE [" + newDbName + @"] SET ANSI_PADDING OFF ALTER DATABASE [" + newDbName + @"] SET ANSI_WARNINGS OFF ALTER DATABASE [" + newDbName + @"] SET ARITHABORT OFF ALTER DATABASE [" + newDbName + @"] SET AUTO_CLOSE OFF ALTER DATABASE [" + newDbName + @"] SET AUTO_CREATE_STATISTICS ON ALTER DATABASE [" + newDbName + @"] SET AUTO_SHRINK ON ALTER DATABASE [" + newDbName + @"] SET AUTO_UPDATE_STATISTICS ON ALTER DATABASE [" + newDbName + @"] SET CURSOR_CLOSE_ON_COMMIT OFF ALTER DATABASE [" + newDbName + @"] SET CURSOR_DEFAULT GLOBAL ALTER DATABASE [" + newDbName + @"] SET CONCAT_NULL_YIELDS_NULL OFF ALTER DATABASE [" + newDbName + @"] SET NUMERIC_ROUNDABORT OFF ALTER DATABASE [" + newDbName + @"] SET QUOTED_IDENTIFIER OFF ALTER DATABASE [" + newDbName + @"] SET RECURSIVE_TRIGGERS OFF ALTER DATABASE [" + newDbName + @"] SET DISABLE_BROKER ALTER DATABASE [" + newDbName + @"] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ALTER DATABASE [" + newDbName + @"] SET DATE_CORRELATION_OPTIMIZATION OFF ALTER DATABASE [" + newDbName + @"] SET TRUSTWORTHY OFF ALTER DATABASE [" + newDbName + @"] SET ALLOW_SNAPSHOT_ISOLATION OFF ALTER DATABASE [" + newDbName + @"] SET PARAMETERIZATION SIMPLE ALTER DATABASE [" + newDbName + @"] SET READ_COMMITTED_SNAPSHOT OFF ALTER DATABASE [" + newDbName + @"] SET HONOR_BROKER_PRIORITY OFF ALTER DATABASE [" + newDbName + @"] SET RECOVERY SIMPLE ALTER DATABASE [" + newDbName + @"] SET MULTI_USER ALTER DATABASE [" + newDbName + @"] SET PAGE_VERIFY CHECKSUM ALTER DATABASE [" + newDbName + @"] SET DB_CHAINING OFF ALTER DATABASE [" + newDbName + @"] SET READ_WRITE GO