Help!!Compacting Database MSDE/SQL
-
Hi Guys, I have not known so much about the SQL Server and MSDE, so just rush to here to get help. Can any one tell me When records are deleted, is the space immediately compacted or when is it compacted? and Is the compacting configurable? How to set it up? Since I am using the SQL buddy For large number of records deleted (eg. Archival), is the database compacted immediately! Many thanks in advance! I am really appreciate your help.
-
Hi Guys, I have not known so much about the SQL Server and MSDE, so just rush to here to get help. Can any one tell me When records are deleted, is the space immediately compacted or when is it compacted? and Is the compacting configurable? How to set it up? Since I am using the SQL buddy For large number of records deleted (eg. Archival), is the database compacted immediately! Many thanks in advance! I am really appreciate your help.
SQL Server does not generally compact free space. To do so, use the Shrink feature (in T-SQL,
DBCC SHRINKDATABASE). To have this occur automatically, set the 'Auto Shrink' option. You can do this in T-SQL withALTER DATABASE db SET AUTO_SHRINK ON. MSDE has this option turned on by default. If Auto Shrink is set, the shrinking operation occurs when there is more than 25% free space in the file. The resulting file will have 25% space free or be the size it was when originally created, if that is larger. Stability. What an interesting concept. -- Chris Maunder -
SQL Server does not generally compact free space. To do so, use the Shrink feature (in T-SQL,
DBCC SHRINKDATABASE). To have this occur automatically, set the 'Auto Shrink' option. You can do this in T-SQL withALTER DATABASE db SET AUTO_SHRINK ON. MSDE has this option turned on by default. If Auto Shrink is set, the shrinking operation occurs when there is more than 25% free space in the file. The resulting file will have 25% space free or be the size it was when originally created, if that is larger. Stability. What an interesting concept. -- Chris MaunderHi Mike, Thanks very much for your reply. So the compacting is a default option which already be turned on in MSDE? How about SQL Server, the option is also turned on by default? Is there any way I can configure this option so that it can be performed when there is 10% free space? Many thanks for your help!!