Microsoft SQL Server Database Maintenance activity

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 to the SQL server.

a.                  Expand Databases.

b.                 Right-click on the database you want to back up, then select Tasks > Back up. (This is not available for version 2018/2019)

c.                  On the Back Up Database window, make sure the Database field contains the name of the database you want to back up.

d.                 Select the Backup Type. By default, it is Full - leave it set to that.

e.                  Click Remove to remove the default/last backup file name.

f.                    Click Add to open the Select Backup Destination window.

g.                  Click [...] next to the File Name field.

h.                  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.

i.                    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.

j.                    Click OK to close the Locate Database Files window.

k.                  Click OK to close the Select Backup Destination window.

l.                    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.

iVend POS - Purge Data to Reduce iVend Store database size

Introduction

The Data Purging feature in iVend helps the Administrator to control the size of the iVend database at each store. Data purging at stores is controlled centrally and is managed from the iVend Enterprise at Head Office. The Administrator can also view the status of the data purge that is carried out at the store. Once data purge activity is scheduled and triggered from the iVend Enterprise the actual data purging occurs at the store when the Store Manager runs the End of Day routine.

Purpose

To help the Administrator to control the size of the iVend database at each store.

Purging Data

In the following article, we will see how to setup and use the Purge Data functionality in iVend retail.

Figure – Data Purge Search

1. Log into the iVend Management Console and go to Administration > IT Administration > Purge Data.

 This option is only available at the iVend Enterprise Level.

 

2. By default, the list of created Data Purges that are scheduled are displayed. The user can search from existing Data Purges that are scheduled by entering the Schedule Date and pressing the [F1 Search] button.

3. To create a new Scheduled Data Purge for a store, press the [F3 New] button.

Figure – Data Purge Screen

4. This feature enables the Administrator to start the data purge activity at the stores. Using this, the System Administrator can generate an event for performing a data purge at the store.

5. This feature is generally used in the scenarios where the stores are using the SQL Server Express Edition. Due to size limitations of the SQL Server Express Edition database, the Administrator should keep the store database size in check and therefore this activity is recommended to be performed at regular intervals.

This menu option is only available at the iVend Enterprise. Using this option would delete the transactional information from the store database.

 

Field

Description

Purge Transaction Before

Enter the date for which the data needs to be purged. The earlier transaction data will be purged from the date defined.

Clear Audit Log

Audit log data will be purged when this checkbox is checked.

[Add Store]

Use this button to choose an individual store for which the data purge needs to be run.

[Add All Stores]

Selecting this button will add all the stores in the network and the data purge will be run for all of them.

[Delete Store]

Choose this button to remove a selected store from the data purge screen. Any store not showing on the data purge screen will not have a data purge performed on it.

StoreID

Displays the Store Id for which the purge will be run.

Run Date

Shows the date on which the purge will be run.

[Start]

Clicking this button will start the data purging process for the selected stores.

[Cancel]

Clicking this button will Cancel the changes made, the data purge will not be performed and the window will close.

 

7. Once the information has been entered, click the [Start] button. Please remember that once this is done, the information entered here in the Purge Data window cannot be edited or deleted.

8. The Purge Data Schedule will be replicated to all the retail stores that are included in the run. The Data Purge process will automatically run when Store Manager runs the End of Day process

9. Once the Data Purge run has been successful at the stores, the transactional data will no longer be available.

10. The Administrator can view the last successful Data Purge Run Date at the Enterprise Management Console.

 

    • 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 ...
    • SQL server 2016 : Database Mirroring for High Availability

      Query: Customer is planning to set up Database Mirroring for High Availability in his test environment. In SQL 2016 version is this supported? Response: Database High availability is the concept related to RDBMS and not with iVend application. Below ...
    • Methods of back up of Enterprise Server SQL Database

      Version: Not any specific version. Query: Kindly advise methods of backup of Enterprise Server SQL database. Answer: We can only take SQL backup from SQL Server only for enterprise databases. We don't have any utility to take an enterprise backup. ...
    • Limit SQL SERVER memory consumption

      Version: Not the version-specific Query: Below query has been raised by one of the iVend user. I am experiencing in my HO server a lot of memory consumption when I checked it was the SQL SERVER, so I want to ask you if I can limit it, defining in SQL ...
    • Found error while login MC "Error encountered found in application"

      Environment: 6.6.7989 Short Description: Error found in the application Product Query:  While login in Management Console found error “Error found in the application” Resolution/Work Around: Not Applicable Recommendations /Suggestion: Ivend Database ...