patternsqlMinor
Shrink or no Shrink dilemma
Viewed 0 times
shrinkdilemmastackoverflow
Problem
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.
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.
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 KBWhat 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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#91229, answer score: 2
Revisions (0)
No revisions yet.