5

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.

2

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.

  • Nick, Thanks for your comment.I have a 4 hour window and no issue with time at all.It is a production server and I just want to do the right thing. – user42148 Feb 5 '15 at 21:33
  • Hello, completely understand where you're coming from. Given that you have an outage window, it seems like the simplest scenario would be to shrink - provided you do the necessary maintenance afterwards. – Nick Burns Feb 5 '15 at 21:39
  • 2
    Shrink is an online operation, so no need to do it during an outage. I would capture the log counters to see how much you're actually using at peak times - such as rebuilds, max concurrent times, etc. After you're armed with that information you can make an informed decision. Overall, though, shrink isn't bad if used correctly and not as a crutch or knee-jerk responses. – Sean Gallardy Feb 5 '15 at 22:25
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?

  • Rob, Can you tell how to do this "you could simply move the data into a new database that's correctly sized " I like this idea but need the proper implementation. The space will be sufficient plus we can expand the LUN if required. Thanks. – user42148 Feb 5 '15 at 23:32
  • In an existing database, you create a new filegroup with appropriately sized files. Then use CREATE INDEX ... WITH DROP_EXISTING = ON for all your clustered and non-clustered indexes. If you have heaps, I would suggest recreating them with new names and then renaming them. Then when your filegroup is empty, you can remove it. Or you could shrink that almost-empty filegroup and rebuild indexes for those objects still there. – Rob Farley Feb 5 '15 at 23:41
  • But you could also do it using a completely new database, sucking the data across as you need, potentially using replication, keeping it in sync until all the data you need is in place and you can do some database renamed so that your systems start using the new database. Replication can be done for particular articles, so that you move the data across, rather than starting with a full database restore (which will be the same size as what you currently have). – Rob Farley Feb 5 '15 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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