How can I perform maintenance on my CAREWare database?

Modified on Wed, 27 May 2015 14:32 by Kevin Ricciardo — Categorized as: FAQ

The SQL Server database occasionally needs maintenance particularly if you have a large amount of data. Currently there is a maintenance feature built into CAREWare, but for very large databases it may not work properly. In situations such as this you can run the query below using any SQL manager.

NOTE: As always, before beginning you should make a backup of the database. Never do any significant operations without first making a good backup.

NOTE: This may take a long time (like hours) and you will not be able to use the database during that time!

  1. Stop the CAREWare business tier, to ensure that no one will try to access the database during this time.
  2. Open Enterprise Manager or Craig's SQL Manager paste this into a query window:


    USE cw_data
    DECLARE @TableName varchar(255)
    DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'
    OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,'',90) FETCH NEXT FROM TableCursor INTO @TableName END
    CLOSE TableCursor DEALLOCATE TableCursor

  3. Run this query and it will rebuild all the indexes for the tables in the database.

You can access the re-indexing query with CAREWare with the following procedure.

  1. Central Administration domain
  2. Once logged in, press Ctrl-I
  3. Look for the Reindex Now button in the lower right corner
  4. Press it and allow the re-indexing to complete
  5. Once it is complete a Success! message will appear notifying you
  6. Press OK

Once this is done, you should see a dramatic speed increase, particularly in reports that compile large amounts of data (I.e. running the RDR).

Back to the Data Tier

Back to Frequently Asked Questions