Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I kill DBCC Shrinkfile??? Expand / Collapse
Author
Message
Posted Friday, October 14, 2005 11:10 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:17 AM
Points: 454, Visits: 879

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




Post #229091
Posted Monday, October 17, 2005 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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!
Post #229335
Posted Monday, October 17, 2005 5:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204

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
Post #229558
Posted Monday, October 17, 2005 6:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 12, 2007 8:02 AM
Points: 299, 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

  

Post #229567
Posted Monday, October 17, 2005 8:44 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:17 AM
Points: 454, Visits: 879

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

Dave




Post #229580
Posted Monday, April 27, 2009 12:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 4:37 AM
Points: 54, Visits: 292
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.
Post #705222
Posted Tuesday, April 28, 2009 10:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:25 PM
Points: 55, Visits: 217
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
Post #706071
Posted Friday, May 1, 2009 1:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 6:59 AM
Points: 2, 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
Post #708284
Posted Monday, August 11, 2014 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:05 PM
Points: 2, 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.
Post #1602084
Posted Monday, August 11, 2014 3:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,898, Visits: 36,243
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 2008, MVP
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

Post #1602092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse