Can frequent shrink files cause DB corruption

Question

Post reply

Can frequent shrink files cause DB corruption

VastSQL

SSC-Dedicated

Points: 36069
More actions
June 29, 2014 at 2:35 am

#396978

Hi Experts,
Will shrink files on database files cause corruption by any chance??
TIA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

No. http://www.sqlskills.com/blogs/paul/search-engine-qa-26-myths-around-causing-corruption[/url]

You should ask why/when shrink.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

e4d4 (6/29/2014)
No. http://www.sqlskills.com/blogs/paul/search-engine-qa-26-myths-around-causing-corruption[/url]
You should ask why/when shrink.

With the problems they recently had with online index rebuilds corrupting data in 2012 because of a supposed "regression in code" (that means they accidently promoted older code), I don't believe anyone on similar subjects anymore. I'll say that the processes are usually five 9's but nothing is perfect and nothing is impossible.

I agree that shrink should be used only to repair a massive problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 3

Shrinks are no more likely to cause corruption than any other operation that writes data that we don't currently know causes corruption (i.e. they're just like an UPDATE statement today, or SQL 2012/2014 online index rebuilds a few weeks ago... or today if you've applied those patches!).

They are, however, a really, really bad practice.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/[/url]

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

Nadrek (6/30/2014)
Shrinks are no more likely to cause corruption than any other operation that writes data that we don't currently know causes corruption (i.e. they're just like an UPDATE statement today, or SQL 2012/2014 online index rebuilds a few weeks ago... or today if you've applied those patches!).
They are, however, a really, really bad practice.
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/[/url]

Heh... yep... they used to say that about online index rebuilds, as well. I do agree that shrinks are generally a bad idea, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 5

Jeff Moden (6/30/2014)
Heh... yep... they used to say that about online index rebuilds, as well. I do agree that shrinks are generally a bad idea, though.

I was never so happy to have stayed with offline index rebuilds before - fixed maintenance windows are a wonderful thing.

In general, though, I would suggest that a good theoretical rule of thumb is "If there's no published bug [like said online index rebuild data loss bug], then any operation that performs any disk write is more or less as likely to cause corruption as any other operation, page written for page written." Thus, databasewide operations are the most likely to cause corruption, followed by having to do math on amount of data written.

I would also suggest that a good practical rule of thumb is "Keep an eye out for published bugs, make sure your databases have PAGE_VERIFY CHECKSUM set (and if you're updating an old database to this, you do need to rewrite all pages, so it's one of the few times rebuilding every index isn't pointless), make sure you use CHECKSUM on your backups, make sure your backups work and restore well, run your CHECKDB's often and check the results, and make sure you run consistency checks on your RAID sets regularly... and then don't worry about it anymore - do whatever operations you need to do without concerning yourself with the incremental risk, because corruption bugs are a crapshoot - it's whoever screwed what up that hasn't been found yet."

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

Nadrek (7/1/2014)
Jeff Moden (6/30/2014)
Heh... yep... they used to say that about online index rebuilds, as well. I do agree that shrinks are generally a bad idea, though.
...make sure your backups work and restore well, run your CHECKDB's often and check the results, and make sure you run consistency checks on your RAID sets regularly... and then don't worry about it anymore ...

+ 1 gazillion... especially on the restores!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)