Page History: How can I perform maintenance on my CAREWare database?
Compare Page Revisions
Page Revision: Tue, 29 Dec 2009 09:52
The SQL Server database occasionally needs maintenance particularly if you have a large amount of data. In a future build, we will have some maintenance features built into CAREWare, but for now here is a way to manually do this maintenance.
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.
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
Frequently Asked Questions