Reindex / Shrink Database

 

Back Office > Administration > SQL Tools > Database Maintenance > Reindex Database

 

Description

 

This menu item is used to reindex (recreate) the indexes of all tables and/or shrink (compress) the SwiftPOS Back Office and/or the Table Tracking databases. Note : Instead of Shrinking, the Data Rollup feature can be used as an alternative to reduce the size of the SwiftPOS Back Office database.

To Be Considered

 

  1. Note : DO NOT SHRINK the same Database after a REINDEX has been done. This will impact adversely on performance.

 


Reindex/Shrink Database

 

 

Reindex / Shrink Database
 

  • Select from the drop down list of Databases the appropriate Database.

 

Database Tasks

 

Reindex

 

  • Reindex - Select to recreate the index of each table within the selected Database. A power failure on the SQL Server PC, may cause indexes to be corrupted and result in not all data being visible. This feature will re-build all indexes and ensure that the data is up to date. It is recommended that this be set up as a Scheduled Task on a weekly or monthly basis. This can also lead to an improved performance when doing database searches.

    This will reindex all the tables and leave 20% of space for growth of that table. (80% Fill Factor). To re-index a Clustered (Primary Key) index an entire copy of the table is placed in the tempdb and resorted.  After which it is copied back as a new table into the database and the original table is removed.  This process will increase the size of your database in proportion to the table size.  The white (empty) space left after removing the original table will more than likely be used for the next Clustered Index on another table.  E.g.  The next Clustered Index rebuilt will copy back from the tempdb into the white space left by previous original table removed on last Clustered Index rebuild. Clustered Indexes are basically a rule on how the data in a table is stored in a sorted order.  Non Clustered Indexes contain pointers to the rows being indexed by columns.  So when a Clustered Index is rebuilt ALL other Non-Clustered indexes have to be rebuilt to update the pointers in case the row reference has now changed. This makes the database SWIFTPOS.MDF larger by the exact size of this table and this is left as free space in the final SWIFTPOS.MDF database. This will make your SWIFTPOS.MDF larger but it only contains the free space which will get used in the future. If you are running SQL Express which has a 10GB limit, and your database is reaching that limit then you will need to archive transaction history either by the ???Roll Up??? feature or clearing out sales data over xx years old.
  • Fill Factor % ??? Set to between 70% and 100%. Default is 95%. A Setting of 100% is NOT recommended, as it will allow no room for the growth of each Database table.
  • Shrink before Reindexing ??? This is the only time it is recommend to do a Shrink.
  • Force Rebuild of ALL indexes ??? If tables only have a small amount of fragmentation then by default it will ignore these tables. This option will force it to rebuild all indexes.
  • Update Statistics ??? This records all the statistics in the Log files so you can analyse which tables are taking up all the space.
  • Sort Tables by Size (DESC) - Helps with keeping the database size down during the re-index process.
  • Disable Logging Sizes - Select to disable the logging of table and index sizes before and after Reindexing.

 

Shrink

 

  • Shrink - Select to Shrink (compress) the selected Database. This will assist in reducing the current space requirements of the selected Database.  Note : It is recommended to only Shrink Databases BEFORE the Reindex. Also, the Data Roll-up feature can also be used as an alternative to reduce the size of the SwiftPOS Back Office Database.

    This removes all the deleted data and shrinks all the tables down with zero free space for future growth. This makes SwiftPOS run slower because every time it needs to add a record, it has to increase the size of the database which takes time.
     
  • Free Space % - Displays/Enter the percentage of Free space to be left for future growth once the Shrink has completed.

 

Last Run Timers

 

  • Displays the elapsed time the last time a Reindex was ran. Note : The time elapsed will also be logged and can be viewed via the Event Log Viewer

 

Footer

 

  • Close - Select to exit.

 


Related Topics

 

  1. Scheduled Tasks
  2. SQL Server Management Studio
  3. SQL Tools