Shrink or no Shrink dilemma

Question

I have databases that I am tasked to move two databases DBName1 and DBName2 to SSD and I only have limited space on the SSD to fit these databases.I do not want to shrink log or data files for the obvious reason and Brent or Paul might go crazy. I noticed the database is not growing much and it is using fraction of what it is originally allocated. The initial size for log files are the current size 41GB and 147GB respectively. When I checked the DBCC SQLPERF(logspace) , I found 41017.3 MB log size and 0.4339632 % log space use % and status 0 147474MB log size and 0.08165617 log space use % and status 0.

Database Name   Log Size (MB)   Log Space Used (%)  Status
 DBNAme1            41017.3      0.4339632           0


 DBName2          147474        0.08165617           0

Database files(Sp_Spaceused)

database_name   database_size   unallocated space
DBName1         126294.31 MB    67443.73 MB

reserved    data    index_size  unused
18261272 KB 8347376 KB  9677480 KB  236416 KB

database_name   database_size   unallocated space
DBName2        271075.31 MB      115074.44 MB

reserved    data    index_size  unused
8731200 KB  5634520 KB  12976 KB    3083704 KB

What do you think I should do. I need to be able to utilize the SSD. Is it worth shrinking or do you think of other way of reclaiming the free space? I know shrinking is the last resort.

Answer 1

At the risk of making an unpopular statement, I think that shrinking might be a good option for you here. Let me explain...

Shrinking a database data file will almost certainly cause fragmentation. This is critical in a production database, where you likely cannot afford the time to defrag the database after the shrink.

However, it sounds like you have some time up your sleeves. Perhaps you could restore the databases onto a non-production server where you will have ample time and resources to shrink and defrag the databases. Then you could migrate these databases to your SSD.

Of course this takes time. You might need to keep your log backups on production so you can roll forward changes, or possibly setup something like replication to keep your data in sync.

Will be interested in what other people think, and what you ultimately choose to do.

Answer 2

For starters, there's no issue with shrinking log files, only database files.

You don't need to shrink the database - you could simply move the data into a new database that's correctly sized, and then you can get rid of the old files. No fragmentation introduced. I'm pretty sure Paul writes about this.

I'd be more concerned about whether the smaller space will be sufficient. If it's needed that much space in the past, will it need it again in the future, but be on volumes that don't have free space?