Database maintenance jobs failing with error: The current database does not contain a file named 'tempdev'

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:

게시 대상: 슈나이더 일렉트릭 Korea