Issue Reported: HP Connected Backup Server gets hung and will work for some days after the reboot.

Findings:

Verified the event logs and suspected the issue with SQL Database. So ran the below mentioned Query on the SQL Server,

Start

use Directory SELECT OBJECTSCHEMANAME(ps.objectid) 'schemaname', OBJECTNAME(ps.objectid) 'objectname', si.name 'indexname',ps.* FROM sys.dmdbindexphysicalstats(DBID('Directory'), null,null,null,'DETAILED') ps LEFT OUTER JOIN sys.indexes si on ps.[objectid]=si.[objectid] and ps.indexid=si.indexid WHERE ps.avgfragmentationin_percent>30

End

The output shows some of the indexes are fragmented more than 90%. Since the Fragment rate was striking to above 90 % which should to be under 30% which made the server to act sluggish with timeout error when query something.

Workaround:

• We had done the manual done the re-indexes of the application after taking the application offline by following the below Microsoft KB, https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017 To rebuild an index from SQL Management,
  
• In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
• Expand the Tables folder.
• Expand the table on which you want to reorganize an index.
• Expand the Indexes folder.
• Right-click the index you want to reorganize and select Rebuild.
• In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.
• Click OK. Even after the Index rebuilt, the issue happened very frequently & understand that the Database maintenance like rebuilding or reorganizing the indexes needs to be done regularly. This should not be a once time task. So In this situation, we need to schedule rebuild of the indexes every other day. Note: Scheduling the Re-build indexes with SQL Standard database will bring the application down and so we will require SQL Enterprise edition for online indexing.

Published By : Jenifer John Published on : 31-Aug-2018