1

I have a SQL Server 2008 instance and one table is quite large (log data) so I delete old logs. Now I start to read articles on this problem, here is one.

As I see it there is no solution! If I shrink the database and then rebuild the index, the database will be about the same size as before. If I don't rebuild the index, the database will shrink, but performance will be low, which is not an option.

So shrinking for production databases seems not to be an option. Is there any solution? Does deleting from the database make any sense? How do big companies delete log data from their databases?

Improve this question
0

2 Answers 2

Reset to default
3

Why do you need to shrink the file? Rebuilding the CI after a massive delete (without shrinking the file) will help make those pages available for future data. Shrinking the file just means it has to grow again when you add more data. So there was no benefit to shrinking it in the meantime - ooh, you freed up some space, but only temporarily.

Another thing you should look at is table partitioning (depending on SQL Server edition). It can make disposing of old data much, much easier.

Improve this answer
1

Big companies never shrink the database files - and the logs reuse space after a log backup.

Basically, shrink / grow is for low performance. You want performance, you initialize files for a specific planned size, then resize when you need more manually. You MAY use "emergency autogrow" but you do NOT shrink. What for? I mean, seriously. I ahve a small (400 gb right now) database and it lives in DEDICATED DISCS to get performance - I would gain nothing from shrinking. In fact, I am expanding it once I get the next set of discs (on shipment) and I Do that manually.

Same with logs. They live on their own dedicated discs. They are preinitialized for a size I am comfortable with. I do not shrink them - what for?

Improve this answer

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