question

Sagar Bhargava avatar image
Sagar Bhargava asked

Shrink data files on DEV server

Hi, Recently we had an activity to clean a big table (350 GB) from a database in our Dev environment. Post that, we want to reclaim the space so that other databases on the server can utilize that. I have been trying to shrink the data file but it keep on running for over 2 hours when trying to reduce the space by 50 MB. I have tried the below: 1. Rebuild the indexes on the table. 2. Ran dbcc updateusage 3. DBCC Shrinkfile(filename, target size) 4. Backup the database name FileSizeMB SpaceUsedMB FreeSpaceMB MDF1 394504.06 40246.63 354257.44 LOGFile 154128 1136.41 152991.59 MDF2 26532 26514.06 17.94 Is there another way this can be done. Thanks.
shrink-databasestorage
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Depending on the size of the other tables, it might be quicker to transfer the remaining data into a new database...
0 Likes 0 ·

1 Answer

· Write an Answer
Wilfred van Dijk avatar image
Wilfred van Dijk answered
If you rebuild the index, this will cost some space in your datafiles, unless you add the option SORT_IN_TEMPDB, This might explain the amount of free space in your datafile. If you do a rebuild on your indexes and do a shrink afterwards the rebuild is useless. this because the shrink introduces fragmentation Don't know why you ran DBCC UPDATEUSAGE. This is only needed once, after an upgrade from SQL2005 (or lower). This fixes some bugs in statistical metadata. I assume you wanted to update the statistics? This is done automatically if you rebuild the index. I suggest to implement the index maintenance from Ola Hallengren, see http://ola.hallengren.com
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.