Shut-Up and Smile

ASP.NET 2.0 comes with a great set of built-in membership features. When you enable personalization, via Website | ASP.NET Configuration in Visual Studio 2005, by default, the user information is stored in a Microsoft SQL Server Express database in the Data folder of your Web site. The database is made up of two files: ASPNETDB.MDF and aspnetdb_log.ldf with a default size of 10 MB used for the database. Now this is all fine and dandy if you're going to build a real web site. But when you're doing demos all the time, it can be a pita to have a ton of 10 MB databases lying around.

Now, if you have SQL Server 2005 Management Studio installed, and then it's as easy as a context menu option to make it smaller. However if you don't, well, then it's off to SQLCMD.

Here's a little script I came up with (naturally you'll need to specify the path to the database you wish to squish) for my good buddy Ken Getz who prompted this whole thing:

SET NOCOUNT ON;
GO

USE MASTER;
GO

CREATE DATABASE SHRINK_ASPNETDB
ON
(FILENAME = 'C:\TEST\DBSIZE\APP_DATA\ASPNETDB.MDF'),
(FILENAME = 'C:\TEST\DBSIZE\APP_DATA\aspnetdb_log.ldf')
FOR ATTACH;
GO

USE SHRINK_ASPNETDB;
GO

DECLARE @FileName nvarchar(100);
SELECT @FileName = FILE_NAME(1);
DBCC SHRINKFILE (@FileName, 1);
GO

USE MASTER;
GO

sp_detach_db SHRINK_ASPNETDB;
GO

You need to change the path for both the MDF and LDF files. Once you've done it, just run the following from a command prompt (this assumes you name the file Shrink.sql):
 
sqlcmd -E -S .\SQLExpress -i Shrink.sql
 
You should be good to go. You're MDF should now only need about 1.5 MB instead. Perfect for demos.
 
End of line.