DTM Database Options and Maintenance Best Practices
On This Page
Database Options for the DTM Controller
When you install the DTM Controller with its default configuration, the database it uses for data storage is Microsoft SQL Server Desktop Engine (MSDE). It is important to note that MSDE has a database size limit of 2 GB. As a result, adhering to best practices for backing up and restoring databases can ensure that the data storage space in DTM Controller is managed efficiently when DTM Controller is configured to use MSDE.
As an alternative to using MSDE, Microsoft SQL Server 2000 SP3a can also be used. For more information about this version of SQL Server, see SQL Server 2000.
Remember that if you decide to install the DTM Controller with MSDE and later decide to switch your datastore to another version of Microsoft SQL Server, the recommended approach would be to:
1. | Back up your datastore by using the procedures described later in this document. |
2. | Uninstall DTM Controller. |
3. | Install the version of Microsoft SQL Server that you want to use. |
4. | Reinstall DTM Controller. |
5. | Restore the data that you backed up in step 1. |
Important: If you install Microsoft SQL Server 2000 on a PC running Windows with a non-English language pack enabled, the DTM installation procedure will fail. A workaround for this issue is now available. Refer to the Install.htm file in the Windows Driver Kit (WDK) for information about installing Microsoft SQL Server 2000 to use with DTM.
MSDE Database Maintenance Best Practices
Because of the 2-GB size limit of the MSDE database, it is important to perform regular maintenance so that you can remove and archive old data as well as back up and restore the database. Each time a set of logo submission tests is run, the database increases in size. For example, a typical logo submission for a video display adapter increases the size of the database by approximately 18 MB.
For use with the DTM Controller, database maintenance is limited to backup and restore operations. You should determine how often you will back up and restore your data based on your usage of the DTM suite of products and your company policies.
When the database size limit of 2 GB is reached or exceeded, MSDE is halted, no further jobs are scheduled, and a record similar to the following is written to the system event log:
*************************System Event Log**************************************** Event Type: Error Event Source: Ntfs Event Category: Disk Event ID: 55 Date: 1/14/2006 Time: 12:46:11 AM User: N/A Computer: 3H1O-X8 Description: The file system structure on the disk is corrupt and unusable. Please run the chkdsk utility on the volume WTTSYSTEMLOGS. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 00 00 04 00 02 00 52 00 ......R. 0008: 02 00 00 00 37 00 04 c0 ....7..A 0010: 00 00 00 00 02 01 00 c0 .......A 0018: 00 00 00 00 00 00 00 00 ........ 0020: 00 00 00 00 00 00 00 00 ........ 0028: 17 0b 14 00 ....
Database Maintenance: Backing up Data
Database backup and restore operations are used to recover from catastrophic failure, either in hardware or software. These operations are run against the entire database and are not recommended for archival purposes. The DTM database schema is not published, which prevents the use of backup and restore for database archiving. SQL Server 2000 comes with database maintenance tools with which you can back up and restore SQL databases. MSDE ships with Osql.exe, a command-line tool that you can also use to back up and restore data. In addition, many third-party tools are available for SQL database maintenance.
The following procedure shows how to back up an MSDE database by using Osql.exe:
To back up the MSDE DTM database
Note: The database default name is DTMJobs. If you chose another database name when you installed the controller, substitute that name for DTMJobs in the following procedure.
1. | Close all studios connected to this controller. |
2. | Open a command prompt window on the controller. |
3. | Type the following: |
4. | Press ENTER. |
Database Maintenance: Restoring Data
To restore the MSDE DTM database
1. | Close all studios connected to this controller. |
2. | Stop the following services on the controller: |
3. | Stop the MSSQLSERVER to clear any pending interaction. |
4. | Restart the MSSQLSERVER so Osql.exe can access the database. |
5. | Open a command-prompt window. |
6. | Type the following: |
7. | Press ENTER. |
When you have finished, restart the controller.
Notes:
| • | If you restore the database, you will lose any changes that were made to it since the last backup. |
| • | If you try to restore the database and get the error message "Exclusive access could not be obtained because the database is in use," a WTT service is still running or an instance of DTM Studio is still accessing the controller. |
You can find more information about using OSQL from the following articles:
| • | |
| • | |
| • | How to back up a Microsoft Data Engine database by using Transact-SQL |
Database Maintenance: Archiving Data
Archiving a database involves removing (exporting) data from the database to preserve the data in an archival form. There are two methods for exporting data; both methods are presented here.
| • | Exporting data by using Wttcl.exe Wttcl.exe is a command line tool that is shipped with the DTM and is found in the Studio directory on any computer that has DTM Studio installed on it. Wttcl.exe includes an Export Results command that can be used to export data from the DTM database. Exporting data does not delete it from the database, and the log files associated with the data are not exported when using Wttcl.exe. However, with this method of exporting data, the data is exported in such a way that it can be reimported into the database. The following is an example of a command for exporting all the test results in a particular feature path: wttcl exportresult /IdentityServer:Controller_Mach where the machine name of the controller is Controller_Mach, the name of the database is DTMJobs1, the name of the category to export results from is $\WDK, and the path to save the exported results is C:\Results. The exported data (the test results) can be reimported into the controller by using the Wttcl.exe tool. You can find additional information about Wttcl.exe in the DTM documentation or by typing the following in a command prompt window, with your directory set to the Studio folder: To save the logs that are associated with the exported data, navigate to the shared log directory on the controller and then copy or back up the desired logs. |
| • | Exporting data by using the Results Export add-in An add-in will be made available later on a Microsoft Web site, at which time this document will be updated with the URL to the download site. With this add-in, you can select and export results and all related data, including machine configuration logs and test logs. Results exported with the add-in cannot be reimported into the controller, but they can be viewed by using the viewer that is included with the add-in. You can view the results exported with the add-in without DTM Studio or DTM Controller installed. |
After results have been archived, it is possible to delete the results from the database by using the results viewer that is included with DTM. The results viewer supplies a query builder that you can use to select the results that you want to view or delete. You delete results by right-clicking the results, and clicking Delete. This process does not delete the associated logs or the jobs that created the results.
To delete the logs, you must navigate to the shared log folder on the controller and manually delete the logs.
Database Maintenance: Shrinking the Transaction Log
It is possible for the MSDE transaction log to grow until it consumes all available disk space; however, you can shrink the transaction logs to avoid this.
Note: The database default name is DTMJobs. If you chose another database name when you installed the controller, substitute that name for DTMJobs in the following procedure.
To shrink a transaction log
1. | Close all studios connected to this controller. |
2. | Run a BACKUP LOG DTMJobs to DTMJobsBackup command, and then run a DBCC SHRINKFILE (DTMJobs_log, 2) where you want to shrink the transaction log to 2 MB. |
3. | Open a command prompt window on the controller, and then type the following: |
4. | Press ENTER. |
For more information about shrinking transaction logs, see the following knowledge base article: http://www.support.microsoft.com/?id=272318