This fourth article in "SQL Server management with WMI" series provides examples of using WMI in common maintenance tasks, such as truncating the database transaction logs, or updating statistics and rebuilding indexes on database tables.

Truncating the database transaction logs removes the inactive portion (containing transactions committed to the database). This typically happens as the result of the transaction log backup, or on every checkpoint, when the simple recovery model is used, (Truncate log on checkpoint database option is ON); but it can also be forced (e.g. by using T-SQL command BACKUP LOG TRUNCATE_ONLY or by the method presented here). Truncating the transaction log helps to keep the size of the log under control and negatively impacts the database up-to-point recoverability. Do not truncate transaction logs when performing transaction log backup as part of the recovery strategy. If it is decided to truncate the transaction logs using the method presented here, immediately follow it with a full database backup. Note also that truncating transaction logs does not necessarily affect the size of the physical file containing the transaction log - it simply clears transactions contained within the file, leaving space for new ones. Truncate is the method of MSSQL_TransationLog class. To execute it, specify the name of the SQL server, SQL server instance, and the target database.

The next two methods belong to the MSSQL_Table class and deal with commonly performed maintenance tasks on a table level. The first allows rebuilding all indexes for a specific table. Rebuilding indexes is recommended after operations involving large amounts of data (such as bulk inserts), but should also be included as part of regular maintenance procedures. The RebuildIndexes method of MSSQL_Table class is equivalent to the DBCC DBREINDEX statement (in fact, it uses it directly to perform the reindexing). To run the sample script, specify the name of the SQL server, SQL server instance, target table and the database. You also need to provide the value of the fill factor that will be used when rebuilding indexes. This integer value, (between 1 and 100), controls the percentage of space occupied by index data on each index page, (called index density), that is taken into consideration when the index is rebuilt initially. Using a small value will prevent frequent index page splits when adding data to the table, which effectively speeds up INSERT operations. However, it will increase amount of space used by the index pages at the same time. Using a large fill factor value will have the opposite effect. Note that the RebuildIndexes method recreates all indexes on a table. This might be computationally expensive, (in terms of both processor utilization and time), so schedule this process according to the business requirements. In case you want to limit the scope to just one specific index, use the Rebuild method of MSSQL_Index class. WMI offers also RebuildIndex methods in MSSQL_PrimaryKey and MSSQL_UniqueKey classes.

The third method presented in this article is the UpdateStatistics of the MSSQL_Table class. Query optimizer uses the statistics on the data distribution across table indexes. Accuracy of these statistics significantly benefits query performance. Just as with rebuilding indexes, updating statistics should be performed after operations involving large amounts of data, but it should also be part of regular maintenance procedures. The WMI method is equivalent to the UPDATE STATISTICS T-SQL statement (in fact, it executes it behind the scenes). To run the sample script, specify the name of the SQL server, SQL server instance, target table and database. In case you want to update statistics for a particular index only, use the UpdateStatistics method of MSSQL_Index instead.

As you can see, functionality of WMI can be utilized to perform standard SQL server maintenance tasks. I will continue this topic in the next article of this series, covering methods (such as UpdateStatistics and Rebuild of MSSQL_Index class), only briefly mentioned in this article .