Technical FAQs

Ask a Question

Splitting out the Transaction Log and Database Files in ION_DATA Database

Issue
It's a common practice to split out the mdf and the ldf files associated with the ION_Data database to improve performance and increase reliability 

Cause 
The database files and the log files have a different I/O patterns because transaction logs are typically sequential writes while data files are mostly random reads and writes. Separating the mdf and the ldf files on different physical drives will reduce I/O contention and hence improve performance. Moreover, placing the files on different locations but on the same physical drive will exhibit no performance gain.

Product Line
PME 7.2.2

Environment
SQL Server Management studio

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

This article discusses the two methods that can be utilized to move and separate the database files.  Both of these methods will achieve the same end result if the following steps are correctly executed:

The detach/reattach method:
  • Stop all the ION services from the services ( Stopping the ION Network Router service first will stop several other services at once)
  • Launch the Microsoft SQL Server management Studio and connect to the Sql server
  • Expand the databases tree, right click on the ION_Data database and then detach database as show below
    ​                             
 
  •  Move ION_Data.MDF and the ION_Data.LDF files to their prospective new location. It's advisable to copy and paste the files instead of cutting and pasting. Once the files have been successfully transferred, you can delete the files from the old location.
  • Attach the MDF file as shown below and then change the current file path of the ldf file to match your new location.
                                                               
 
  • Execute the following SQL script to verify the new location.
                                                               
 
  •  Restart the ION services and verify that PME's components are functioning
The online/offline method
  • Stop all the ION services from the services ( Stopping the ION Network Router service first will stop several other services at once)
  • Launch the Microsoft SQL Server management Studio and connect to the Sql server
  • Set the ION_DATA database offline by running the following SQL script
                                                              
 
  • Move ION_Data.MDF and the ION_Data.LDF files to their prospective new location
  • Execute the following script be careful with the NAME as it is case sensitive; ION_Data_Data for the mdf file and ION_Data_Log for the LDF file. The FILENAME should correspond to your new location path.
                                               
 
  • Set the ION_DATA base online and then check for integrity by running the following scripts
                                               
                                              
 
  • Execute the following SQL script to verify the new location and restart the ION services
                                                   
The SQL scripts are attached in a .txt file
Was this helpful?
What can we do to improve the information ?