Killing a database shrink process

Question

Killing a database shrink process

newbie2sql

SSC Eights!

Points: 987
More actions
May 7, 2010 at 12:14 am

#140074

Hi,
One of my colleague had given a shrink command on a mdf file and its running for the past 24 hours. He executed the command think he can get more free space on the drive. Can I kill the process. How much time will the rollback take. What are the pros and cons for this. Mdf file size is around 300gb with nearly 100gb free space and log file with 50gb.
Thanks
Mohan

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

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

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

Yes, you can kill it.

Rollback time - unknown. As far as I know, it won't undo the entire thing, so should be relatively quick. If I'm wrong and it does have to roll the entire thing back, it can easily take longer to

Why were you shrinking in the first place?

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

You will need to schedule an index rebuild soon to fix the fragmentation that the shrink caused.

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

newbie2sql SSC Eights! Points: 987 More actions · Answer 2

Hi,

As per below link it does not take much time as it moves only 32 pages at a time. Thanks for the information.

http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

Thanks

Mohan

balaji.ganga SSCrazy Points: 2280 More actions · Answer 3

hi..

while shrinking a database that time if kill that process.. if database is going to be crash or not..

reply pls

With Regards

BalajiG

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

Don't understand what you're asking.

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 5

balaji.ganga (5/7/2010)
hi..
while shrinking a database that time if kill that process.. if database is going to be crash or not..
reply pls
With Regards
BalajiG

read Gail's first answer, you asked the same as the original poster here.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Stamey SSCertifiable Points: 5600 More actions · Answer 6

To be clear, you can kill a shrink any time with no ill effect, except that the DB or log file will still be large. It will stop with no noticeable rollback of what it has already done.

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.