What happens when delete query executed inside sql server engine , process in deleting file , how data page will be managed , how deleted data pages will be free for next use?

Question

What happens when delete query executed inside sql server engine , process in deleting file , how data page will be managed , how deleted data pages will be free for next use?

ravi@sql

SSCrazy

Points: 2121
More actions
October 27, 2017 at 12:38 am

#326026

Hi Champs,
I want to understand what happens when delete query executed in inside sql server engine , how page de allocation happens , how data page becomes free for next use ? or steps in delete statement ? please share any links or stuffs for better understanding .

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

ravi@sql - Friday, October 27, 2017 12:38 AM
Hi Champs,
I want to understand what happens when delete query executed in inside sql server engine , how page de allocation happens , how data page becomes free for next use ? or steps in delete statement ? please share any links or stuffs for better understanding .

The basics of the process (ghosting) is explained in this post:
When a Delete isn't Really a Delete

And then you can find more details about ghost cleanup in this post:
Inside the Storage Engine: Ghost cleanup in depth

You can find a lot of other information, issues, etc if you search on Ghost Cleanup.

Sue

sgmunson SSC Guru Points: 110639 More actions · Answer 2

Sue_H - Friday, October 27, 2017 7:35 AM
ravi@sql - Friday, October 27, 2017 12:38 AM
Hi Champs,
I want to understand what happens when delete query executed in inside sql server engine , how page de allocation happens , how data page becomes free for next use ? or steps in delete statement ? please share any links or stuffs for better understanding .
The basics of the process (ghosting) is explained in this post:
When a Delete isn't Really a Delete
And then you can find more details about ghost cleanup in this post:
Inside the Storage Engine: Ghost cleanup in depth
You can find a lot of other information, issues, etc if you search on Ghost Cleanup.
Sue

Sue,

Why not just call the GhostBusters ? LOL 😉

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 3

sgmunson - Friday, October 27, 2017 12:05 PM
Sue,
Why not just call the GhostBusters ? LOL 😉

Now you have that going through my head...who you gonna call blah blah.
And I don't like that song.
And you did this to me on a Friday.

Sue

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Sue_H - Friday, October 27, 2017 12:55 PM
sgmunson - Friday, October 27, 2017 12:05 PM
Sue,
Why not just call the GhostBusters ? LOL 😉
Now you have that going through my head...who you gonna call blah blah.
And I don't like that song.
And you did this to me on a Friday.
Sue

With pictures of little "Stay-Puft" marshmallow men dancing in your head. 😉

--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)

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Sue_H - Friday, October 27, 2017 12:55 PM
sgmunson - Friday, October 27, 2017 12:05 PM
Sue,
Why not just call the GhostBusters ? LOL 😉
Now you have that going through my head...who you gonna call blah blah.
And I don't like that song.
And you did this to me on a Friday.
Sue

Sorry about that, .... but it was kind of irresistable from a certain perspective... I'll at least wish you a happy Monday, though... 🙂

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Well now I feel so much better, Thank you.
You know what they say about payback though. When you least expect it........

Sue

sgmunson SSC Guru Points: 110639 More actions · Answer 7

Sue_H - Monday, October 30, 2017 8:48 AM
Well now I feel so much better, Thank you.
You know what they say about payback though. When you least expect it........
Sue

Yep. I am all too familiar with Karma. I also know "Slow butt Shirley" far better than I should. Of course, had I known in advance that the movie in question's theme song was a bug-a-boo for you, I could have taken a different course of action, but alas, I did not... "know your audience" "know your audience" "know your audience" is now ringin' in me ears...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

sgmunson - Monday, October 30, 2017 9:15 AM
Sue_H - Monday, October 30, 2017 8:48 AM
Well now I feel so much better, Thank you.
You know what they say about payback though. When you least expect it........
Sue
Yep. I am all too familiar with Karma. I also know "Slow butt Shirley" far better than I should. Of course, had I known in advance that the movie in question's theme song was a bug-a-boo for you, I could have taken a different course of action, but alas, I did not... "know your audience" "know your audience" "know your audience" is now ringin' in me ears...

Heh... the ultimate revenge... anyone up for several thousand rounds of "Its a Small World"? 😉

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Jeff Moden - Monday, October 30, 2017 9:29 AM
sgmunson - Monday, October 30, 2017 9:15 AM
Sue_H - Monday, October 30, 2017 8:48 AM
Well now I feel so much better, Thank you.
You know what they say about payback though. When you least expect it........
Sue
Yep. I am all too familiar with Karma. I also know "Slow butt Shirley" far better than I should. Of course, had I known in advance that the movie in question's theme song was a bug-a-boo for you, I could have taken a different course of action, but alas, I did not... "know your audience" "know your audience" "know your audience" is now ringin' in me ears...
Heh... the ultimate revenge... anyone up for several thousand rounds of "Its a Small World"? 😉

Beers on the Wall; Row, Row, Row Your Boat; and last but not least, The Wheels on the Bus.

sgmunson SSC Guru Points: 110639 More actions · Answer 10

Lynn Pettis - Monday, October 30, 2017 9:44 AM
Jeff Moden - Monday, October 30, 2017 9:29 AM
sgmunson - Monday, October 30, 2017 9:15 AM
Sue_H - Monday, October 30, 2017 8:48 AM
Well now I feel so much better, Thank you.
You know what they say about payback though. When you least expect it........
Sue
Yep. I am all too familiar with Karma. I also know "Slow butt Shirley" far better than I should. Of course, had I known in advance that the movie in question's theme song was a bug-a-boo for you, I could have taken a different course of action, but alas, I did not... "know your audience" "know your audience" "know your audience" is now ringin' in me ears...
Heh... the ultimate revenge... anyone up for several thousand rounds of "Its a Small World"? 😉
Beers on the Wall; Row, Row, Row Your Boat; and last but not least, The Wheels on the Bus.

And let's not forget an endless loop of the Looney Tunes theme that followed each cartoon...
or, for that matter, of any 1950's, 1960's, or even 1970's sitcom theme.. (e.g. All in the Family, Happy Days, Mash, Leave it to Beaver, etc.)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)