1

Closed

Memory usage ballons on @optype reindex or checkdb

description

I've been using ExpressMaint on a remote (dedicated) server for a few weeks. I've discovered that the scripts i'm running (or the way i'm running them) is causing the memory used by sqlserver.exe (as reported in Task Manager) to explode from 30k to 200k.
 
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)
 
On a patched to current WinServer03 box.
 
To run daily backup unattended I have a Scheduled Tasks job defined as:
 
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S.\ -i"c:\foo\DailyUsersFull.sql"
 
Below is my full script showing i'm running backup, check and reindex daily against all userdbs by firing 3 separate 'exec expressmaint'.
 
If I run only the @optype = DB everything works fine. Adding either the reindex or dbcheck will cause the condition. Memory usage goes to 200+K _and stays there until the server's restarted. I've glanced at the logs produced by the runs and see no error conditions recorded anywhere.
 
exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'c:\foo\Daily',
@reportfldr = 'c:\foo_reports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 2,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1
 
exec expressmaint
@database = 'ALL_USER',
@optype = 'CHECKDB',
@backupfldr = 'c:\foo\Daily',
@reportfldr = 'c:\foo_reports_checks',
@verify = 1,
@rptretainunit = 'months',
@rptretainval = 9,
@report = 1
 
exec expressmaint
@database = 'ALL_USER',
@optype = 'REINDEX',
@backupfldr = 'c:\foo\Daily',
@reportfldr = 'c:\foo_reports_reindex',
@verify = 1,
@rptretainunit = 'days',
@rptretainval = 9,
@report = 1
 
 
Expressmaint utility, Logged on to SQL Server
Starting CheckDB on Sep 5 2007 4:26AM
 
 
[1] Database GlassinePrePurge: Check Data and Index Linkage...
CheckDB completed in 0 hour(s) 0 min(s) 2 second(s)
 
 
[2] Database GlassineDemo: Check Data and Index Linkage...
CheckDB completed in 0 hour(s) 0 min(s) 2 second(s)
 
 
[3] Database Glassine2: Check Data and Index Linkage...
CheckDB completed in 0 hour(s) 0 min(s) 3 second(s)
 
 
[4] Database ELMAH: Check Data and Index Linkage...
CheckDB completed in 0 hour(s) 0 min(s) 1 second(s)
 
 
[5] Database Glassine: Check Data and Index Linkage...
CheckDB completed in 0 hour(s) 0 min(s) 4 second(s)
 
 
[6] Delete Old Report Files...
0 file(s) deleted.
 
 
Expressmaint processing finished at Sep 5 2007 4:27AM (Return Code : 0)
Closed Sep 5, 2007 at 3:15 PM by jaspersmith
Expected behaviour. See comments on issue

comments

jaspersmith wrote Sep 5, 2007 at 3:13 PM

This is expected behaviour of SQL Server. When performing a database reindex or integrity check, database pages need to be loaded into the buffer pool which causes an increase in memory as reported by Task Manager. SQL Server will only release memory back to the operating system if the server is under memory pressure (backups use a different type of buffer for transferring pages from the database files to the backup device). Please see KB article 321363 (http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363).