Shrink Database Files and how to make MSDE not eat up all of your memory.
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.
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*
Eric
You Rock – I did not know that about MSDE
Thanks