Note: This applies to DocuShare 6.5.x
Note: The term MSDE and SQLExpress interchangeable.
For active sites it is recommended that the following maintenance/optimization is done to the database:
· Daily update statistics
· Check on fragmentation once a month
Note: If you have SQL Server Express without the sqlserver agent (by default DocuShare Installs SQL Express without the sqlserver agent), then to schedule the maintenance/optimization tasks to run regularly you will need to utilize the operating system task scheduling.
Consult your DBA before attempting to run these commands. The DBA will determine the appropriate settings and configurations for your environment. Only the DBA has the security permissions to run the database optimization.
Note: Depending on the size and usage of your DocuShare site, performance may be slow during database optimization. We recommend that you use Site Management/Access and set Site Access Authority to Administrator.
The following commands are recommendations for various database applications. Changes may be necessary to fit your specific environment. All commands in the examples run on the database server, which may or may not be the same server that is running your DocuShare site.
Note: This database optimization procedure assumes that the default installation of DocuShare is on a Windows server.
Caution: Only an experienced Microsoft SQL Server DBA should use the analyzer. Creating unnecessary indexes can severely degrade database performance.
Note: sp_updatestats effectively executes UPDATE STATISTICS, by specifying the ALL keyword, against all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables. Statistics on disabled nonclustered indexes are also updated by sp_updatestats. sp_updatestats ignores tables with a disabled clustered index.
To optimize the database from the query analyzer:
UPDATE STATISTICS DSObject_table WITH FULLSCAN;
UPDATE STATISTICS ACL_table WITH FULLSCAN;
UPDATE STATISTICS Link_table WITH FULLSCAN;
UPDATE STATISTICS dsprop_table WITH FULLSCAN;
UPDATE STATISTICS userInfo_table WITH FULLSCAN;
UPDATE STATISTICS eventTable WITH FULLSCAN;
UPDATE STATISTICS eventObjectTable WITH FULLSCAN;
UPDATE STATISTICS CE_table WITH FULLSCAN;
UPDATE STATISTICS rootObjectTable WITH FULLSCAN;
To optimize the database from a Command Line:
1. Open a command prompt window.
2. Use the CD (change directory) to navigate to the Binn directory.
For example: CD C:\Xerox\Docushare\SQLExpress\MSSQL.2\MSSQL\Binn
3. From the Binn directory, run the osql.exe command by entering the following line, replacing all bracketed names with the correct values for your installation:
osql -S [servername]\[MSDE_InstanceName] -d [DocushareDatabaseName] -U
SA -P [SA_Password]
Note : The default Instance Name at the time of install is Docushare and the default Database Name is Docushare.
4. Press Enter. The command displays the OSQL prompt 1>.
Note: If the 1> prompt is not displayed then you will need to verify the information specified in step 3 is correct for you server.
5. At the 1> prompt, enter UPDATE STATISTICS DSObject_table WITH FULLSCAN; and press Enter.
6. At the 2> prompt, enter UPDATE STATISTICS ACL_table WITH FULLSCAN; and press Enter.
7. At the 3> prompt, enter UPDATE STATISTICS Link_table WITH FULLSCAN; and press Enter.
8. At the 4> prompt, enter UPDATE STATISTICS dsprop_table WITH FULLSCAN; and press Enter.
9. At the 5> prompt, type UPDATE STATISTICS userInfo_table WITH FULLSCAN; and press Enter.
10. At the 6> prompt, type UPDATE STATISTICS eventTable WITH FULLSCAN; and press Enter.
11. At the 7> prompt, type UPDATE STATISTICS eventObjectTable WITH FULLSCAN; and press Enter.
12. At the 8> prompt, type UPDATE STATISTICS CE_table WITH FULLSCAN; and press Enter.
13. At the 9> prompt, type UPDATE STATISTICS rootObjectTable WITH FULLSCAN; and press Enter.
14. At the 10> prompt type go and press Enter. When the optimization processing is complete, OSQL displays a 1> prompt.
Example:
C:\Xerox\Docushare\SQLExpress\MSSQL.2\MSSQL\Binn>osql -S MYSERVER\Docushare -d Docushare -U sa –P password01
1> UPDATE STATISTICS DSObject_table WITH FULLSCAN;
2> UPDATE STATISTICS ACL_table WITH FULLSCAN;
3> UPDATE STATISTICS Link_table WITH FULLSCAN;
4> UPDATE STATISTICS dsprop_table WITH FULLSCAN;
5> UPDATE STATISTICS userInfo_table WITH FULLSCAN;
6> UPDATE STATISTICS eventTable WITH FULLSCAN;
7> UPDATE STATISTICS eventObjectTable WITH FULLSCAN;
8> UPDATE STATISTICS CE_table WITH FULLSCAN;
9> UPDATE STATISTICS rootObjectTable WITH FULLSCAN;
10> go
1>