SQL Server – Restarting When Shrinking Database Files

sql server

If frequently shrinking database files is it smart to restart SQL Server/ or service ?
I currently work in a company (Mainly analytics databases , using SIMPLE Recovery Model)where we have to shrink the data files sometime to reclaim space on the disk (Although i did say to my manager shrinking data file is not a smart think to do. Shrinking the log are fine).
I suggested to my manager that we restart the server as we have been shrinking the data file a lot this month.
Can you share your thought and expertise on the subject.

Best Answer

As you mentioned yourself, often shrinking is a bad idea, and should only be done on special occasions. Eg. You just archived half of your db, and you're really in need of some extra space.

Rebooting the server will NOT solve your performance issues, what this will do is give you a bunch of new issues.

  • SQL likes to keep data pages (Buffer pool) and plan caches in memory, once you reboot the server these are gone, which means all queries will need to get new plans compiledand new data filled in buffer pool from disk, which of course is a lot slower.

  • A lot of the DMVs that return server information will have lost all useful information

  • Rebooting causes downtime, can you afford to have your services go down?

What you should do: Allocate enough space for your data files so they don't need to grow, and keep some extra free space in case of unexpected data growth.
If there's not enough disk space to use for your normal day to day use you could:

  • Increase disk size
  • Take a look at unused/duplicate indexes, perhaps some space can be freed up here
  • Take a look at your data compression
  • Archive unneeded data
Related Question