How do I shrink my SQL Server Database?

I have a Database nearly 1.9Gb Database in size, and MSDE2000 does not allow DBs that exceed 2.0Gb

I need to shrink this DB (and many others like this at various client locations).

I have found and deleted many 100's of 1000's of records which are considered unneeded: these records account for a large percentage of some of the main (largest) tables in the Database. Therefore it's reasonable to assume much space should now be retrievable.

So now I need to shrink the DB to account for the missing records.

• I execute DBCC ShrinkDatabase('MyDB')...... No effect.
• I have tried the various shrink facilities provided in MSSMS.... Still no effect.
• I have backed up the database and restored it... Still no effect.

Still 1.9Gb

Why?

Whatever procedure I eventually find needs to be replayable on a client machine with access to nothing other than OSql or similar.

Solution 1:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

Solution 2:

This may seem bizarre, but it's worked for me and I have written a C# program to automate this.

Step 1: Truncate the transaction log (Back up only the transaction log, turning on the option to remove inactive transactions)

Step 2: Run a database shrink, moving all the pages to the start of the files

Step 3: Truncate the transaction log again, as step 2 adds log entries

Step 4: Run a database shrink again.

My stripped down code, which uses the SQL DMO library, is as follows:

SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_NoTruncate);
SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default);