DBCC ShrinkDatabase + Insert Query same time

Question

DBCC ShrinkDatabase + Insert Query same time

yuvipoy

SSChampion

Points: 10469
More actions
March 19, 2014 at 6:40 am

#284750

Hi,
This is general one not specific to Database version(2k/2k5/2k8)
DBCC ShrinkDatabase + Insert Query same time
While shrink operation is happening can we insert a query ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Sure. Shrink is a fully online operation. Not something that should be run often, only under exceptional situations though.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yuvipoy SSChampion Points: 10469 More actions · Answer 2

Will there be any impact in insert or select or delete or update statement like slowness

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sure. Shrink's an IO intensive operation.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yuvipoy SSChampion Points: 10469 More actions · Answer 4

GilaMonster (3/19/2014)
Sure. Shrink's an IO intensive operation.

You are saying that there will be impact or how?

Sorry unable to get you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Yes, there will be impact. Shrink is an IO-intensive operation (does a huge amount of IO operations)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yuvipoy SSChampion Points: 10469 More actions · Answer 6

GilaMonster (3/19/2014)
Yes, there will be impact. Shrink is an IO-intensive operation (does a huge amount of IO operations)

Actually DBCC Shrinkdatabase is back ground operation , it should not affect usual works right.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

yuvipoy (3/20/2014)
Actually DBCC Shrinkdatabase is back ground operation , it should not affect usual works right.

Um, I've told you twice it will have an impact. Is this just a case of ignoring what you don't want to hear?

Yes, there will be impact. Shrink is an IO-intensive operation (does a huge amount of IO operations)

It is not a 'background operation', it's an online operation which does a lot of IOs and will have an impact on the rest of the system.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

humbleDBA Hall of Fame Points: 3540 More actions · Answer 8

On top of what Gail has said, depending on the DB you are carrying out the Shrink work on - its size, how many indexes, etc. - you may experience performance issues due to the heavy, almost 100% fragmentation of the indexes of that DB caused by the shrink operation (see Paul Randal's article Why You SHould Not Shrink Your Data Files[/url]). Not an operation I would like to be doing, except in 'exceptional' circumstances.