Database health check & Optimization steps

Database health check & Optimization steps

Steps and walkthrough to do a health check and optimization of iVend database.

The entire process broadly consists of 8 steps:

Step 1: Take Backup of DB.        

Open SQL Server Management Studio Express and connect to the SQL server.
  1. Expand Databases.
  2. Right-click on the database you want to back up, then select Tasks > Back up. (This is not available for version 2018/2019)
  3. On the Back Up Database window, make sure the Database field contains the name of the database you want to back up.
  4. Select the Backup Type. By default, it is Full - leave it set to that.
  5. Click Remove to remove the default/last backup file name.
  6. Click Add to open the Select Backup Destination window.
  7. Click [...] next to the File Name field.
  8. On the Locate Database Files window, select the folder where you want the backup file to go. By default, it is ..\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
  9. In the File Name field, type the name for this backup, with a .bak extension. For example, xyz_20080221.bak for a backup of the XYZ database created on 21 February 2008.
  10. Click OK to close the Locate Database Files window.
  11. Click OK to close the Select Backup Destination window.
  12. Click OK to start the backup. The progress icon displays in the lower-left corner, and a ‘completed successfully’ message displays when it's done.

Step 2 : Check allocation and structural integrity of all the objects.       

"DBCC CHECKDB“
Note: If the health check returns any errors, then please contact your DBA immediately.

Step 3 : If above Query is Successful then run below Query to check Fragmentation of Major tables

"SELECT  top 10 OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
WHERE indexstats.avg_fragmentation_in_percent > 30
AND i.index_id = indexstats.index_id
ORDER BY 3 desc"

Step 4: Check database size  

"SELECT DB_NAME(database_id) AS DBName,Name AS Logical_Name, Physical_Name,(size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'CXSRetail66' --databasename"

Step 5: Shrinking the database


In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Expand Databases, and then right-click the database that you want to shrink.
Point to Tasks, point to Shrink, and then click Database.
Database - Displays the name of the selected database.
Current allocated space - Displays the total used and unused space for the selected database.
Click OK

Step 6: Keep the Recovery model - Simple on Retail stores.


Step 7: Check SQL Server Version         

“Select @@VERSION“       
 

Step 8: Optimization

Run the following command for database optimization
"Exec sp_updatestats
Exec     sp_databaseoptimization“
Suggestion: Kindly take SQL Database backup on daily basis to avoid any unseen issues in future.
Note : it is suggested to perform such above activities in downtime with help of IT Team or SQL DBA when no one is working on iVend databases. Incase if anyone using SQL Express please check the DB size after optimization to avoid 10GB crossing limit. As SQL Express cannot grow more than 10 GB and it will stop processing data on reaching 10 GB limit, this is default behavior of MS SQL.


    • Related Articles

    • Cannot open database 'CXSIntegration' login failed

      Environment: iVend 6.6 Problem Statement: Cannot open database 'CXSIntegration' login failed Symptoms: Environment Specific Resolution/Work Around: It seems wrong SQL password is entered for user "sa". So need to check correct password for SQL login ...

    • ISSUES WHEN INTIALIZING MASTER TABLES FROM SAP: "iVend 6.*.*.* add-on failed to update the database"

      Product version 6.5 Update 6 Problem statement Getting Error "iVend 6.*.*.* add-on failed to update the database" when initializing master tables from SAP Reason CXSINTEGRATION DATABASE IS DELETED SAP database, already initialized with iVend database ...

    • Steps to check the issue : 'Database size increase

      Scenario : If iVend database size is high in size, suggest to check the following information : Steps : 1) Where is the user getting this abnormal data in size (Enterprise or Store) 2) What is the SQL server version. (Express edition has a limit and ...

    • iVend API Test Call Error : iVend API health Check failed

      Problem Statement iVend API health check failed due to iVend API Test Call Error . Error Screenshot as per below Investigation Steps 1. First Copy the API url and make complete URL as per below Screenshot ...

    • Microsoft SQL Server Database Maintenance activity

      Database health check & Optimization steps Steps and walkthrough to do a health check and optimization of iVend database. The entire process broadly consists of 8 steps: Step 1: Take Backup of DB. Open SQL Server Management Studio Express and connect ...