Can I kill DBCC Shrinkfile???

Question

Post reply

Can I kill DBCC Shrinkfile???

DBADave

SSChampion

Points: 10962
More actions
October 14, 2005 at 11:10 am

#169361

Is it safe to kill DBCC Shrinkfile? The process has been running for 30 minutes on an 11GB database. I have no idea how much more time it will take to shrink the database and would like to kill it, assuming that would not cause a 30 minute rollback.
Dave
October 17, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

I presume this is still not running

I'd say it'd be a safe bet that killing a process thats shuffling data around in the database is not a good thing to do

--------------------
Colt 45 - the original point and click interface

Rick Phillips-220796 SSC Eights! Points: 997 More actions · Answer 2

DBCC Shrinkfile is transaction safe. You can cancel it at any time.

We would have to cancel this process when the shrinkfile failed to finish during the "quiet" time.

Rick

DBADave SSChampion Points: 10962 More actions · Answer 3

Thanks Rick. Fortunately it completed just before I killed the process.

Dave

vikasrana2006 SSC Enthusiast Points: 130 More actions · Answer 4

Same kind of problem occurred in my UAT Env where after database archival activity I was shrinking my database which took more than 2 Hour to Shrink 80 GB.

I simply close the EM and Kill that session and transaction rollback immediate without any issue.

😀

Victor Shahar Mr or Mrs. 500 Points: 533 More actions · Answer 5

Hi,

Shrinking datafiles is not a good idea in generally but if you have to do it, use a loop with small chunks of sizes to shrink, for example something like 500 MB each time, you can also add a 'control' table that can be checked in the loop and you can stop the shrink process in the next circle inside the loop by checking some value, for example if there is a '1' you break the loop, else continue.

Happy to help you,

Victor

raghurajparihar Valued Member Points: 74 More actions · Answer 6

Hi

we can use this command on behalf of shrink file command for the maintening the DB Size

backup log DBname to disk='D:\Folder\mandilog.bak'

with truncate_only

Raghuraj

SQL DBA

mogalappa Valued Member Points: 56 More actions · Answer 7

Killing of Shrink data file command will cause rollback and it will take more time to rollback depending upon the data file size. Sometime rollback will not happen for a days for larger files and if you recycle SQL server, database may go in recovery forever.

If you want to shrink data file, do it in smaller chuck of size from large free space available and continue. Do not shrink multiple data files from the same database as it may go in deadlock situation where recovery of database is a problem.

You should shrink one data file at a time from the database to avoid deadlocks and recovery issues.

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

Please note: 9 year old thread.

Shrink works in lots of little transactions, so there's little chance of a rollback taking forever. A database recovery after a restart won't take forever, it can take a very long time (seen days)

You can't shrink multiple data files of the same database at the same time, you'll get an error if you try. Something about file operations been serialised.

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

If I stop shrink command before its completion, does it rollback everything ?

Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

SQL-DBA-01 (3/7/2016)
If I stop shrink command before its completion, does it rollback everything ?

According to Gail's last post, it won't.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2