0

I've been having issues finding a real answer to this question as the response is always "Don't do this, it's a bad idea, that space was obviously needed and it will just get used up again in quick order." But that response doesn't apply to me - I have a copy of the production database that I am using solely for developmental purposes - my read/write activites to be limited to a small amount of the database, but I will need to constantly restore the database to overwrite my changes, so I want to reduce the size of the database - I can achieve this by deleting the data in a handful of tables.

Here is what I have done so far:

  1. Made sure database was in simple recovery mode
  2. Ran DELETE FROM TABLE_NAME queries
  3. Tasks > Shrink > Files > File Type: Log, Release Unused Space - this step was successful and reduced the .ldf to its initial minimum size.
  4. Tasks > Shrink > Files > File Type: Data, Release Unused Space - this step did not do anything. It "executed" in half a millisecond and the .mdf is just as big as it was before running the DELETE queries...

Any input appreciated.

Improve this question
0

1 Answer 1

Reset to default
0

The first step is worthless because delete operations are completely logged. Use truncate instead if you want to empty a table.

About the task 4 you have to reorganize pages because the release pages free only pages ad the end of the mdf file. It will takes a lot.

I suggest you add task 5: rebuild the indexes because shrinking the data file causes the indexes to fragment.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.