Issue
The three maintenance jobs are failing with the following error:
An error occurred during the execution of the Maintenance task for ION_Network: Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException: The current database does not contain a file named 'tempdev'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The error refers to the tempdev database.
Product Line
Power Monitoring Expert 7.2.x
Power Monitoring Expert 8.x
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023
Power Monitoring Expert 2024
Environment
SQL Server
Cause
Database maintenance jobs use stored procedure. Two of the stored procedures check the size of TempDB by referring to it by its logical name. Since the logical name is modified, the stored procedures fail to find the database. As a result, the maintenance jobs fail.
By default TempDB has one data file (.MDF) and one log file (.LDF), with each having a physical and a logical name. The logical file name is tempdev as shown below:
TempDB can be modified to include multiple data and log files. As such, each file will have a unique logical name. Below is an example were TempDB consists of four data files and one log file:
| Logical File | Physical File |
|---|---|
| tempdev_primary_01 | tempdb_primary_01.mdf |
| tempdev_primary_02 | tempdb_primary_02.ndf |
| tempdev_primary_03 | tempdb_primary_03.ndf |
| tempdev_primary_04 | tempdb_primary_04.ndf |
| tempdb_log_01 | templog.ldf |
As can be seen, the logical file names have changed.
Resolution
Warning: Irreparable database damage can occur. This procedure should only be performed by DBAs or users familiar with SQL Server Management studio with extensive knowledge of database structure. Databases should be backed up before performing this procedure.
Warning: Disabling this check could lead to drives filling up. Please make sure there will always be sufficient space prior to eliminating this check
Since the code within the software which refers to tempdev, cannot be modified, there is no resolution. However, there is a workaround which requires eliminating the TempDB size check.
For each of the three ION databases, modify these two stored procedures:
dbo.DefragIndexAll
dbo.UpdateStatisticsAll
By commenting out the following line using '--' at the beginning of the line:
EXEC ChkTempSpace
Here is an example showing the line has been commented out:
