Maintenance plans can be used to schedule tasks required to make sure that the database performs well, such as keeping database and transaction log file sizes small and checking the database for inconsistencies.
Autodesk recommends that the plan be run at least once per week, during out of office hours.
In a Connected Workgroup Environment, this should be configured on each SQL Server.
Part of the Administrators tasks should be to regularly check that the plan ran successfully.
If the plan was configured for an earlier release of Vault Server, ensure that you check and update the plan in accordance with this article.
The following steps are universal across all versions of SQL in use with Vault Server (Express and Full SQL). The complete list of supported database engines is located in the product readme file.
Note: In case of SQL 2016 or later, Data file will default to the lower value of 8 or number of logical cores as detected by setup. The value can be increased as required for a specific workload. The file names for secondary data files will follow the tempdb_mssql_#.ndf naming convention where # represents the file ordinal for each additional file.
Maintenance plans can be used to schedule tasks required to make sure that the database performs well, such as keeping database and transaction log file size small and checking the database for inconsistencies.
SQL Standard/Enterprise
Or, in table form:
Logical Name |
Size (MB) |
Autogrowth |
---|---|---|
KnowledgeVaultMaster |
|
By 10MB, unrestricted growth |
KnowledgeVaultMaster_log |
|
By 10 percent, unrestricted growth |
<Vault> |
|
By 100MB, unrestricted growth |
<Vault>_log |
500 |
By 10 percent, unrestricted growth |
<Custom_Library> |
|
By 25 MB, unrestricted growth |
<Custom_Library>_log |
|
By 10 percent, unrestricted growth |
<Standard_Library> |
|
By 25 MB, unrestricted growth |
<Standard_Library>_log |
|
By 10 percent, unrestricted growth |
Microsoft SQL Express does not allow you to create a maintenance plan inside of SQL Server Management Studio Express.
Make sure that the OS (Windows) user that is running the SQL maintenance plan has the sysadmin role inside of SQL.
Tempdb Modifications through the Command Prompt
As noted earlier in this section, if the SQL Server Management Studio is not installed, the tempdb database changes can be completed through the command prompt by performing the following:
SQLCMD -E -S".\AutodeskVault" -Q "ALTER DATABASE [tempdb] SET COMPATIBILITY_LEVEL = 110"
SQLCMD -E -S ".\AutodeskVault" -Q "ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 1024MB, FILEGROWTH = 100MB)"
SQLCMD -E -S ".\AutodeskVault" -Q "ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1024MB )"
Note: In case of SQL 2016 or later, Data file will default to the lower value of 8 or number of logical cores as detected by setup. The value can be increased as required for a specific workload. The file names for secondary data files will follow the tempdb_mssql_#.ndf naming convention where # represents the file ordinal for each additional file.
SQLCMD -E -S ".\AutodeskVault" -Q " ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.AUTODESKVAULT\MSSQL\DATA\tempdev_2.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)"
SQLCMD -E -S ".\AutodeskVault" -Q "ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 2048MB )"
Maintenance Plan
Copy the script below and paste it into a new file saved as VaultMaintenance.bat.
The following is an example of how to run the batch file from the root of C:\ against a database named Vault and output the results to a text file.
C:\VaultMaintenance.bat Vault > results.txt
REM This begins the maintenance plan. @echo off if "%1"=="" goto NOPARAM set VAULTNAME=[%1] set VAULTLOG=[%1_log] @echo Setting %VAULTNAME% database compatibility to 110 sqlcmd -E -S ".\AutodeskVault" -Q "ALTER DATABASE %VAULTNAME% SET COMPATIBILITY_LEVEL = 110" @echo Setting %VAULTNAME% database recovery model to simple... sqlcmd -E -S ".\AutodeskVault" -Q "ALTER DATABASE %VAULTNAME% SET RECOVERY SIMPLE" @echo Setting %VAULTNAME% database Autogrowth value... sqlcmd -E -S ".\AutodeskVault" -Q "ALTER DATABASE %VAULTNAME% MODIFY FILE (NAME=%VAULTNAME%, FILEGROWTH=100MB)" @echo Setting %VAULTNAME% database Log filesize... sqlcmd -E -S ".\AutodeskVault" -Q "ALTER DATABASE %VAULTNAME% MODIFY FILE ( NAME = %VAULTLOG%, SIZE = 512000KB )" @echo Setting %VAULTNAME% database Autoclose to false... sqlcmd -E -S ".\AutodeskVault" -Q "ALTER DATABASE %VAULTNAME% SET AUTO_CLOSE OFF WITH NO_WAIT" @echo Reindexing %VAULTNAME% database... sqlcmd -E -S ".\AutodeskVault" -Q "USE %VAULTNAME% DECLARE tableCursor CURSOR FOR SELECT NAME FROM sysobjects WHERE xtype in('U') DECLARE @tableName nvarchar(128) OPEN tableCursor FETCH NEXT FROM tableCursor INTO @tableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@tableName, '') FETCH NEXT FROM tableCursor INTO @tableName END CLOSE tableCursor DEALLOCATE tableCursor" @echo Updating Statistics on %VAULTNAME% database... sqlcmd -E -S ".\AutodeskVault" -Q "USE %VAULTNAME% Exec sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'" goto EXIT :NOPARAM echo [FAIL] Please indicate Vault database pause :EXIT REM This ends the maintenance plan.