How do I shrink my SQL Server Database?

sql-server database shrink msde2000
Rory Becker picture Rory Becker · Jan 13, 2009 · Viewed 238.3k times · Source

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.

Answer

Petr Stedina picture Petr Stedina · Aug 18, 2009 
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More