February 27, 2014 at 6:21 am
Hello. When doing a shrink database - Under "Available free space" - What percentage % free would you say is in the danger zone? What are the ranges you think are acceptable and at what % will we start to see performance issues.
Also... is there documentation on this anywhere?
Thank you!
February 27, 2014 at 6:25 am
Well, 'when doing a shrink' should be a rare event in the first place.
I'm happy to have enough free space in the DB to accommodate 6 months of expected data growth. No problems with plenty of free space.
You need at least enough free space to hold the largest index (for a rebuild), otherwise the DB will grow, but since I'll almost never shrink that's only something I worry about in terms of the DB growing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 6:30 am
Well, I am not even close to a SQL server expert... trying to learn. Our SQL guy is out and we have been having problems with our one database that he is constantly "helping" us with. He had me do a shrink database and it says 8% available and told me the shrink usually takes about 30 minutes each time.
February 27, 2014 at 6:37 am
Well, if you're having a problem with a database, stop shrinking it. Making problems worse is usually a bad idea. Shrink doesn't help matters, this isn't MS Access.
Shrink should never be done regularly, only when there's been some archiving or massive removal of old data and there's space in the DB that won't be needed for months or longer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 6:39 am
ummm, ok thanks
February 27, 2014 at 6:51 am
OK, maybe try to ask the question another way. Is 10GB (8%) of available free space OK / acceptable?
February 27, 2014 at 7:02 am
You need at least enough free space to hold the largest index (for a rebuild), otherwise the DB will grow
Other than that, OK/acceptable in terms of what?
If you have a DB that grows 10GB a month, that's probably OK. If it grows 20GB a day, probably not. But that's just about the DB growing. If it needs free space and doesn't have, it will grow (unless autogrow is disabled) and if it can't grow you'll get out of space errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2014 at 8:21 am
You can go to zero free space if you want. There's not magic "acceptable" answer. The issue is, why do you need to shrink the database? Was there a broken process that input way too much data that you've now deleted and you want to reclaim disk space in a one-time process? Cool, I'd shrink it down to about what you say, around 10% free or so (and there are lots of caveats around that, but it's an OK starting point). But if you're shrinking over and over again, I'd suggest you need to stop, as Gail says, and figure out why you're growing so much and either address that issue, or leave the database at a larger size.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply