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!
- Stop the CAREWare business tier, to ensure that no one will try to access the database during this time.
- 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
- 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.
- Central Administration domain
- Once logged in, press Ctrl-I
- Look for the Reindex Now button in the lower right corner
- Press it and allow the re-indexing to complete
- Once it is complete a Success! message will appear notifying you
- 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 TierBack to
Frequently Asked Questions