Database Maintenance Guidance

Relates to versions - 3.0.0+

This procedure has been created to give an overview of the details required to bring the services offline for database index maintenance. Timings should be modified to suit site/CAB requirements.

Overview:

• Communicate Outage to end users
• Create and action Change Advisory Board (CAB) documentation where required
• Connect to server infrastructure and copy required tools needed to complete maintenance
• Communicate and commence outage
• Maintain databases
• Conclude outage
• Perform functional testing, communicate results

Steps:

1. Contact Mine Control to arrange an application outage.
   • Historically maintenance takes between two to four hours.
   • Control room operators can still use Data Acquisition to record data but must not close it while the services are stopped. Event Editor and the Pitram Portal will be unavailable for the duration of the outage.
2. Arrange CAB requirements. Timing and notification requirements take precedence over any suggestions made hereafter.
3. Prior to beginning, connect to the Pitram Application server and open the Pitram Server Management Tool and an elevated command prompt. Also copy the RebuildAllIndexes.sql and IndexStats.sql scripts to the application server.
4. Contact the Control Room to advise commencement of outage.
5. Open the Pitram Services tab:
   • Press the ‘Connect’ button
   • When the Services appear, press ‘Stop All’

6. Open the Dome Services tab:
   • Press the ‘Connect’ button
   • When the Services appear, press ‘Stop Service’

7. Switch to your elevated command prompt and issue an IISRESET /Stop command:

8. Open ‘Services.msc’ from a run prompt:
   • Locate the Pitram Watchdog service
   • Double click the service to open the properties
   • Change the Startup type to Manual
   • Press ‘OK’
   • Locate the DOMEConnector Service
   • Double click the service to open the properties
   • Change the Startup type to Manual
   • Press ‘OK’
   • Minimise the Services window

9. Use SQL Server Management Studio to execute the IndexStats.sql script against the following tables to obtain a baseline value for index fragmentation*. The results should be stored for comparison.
   • DOMEProduction
   • PITRAMServer
   • PITRAMReporting
   • DOMEWarehouse

*These databases may be named differently according to site naming conventions but will generally always include these terms. If clarification is required, please contact your local Pitram support team to assist.

12. Run the RebuildAllIndexes.sql script against the databases listed above. Once the maintenance is complete, Execute the Index Statistics script again to confirm tables have been re-indexed and the values are lower than the previous values.
13. When the Index Rebuild is complete, run IndexStats.sql again to capture new fragmentation results. Compare to previous results to confirm successful reduction in index fragmentation.
14. Restart the Pitram Services:
    • Change the Pitram Watchdog Service and DOMEConnector services Startup type to ‘Automatic’
    • Issue the IISRESET /Start command in the elevated command prompt
    • Open the Pitram Server Management Tool to the Dome Services page and start the DOME Connector service
    • Open the Pitram Services page and press ‘Start All’
15. Test the Pitram Portal and Data Acquisition is operational
    • Open a browser and navigate to the Pitram Portal website
    • Log-in using an Administrator account
    • Open the Services page and ensure the Queue is processing events
    • Open Data Acquisition and ensure the Service indicators are green
16. Inform the control room that the system is operational.
17. Follow close out CAB procedures where appropriate.

--IndexStats.sql
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
indexstats.fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

--RebuildAllIndexes.sql
Exec sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?',  '  ' , 80)"
GO
Exec sp_updatestats
GO

Want to learn more?

Click here to login to our Learning Management System

Click here to request access