0

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.

  • 5
    None of the practices you described are a good idea: shrinking data or log files shall have performance impact. If your files grow frequently, why don't you leave them with the appropriate size? And what benefits do you expect to have with the restart of the server/service? – Ronaldo Nov 7 at 15:26
  • 1
    Brent's blog has references to some very detailed articles on why you shouldn't be doing what you are doing. Paul Randall, Kim Tripp, Mike Walsh, Gail Shaw, and Tom LaRock all touch on different things. After you read those, then check this blog from Andy Mallon. – scsimon Nov 7 at 16:34
  • 2
    This is a legitimate set of questions, as evidenced by the numerous blog posts listed by @scsimon. That's why SE/SO exist. Why are people voting this question down? If you're tired of this issue (which is understandable), then either you can ignore the posted questions, or patiently continue answer them. We're all on the same path, just different parts of that path. I'm somewhere in the middle, and I get nervous asking a question here. – Conrad S. Nov 7 at 18:39
  • 1
    @ConradS. that's not completely the point I don't think. There are already numerous answers to this particular question accessible using the search function. – George.Palacios Nov 8 at 9:01
1

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
  • I agree with all the above, but if you really have to shrink the mdf files you'd better rebuild your indexes afterwards too. After shrinking the datafiles the indexes will get very fragmented. – VCarstein Nov 8 at 9:31

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.