Ok to kill DBCC Shrink datafile | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Ok to kill DBCC Shrink datafile

Hi,
I wanted to shrink the datafile since dropping a large table, but the process is taking much longer than I thought and users are complaining. Is there any harm in killing this process. Thanks.
Don Don Saluga
No there is no harm in killing the process midway.
Thanks for your reply. I once had this process stopped by a deadlock, but I never just killed it. I didn’t want to make any more problems if I killed it.
Don Don Saluga
Hi,<br />about killing processes <br />BOL Says :<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Remarks<br />KILL is commonly used to terminate a process that is blocking other important processes with locks, or to terminate a process that is executing a query that is using necessary system resources. System processes and processes running an extended stored procedure cannot be terminated.<br /><br />Use KILL very carefully, especially when critical processes are running. You cannot kill your own process. Other processes not to kill are: <br /><br />AWAITING COMMAND<br /><br /><br />CHECKPOINT SLEEP<br /><br /><br />LAZY WRITER<br /><br /><br />LOCK MONITOR<br /><br /><br />SELECT<br /><br /><br />SIGNAL HANDLER <br />Execute sp_who to get a report on valid SPID values. If a rollback is in progress for a specific SPID, the cmd column for the specific the SPID in the sp_who result set will indicate ‘KILLED/ROLLBACK’.<br /><br />Use @@SPID to display the SPID value for the current session. <br /><br />In SQL Server 2000, the KILL command can be used to resolve SPIDs associated with non-distributed and distributed transactions. KILL also can be used to resolve orphaned or in-doubt distributed transactions. A distributed transaction is orphaned when it is not associated with any current SPID. <br /><br />The SPID value of ‘-2′ is set aside as an indicator of connectionless, or orphaned, transactions. SQL Server assigns this value to all orphaned distributed transactions, making it easier to identify such transactions in sp_lock (spid column), sp_who (blk column), syslockinfo, and sysprocesses. This feature is useful when a particular connection has a lock on the database resource and is blocking the progress of a transaction. The user would be able to identify the SPID that owns the lock, and end the connection.<br /><br />The KILL command can be used to resolve in-doubt transactions, which are unresolved distributed transactions resulting from unplanned restarts of the database server or DTC coordinator. For more information on resolving in-doubt transactions<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
I would suggest not to interrupt during such DBCC operations, sometimes the database may get into suspect state if one of the process is killed and unable to rollback. I suggest to perform the SHRINK operation during less traffic hours on the database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
You can create a job and run it once or twice a week, during off peak hours .
use [Pubs] DBCC SHRINKFILE (N’Pubs_Log’) -Rajiv
It is not helpful and suggestable to shrink the database on regular basis, you have to consider the data growth and give the value to data file in order to avoid any interim datasize growth. Only when it is needed then you may proceed for shrink process, otherwise leave the settings as is. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Ok, i think i suggested the solution to avoid large logfile growth, which is somthin irrelavant here. Satya, thanks for the correction .
–Rajiv
Don
On the other hand the problems seems to be replicated, when you’re performing SHRINK operation check for any other jobs that are involed in bulk jobs or any uncommitted transactions on the tlog contributing this slow operation for users also when using SELECT clauses. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>