0

I have a database of 1.5 TB, which had only 200 GB free space before.Due to performance issues, business decided to reduce 1 month data retention policy to 1 week.Due to this 3 weeks data was deleted and now it has 700 GB free space.

The 500 GB free space increased due to data delete is no more required, and there no chance to grow data again to occupy this 500 GB space.

I am planning to shrink the database(using shrink file command ) and run index optimization process to rebuild indexes, if fragmentation is greater than 30%. The released space wont be used by other systems, however can be used for log file and temp db growth efficiently in near future.

Is there any benefits on performance by reducing the size of database, other than space release ?

Previous questions related to shrinking(in stack exchange) does not properly answer to my queries.

Thanks in advance for your kind reply !!!

Improve this question
  • Have you looked in more details what exactly causes the performance issues? – vonPryz May 17 '18 at 8:07
  • von, I want to know how shrinking would harm/benefit on performance of system, though there are other alternatives for tuning. – Madhusudan May 18 '18 at 3:51
  • I'd still recommend you to find out the root cause for performance issues. Should the culprit were to be, say, poorly written queries or seriously lacking hardware, decreasing data is going to help only so much. – vonPryz May 18 '18 at 5:42
  • I appreciate your sugggestions von. – Madhusudan May 18 '18 at 6:46
6

If you are maintaining your indexes as you say, there will be no performance benefit.

Performance will actually get worse from shrinking, and your next index rebuild job will likely have significantly more work to do than usual.

The most common reason people think that the shrinking of a database works to increase performance is because the engine has to read fewer pages for queries, however by shrinking you're not actually freeing up any pages, you're just shifting them around in the database, leading to fragmentation and degraded performance. You do reduce the overall number of pages for the database, but not the number of used pages holding data.

Another point is it's likely to take an extremely long time to shrink that database, due to the shrink process being a single-threaded process. It will likely also cause performance issues during the shrink, and potentially blocking.

Check out When is it OK to shrink a database DBA SE question

I would highly recommend against shrinking.

One option for you might be to transfer the data into a fresh, empty copy of the same database, then delete the old one - this is likely to be quicker and tidier.

Improve this answer
1

Shrinking a database is not considered a good option.

We might think that shrinking the database will reduce the size of our database. However, shrinking a database comes with a cost, namely increased fragmentation.

The resulting fragmentation reduces the database performance. So, to reduce fragmentation, you will go with index rebuilding which will again increase the size of your database. So, overall no gain.

You can go and try reorganizing the index instead of rebuilding it. This might help, but there is no guarantee.

However, you can shrink your log file to release some space. But make sure that you have taken the log backup before shrinking. Also, first check the recovery model and proceed accordingly.

Improve this answer

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.