Drop table rollback

Question

Drop table rollback

sugnu iu

SSCommitted

Points: 1594
More actions
June 17, 2014 at 5:07 am

#292497

Hi all
I got a question which i could not find answer given my knowledge of SQL.
one of my users performed a DROP TABLE on a table with 96 million rows. after he run the DROP he realized after 30 seconds that was the wrong table and he cancelled the query execution.
The table was still there and all the data as well. but there was no BEGIN TRANSACTION.... how can it be the table and its data still there?
can somebody explain to me what happened?

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

sugnu iu (6/17/2014)
Hi all
I got a question which i could not find answer given my knowledge of SQL.
one of my users performed a DROP TABLE on a table with 96 million rows. after he run the DROP he realized after 30 seconds that was the wrong table and he cancelled the query execution.
The table was still there and all the data as well. but there was no BEGIN TRANSACTION.... how can it be the table and its data still there?
can somebody explain to me what happened?

SQL Server uses implicit transactions. Meaning that a single statement is a transaction on its own. If it completes, it is committed. If it fails or get canceled, it is rolled back.

Implicit Transactions

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Extract from BOL for (Drop Table):

Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Now in this case due to huge table before sql server create internal checkpoint commnad was cancelled in 30 min and it might have rollback completely.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

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

free_mascot (6/17/2014)
Now in this case due to huge table before sql server create internal checkpoint commnad was cancelled in 30 min and it might have rollback completely.

Nothing to do with checkpoints.

All operations in SQL are atomic, meaning they complete entirely or not at all. He stopped the drop table part way through its execution and so it didn't complete. Since it didn't complete, it had to roll back entirely.

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

sugnu iu SSCommitted Points: 1594 More actions · Answer 4

thanks to all. it make sense.

so is this behavior the same even if i do a DELETE FROM with no WHERE?

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

Nothing to do with checkpoints.

Thanks Gail,

Agreed; it do not have to do anything with respect to checkpoints.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

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

Yup, any single statement. Thing is, you have to stop it before it completes, and SQL won't always process a stop request immediately so you can easily stop too late and have the statement complete

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

sugnu iu SSCommitted Points: 1594 More actions · Answer 7

sugnu iu

SSCommitted

Points: 1594

June 17, 2014 at 6:07 am

#1722280

thanks a lot.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

sugnu iu (6/17/2014)
one of my users performed a DROP TABLE on a table with 96 million rows.

The logical question now would be...

Why do you have users that have such high privs? I'd strongly recommend changing your security model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sugnu iu SSCommitted Points: 1594 More actions · Answer 9

sugnu iu

SSCommitted

Points: 1594

June 17, 2014 at 10:40 am

#1722405

dev environment

rhythmk SSCertifiable Points: 7271 More actions · Answer 10

sugnu iu (6/17/2014)
dev environment

So are you ok with dropping a table having 90 million records from any environment ? Think of the scenario when you are working on some urgent requirement and this user drops the most important table :blink:

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 11

rhythmk (6/17/2014)
sugnu iu (6/17/2014)
dev environment
So are you ok with dropping a table having 90 million records from any environment ? Think of the scenario when you are working on some urgent requirement and this user drops the most important table :blink:

I can imagine they don't have the same rights in production as they have in the dev environment.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sugnu iu SSCommitted Points: 1594 More actions · Answer 12

guys dev is a play ground. uat and live are untouchable

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

rhythmk (6/17/2014)
sugnu iu (6/17/2014)
dev environment
So are you ok with dropping a table having 90 million records from any environment ? Think of the scenario when you are working on some urgent requirement and this user drops the most important table :blink:

In most places I've worked, devs are responsible for their own development databases. If they mess up the dev database, their problem. Sure, there are backups and they can be restored. DBAs certainly don't want to be spending their day creating and modifying tables for the devs, and the devs don't want to have to go to the DBA every time they want a schema change. That would slow the entire development process down horribly.

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