SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I kill DBCC Shrinkfile???


Can I kill DBCC Shrinkfile???

Author
Message
DBADave
DBADave
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2595 Visits: 1109

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





Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12603 Visits: 16
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
philcart
philcart
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9539 Visits: 1441

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



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Rick Phillips-220796
Rick Phillips-220796
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 1

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
DBADave
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2595 Visits: 1109

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

Dave





vikasrana2006
vikasrana2006
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 360
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.
:-D
Victor Shahar
Victor Shahar
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 248
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
raghurajparihar
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 5
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
mogalappa
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 61
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219039 Visits: 46279
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search