5

I have a large database on sql server 2005. After shrinking the data file, the database is still very big: around 9 GB.

Here is some data from sp_spaceused:

exec [dbname].dbo.sp_spaceused

reserved     |data kb     |index_size   |unused    |
10221976 KB  |849240 KB   |9367456 KB   |5280 KB   |

can I use

DBCC SHRINKFILE(logic_name_dat, 1000(Mb))

without losing user data on that database?

Иван Вълчев
  • 51
  • 4
  • 2
    Your reserved and unused space is roughly 5 MB. Don't shrink your file. That is almost no free space, you will trigger auto growth quickly (provided you have it set to autogrow). As @marc_s pointed out, shrinking data files introduces an extremely large amount of index fragmentation. – Thomas Stringer Sep 17 '12 at 16:53
  • 1
    According to that output, you have lots of space used by indexes. Having indexes 10 times the size of the real data is a bit much, don't you think? Why don't you check them first? See what indexes you need and only then proceed with the cleanup, if there's any need at all. – Marian Sep 18 '12 at 20:23

1 Answers1

4

Yes, you will not lose any user data by shrinking the file. However, the file grows so that the growth operation isn't happening all the time and so by shrinking it you're likely choosing physical size over performance. Furthermore, if it's growing too fast, you should change how it grows and by how much to control it - but it grows at a percentage of the actual size of the file by default.

Remember, all you're doing is asking it to take up less disk space with the buffer portion of the file, it is not going to shrink past what it's able to using the physical data as its limit.

Mike Perrenoud
  • 381
  • 1
  • 5
  • 3
    +1, no data lost: "DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB." – Tim Lehner Sep 17 '12 at 15:07
  • @TimLehner, thanks for the additional example! – Mike Perrenoud Sep 17 '12 at 15:09
  • 4
    Also: shrinking the database will copy around a lot of data pages and in a very stupid way, so that in the end, your indexes will all be severely fragmented. If you do shrink your database file on a regular basis - make sure to rebuild all indexes after you do this! – marc_s Sep 17 '12 at 15:54
  • 4
    @marc_s +1 to your comment. The OP shouldn't want to introduce so much fragmentation for very little to no gain with space. – Thomas Stringer Sep 17 '12 at 16:54