Issue
The three maintenance jobs 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 tempdev database.
Product Line
PME, SQL Server
Environment
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:
As can be seen the logical file names have changed.
Cause
Database maintenance jobs use stored procedure. Two of the stored procedure 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.
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 prior to performing this procedure.*
Since the code within the software which refers to tempdev cannot be modifed, there is no resolution. However, there is a work around which requires to eliminate 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:
The three maintenance jobs 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 tempdev database.
Product Line
PME, SQL Server
Environment
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.
Cause
Database maintenance jobs use stored procedure. Two of the stored procedure 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.
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 prior to performing this procedure.*
Since the code within the software which refers to tempdev cannot be modifed, there is no resolution. However, there is a work around which requires to eliminate 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:
Released for:Schneider Electric USA
