Can I kill DBCC Shrinkfile???
-
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
-
This was removed by the editor as SPAM
-
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
-
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
-
Thanks Rick. Fortunately it completed just before I killed the process.
Dave
-
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.
😀
-
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
-
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
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If I stop shrink command before its completion, does it rollback everything ?
Thanks.
-
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply