This is mainly for my reference, but if anyone else needs it, it is here as well.
If you do not know, by default MSDE 2000 (which is installed with a lot of applications) will by default eat up all of your available memory (up to 2gb). This is a very serious problem if you install an application with MSDE on an Exchange server, or other similarly shared servers. You’re best off limiting the memory. Some perfect examples are, on-box Anti-Spam Software, McAfee EPO, Blackberry BES Server, etc. So be aware.
DBCC SHRINKFILE(pubs_log, 2)
BACKUP LOG pubs WITH TRUNCATE_ONLY
BACKUP LOG pubs TO pubslogbackup
DBCC SHRINKFILE(pubs_log,2)
And if/when that does not work, you can also use…
DBCC OPENTRAN (database_name) – to check for open transactions.
DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent) – This works when shrinkfile hasn’t been working.
And to limit MSDE
Save as a .SQL File
USE master
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE USE master
EXEC sp_configure ‘max server memory (MB)’, MaxServerMemory
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
osql -E -S ServerComputerName\msfw -i Path\setchecksqlmemory.sql
*Update*
In the event that you need to do it all by hand using OSQL as a trusted connection, perform the following – Set to change it to 64mb of memory
————————————-
OSQL -E
USE master
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
USE master
EXEC sp_configure ‘max server memory’, 64
RECONFIGURE WITH OVERRIDE
GO
USE master
EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO
————————————-
That will drop the memory usage from 2gb down to 64MB almost immediately.
Disclaimer: the ‘max server memory’ keeps replacing the first single quote incorrectly, so it may need to be typed manually. Working on correcting.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318
You may experience high memory usage on an ISA Server 2004-based computer that logs messages to an MSDE database
http://support.microsoft.com/?id=909636
OSQL Syntax
http://www.di-mgt.com.au/osqlUtility.htm
Christopher Kusek
*Updated this post to appear in 2007 as it’s been used more often than I did in September when I first posted this*
