January 10, 2012 at 8:13 pm
Hello,
😉 I have learned from school, that a balanced B-Tree is the concept behind any database.
Each index contains at least one pointer to a data page.
So, if you are going to delete a certain index entry in the easiest way that the entry can be found in a leaf, it can be deleted directly without moving it on. If so, entries could be deleted completely by a "DELETE FROM myDatatable WHERE _Field = nnn" e.g.
My question is and what I do not understand is the following.
There is a TSQL-Command like "DBCC SHRINKDATABASE" which is often proposed to be a good choice after deleting many records.
Basically, it is supposed to reorganize a db. But what kind of things are deleted when it is executed ? And how does it work exactly ?
Also,when deleting a record, I can rollback this transaction.
Does this SQL-Command just mark an entry for deletion that can be finally removed from the database with "SHRINKDATABASE" ?
Thank you, so much in advance !
Yours,
Steve
January 11, 2012 at 1:12 am
Steve2012 (1/10/2012)
There is a TSQL-Command like "DBCC SHRINKDATABASE" which is often proposed to be a good choice after deleting many records.Basically, it is supposed to reorganize a db. But what kind of things are deleted when it is executed ? And how does it work exactly ?
Nothing's deleted by shrink. Shrink also does not reorganise a database (that myth comes from Access's compact and repair).
What shrink does is move empty pages (pages are 8kb chunks of the data file) to the end of the file (well, actually it moves in-use pages early, which has the same effect) and then releases the free space at the end of the file back to the OS.
Shrink's not a good choice, it should only be run if a significant amount of space has been freed up and that space won't be reused for a comparatively long time
Also,when deleting a record, I can rollback this transaction.
Does this SQL-Command just mark an entry for deletion that can be finally removed from the database with "SHRINKDATABASE" ?
No, delete does just mark rows as 'ghosted' and there's a process later that actually deletes them, but that process is not shrink. It's the ghost cleanup task.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply