CODEWITHSUNDEEP
sql-server
Teller
Teller

Reputation: 765

Is there a way to compact a SQL2000/2005 MDF file?

I deleted millions of rows of old data from a production SQL database recently, and it didn't seem to shrink the size of the .MDF file much. We have a finite amount of disk space.

I am wondering if there is anything else I can do to "tighten" the file (like something analogous to Access' Compact and Repair function)?

Upvotes: 5

Views: 4296

Answers (4)

HDSSNET
HDSSNET

Reputation: 127

This worked for me and shrank my log files by a thousand.

  1. Using the SQL Server Manager.
  2. Right Click on the database in question.
  3. Choose Properties, then the options tab.
  4. Change the Recovery Model to Simple From Full.

If you need it in full mode switch it back after it shrinks. That's it!

Upvotes: 0

JosephStyons
JosephStyons

Reputation: 58685

If large log files are the problem, this may help:

backup log MY_DATABASE WITH TRUNCATE_ONLY;

Then right click on MY_DATABASE and choose All Tasks->Shrink Database as teller suggested.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

DBCC SHRINKDATABASE etc. - read up on transaction logs and backups in the Books Online

Upvotes: 5

user21576
user21576

Reputation: 126

Use the Shrink File option in Sql Server Management Studio Right-click on Database > Tasks > Shrink > Database (or Files)

Upvotes: 7

Related Questions