May 4, 2015 at 2:45 am
Charles Kincaid (4/30/2015)
SQL server is a bit vulnerable during a shrink operation. ... If something goes bad during the shrink then a restore is likely to be needed.
Not really. No more than in large table operations like index rebuilds. Shrink is a fully logged operations (with lots of small transactions) and if anything causes the shrink to fail or it's stopped, if can roll back to the beginning of it's last transaction.
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
May 4, 2015 at 12:06 pm
Thanks Gail. I had missed that point.
Toby, the O/S fragmentation does not have anything to do with the shrink. Actually it does not even apply to index fragmentation. I'm am slightly less concerned about a blanket rebuild of indexes as I am about blanket shrinks.
ATBCharles Kincaid
May 4, 2015 at 1:24 pm
Charles Kincaid (5/4/2015)
Thanks Gail. I had missed that point.Toby, the O/S fragmentation does not have anything to do with the shrink. Actually it does not even apply to index fragmentation. I'm am slightly less concerned about a blanket rebuild of indexes as I am about blanket shrinks.
A perpetual shrink will cause a perpetual need for the database to regrow. Those growths will cause on-disk fragmentation as the new file segments are being placed on whichever portion of the disk becomes available at that time.
On disk fragmentation can be an issue for performance as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 5, 2015 at 12:22 am
Maybe a clarification is necessary.
WHY is shrinking the database weekly so bad?
Firstly, the database is likely to grow again throughout the week. Growing a database file can be an expensive operation, especially without instant file initialisation, so we try to avoid that by not pre-shrinking the database.
It will also cause filesystem fragmentation. Filesystem fragmentation (with mechanical rather than SSD drives) will cause slower I/Os. If you're lucky enough to be working in a pure SSD environment, then you care less about fragmentation as there's no disk head to move.
The only parts of the database that will be fragmented are the most recent extensions, but in an OLTP environment this is likely to be where the reads are happening.
Finally, the shrinking isn't going to have much effect in this case, because the next operation that happens on the maintenance plan is the re-index. That will regrow the database significantly as it re-indexes clustered indexes. This destroys the point of shrinking the database, as well as slows down the index rebuild.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply