Is Killing Database Shrink via SSMS a risk?

Question

Post reply

Is Killing Database Shrink via SSMS a risk?

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 17, 2014 at 9:44 am

#308262

I started to shrink an mdf via SSMS.
I specified reorganize pages before releasing unused space.
If I were to kill the process could I corrupt the Database?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Gazareth

One Orange Chip

Points: 27737

June 17, 2014 at 10:08 am

#1722388

Nope.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I found the following article that state that you should a good backup before you Kill the process.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153850">

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153850

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

http://www.sqlservercentral.com/Forums/Topic917650-146-1.aspx

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

Welsh Corgi (6/17/2014)
I found the following article that state that you should a good backup before you Kill the process.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153850">
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153850

I think what Russell meant there was make sure you have a good backup before starting the shrink. Good advice.

Killing the shrink won't corrupt your database.

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

Welsh Corgi (6/17/2014)
If I were to kill the process could I corrupt the Database?

No.

There is no process in SQL that you can run or kill that will cause corruption.

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 6

GilaMonster (6/17/2014)
Welsh Corgi (6/17/2014)
If I were to kill the process could I corrupt the Database?
No.
There is no process in SQL that you can run or kill that will cause corruption.

Quick question Gail, are there any processes which would corrupt the data if forcefully terminated, for instance pulling the plug / hard reset or bouncing the service?

😎

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 7

Gazareth (6/17/2014)
Welsh Corgi (6/17/2014)
I found the following article that state that you should a good backup before you Kill the process.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153850">
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153850
I think what Russell meant there was make sure you have a good backup before starting the shrink. Good advice.
Killing the shrink won't corrupt your database.

Well, not automatically cause corruption, no, I agree. But there are processes that you don't really want to have mess up in the middle because of a somewhat higher likelihood of corruption wouldn't you agree? Shrink is something I wouldn't interrupt if I didn't have to.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Eirikur Eiriksson (6/17/2014)
GilaMonster (6/17/2014)
Welsh Corgi (6/17/2014)
If I were to kill the process could I corrupt the Database?
No.
There is no process in SQL that you can run or kill that will cause corruption.
Quick question Gail, are there any processes which would corrupt the data if forcefully terminated, for instance pulling the plug / hard reset or bouncing the service?

No.

Pulling the plug on the SAN however might, depending how the caching is configured, whether the disks handle the sudden loss of power, etc.

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

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

Grant Fritchey (6/17/2014)
But there are processes that you don't really want to have mess up in the middle because of a somewhat higher likelihood of corruption wouldn't you agree?

No, not any more than a delete or a truncate table or a large insert or merge, or an index reorganise. If the IO subsystem is going to barf all over a database file, it's not going to wait for a specific operation to be stopped to do so.

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 10

GilaMonster (6/17/2014)
Eirikur Eiriksson (6/17/2014)
GilaMonster (6/17/2014)
Welsh Corgi (6/17/2014)
If I were to kill the process could I corrupt the Database?
No.
There is no process in SQL that you can run or kill that will cause corruption.
Quick question Gail, are there any processes which would corrupt the data if forcefully terminated, for instance pulling the plug / hard reset or bouncing the service?
No.
Pulling the plug on the SAN however might, depending how the caching is configured, whether the disks handle the sudden loss of power, etc.

Thanks Gail!

Guess that is one external dependency to put on the check-list.

😎

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 11

I will add my comment too.

"No"

We perform shrinks all the time and stop them half way no issues. (The other side is it's not good to perform shrinks - but end up doing it out neccessity to save space in non prod envs)

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Gazareth One Orange Chip Points: 27737 More actions · Answer 12

Eirikur Eiriksson (6/17/2014)
GilaMonster (6/17/2014)
Eirikur Eiriksson (6/17/2014)
GilaMonster (6/17/2014)
Welsh Corgi (6/17/2014)
If I were to kill the process could I corrupt the Database?
No.
There is no process in SQL that you can run or kill that will cause corruption.
Quick question Gail, are there any processes which would corrupt the data if forcefully terminated, for instance pulling the plug / hard reset or bouncing the service?
No.
Pulling the plug on the SAN however might, depending how the caching is configured, whether the disks handle the sudden loss of power, etc.
Thanks Gail!
Guess that is one external dependency to put on the check-list.
😎

MS's comments on this: http://support.microsoft.com/kb/86903

There's another link on the page to more detailed requirements.

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 13

Gazareth (6/18/2014)
MS's comments on this: http://support.microsoft.com/kb/86903
There's another link on the page to more detailed requirements.

Thanks!

😎