I would like to know what happens on level of pages end extents. Is row deleted from page or just marked as deleted? Will data file loose on its size automatically? Do you know about any resource on the internet? Thank you :)

The record truly is deleted. You can't recover it. But unless it was the only record left on that page, then the page will still be "in use". And the DB *can* attempt to insert the next record into that page. That is, if the available space on the page is sufficient, the DB is allowed to reuse that space immediately. Whether it will or not--whether it will prefer placing the record someplace else--depends on how the table is indexed, whether you are using a primary key, etc.

But predicting exactly when and where the DB will reuse page space and/or when it will remove entire pages is not worth worryin about. Suffice to say, the DB engine tries to optimize the processes.

Man, I miss the good ol' days like on DBase III where the rows were only marked for deletion... made recovery from some pretty nasty mistakes a snap.

__________________
--Jeff Moden

LOL! Yeah, but you *NEEDED* recovery back then, because it was so painful to do backups! And hey, why'd you stop at DBase III??? I remember DBase and DBase II.

Heh... oh... you said "backups". How about "restores". Remember when you didn't have to restore a single tera-byte file to recover a lousy million row customer table? Remember when you could restore just one table? And, remember you could selectively do backups on just the tables/indexes that actually had changes in them? I sure do wish they keep that paradigm.

__________________
--Jeff Moden

Does anyone have a valid download link for the source code to this book? ISBD: 1-86100-732-9
I'm a new here,If anyone got the source ,Please send an E-mail to:[email protected]
Thanks!

You are posting in the WRONG FORUM. Find the forum FOR THAT BOOK and post there.

I have conducted an experiment. I created database with just one table without clustered index on that table, I restricted size of mdf file to 3 MB, with initial size of 2 MB, log file had initial size of 1MB, so initial size of DB was 3 MB overall. Then I populated table with data until I got "Could not allocate space for object 'dbo.Table_1' in database 'TestDB1' because the 'PRIMARY' filegroup is full." mdf file is now 3 MB and entire Db is 4MB. Then I said "delete from dbo.Table_1", size of log file increased to 39 MB, while mdf file stayed 3MB!!! Free space according to Management Studio 1.73 MB. So if SQL Server deletes records from pages immediately, when I deleted all rows why mdf size stayed 3MB?

Because now you can add more rows!!! :)

SQL will not shrink the database immediately. (in fact, I believe it will not shrink it never). It's expecting rows, and since you added then earlier, it will just keep the space for more.

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

Are you saying that Old Pedant is wrong? I have shrunk database and size of mdf is 2MB. I think that deleted recrods are just marked as deleted when you delete them until SQL Server runs shrinking or untill you run it by yoursefl