July 26, 2010 at 3:29 am
Hi All,
I have one question like, Is database shrinking as well as defragmentation of windows box functionality are same or if any other differences available?
If anybody know about this. Kindly let me know..
Thanks & Regards
Balaji.G
July 26, 2010 at 3:34 am
Hi
shrinking or growing a database will affect the database file size and ultimately the location of portions of the file on disk (they won't always be contiguous).
The OS file system fragmentation is directly effected by this especially if the file is not laid out contiguously. SQL Server will end up reading database pages from completely different areas of the disk, causing excessive disk head movement.
This is why constant shrinking and growing are discouraged!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 26, 2010 at 5:34 am
At the lowest level, there is the data file (.mdf). Assuming it's created on an empty disk, the initial physical layout of the file should not be fragmented. This file gets fragmented when it grows, unless the disk stores only that single file.
Up one level, you have extents in the data file. Extents (64 Kb in size) are used to hold database objects, including tables, and indexes. In an OLTP database, it's normal for the extents holding the object data to not be contiguous, as applications usually update multiple tables, and each grow at different rates. Extents also get freed when old objects or data are deleted.
When you shrink a database, SQL Server tries to use as little extents as possible to hold your data, but does nothing to defragment the physical file.
When you defragment a file in Windows e.g. the SQL Server data file, Windows will try to rearrange the file blocks so they are physically laid out contiguously on disk, but does nothing to rearrange the contents of the file.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply