---
title: "Maintenance Appendix"
slug: "maintenanceappendix"
updated: 2023-10-11T05:26:48Z
published: 2023-10-11T05:26:48Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://help.12dsynergy.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Maintenance Appendix

### 1.1. How to Find the File Logs

When you can't connect to the server, and have to find the file logs, do the following:

1. Open **SQL Server Management Studio**.
2. Connect to the **12d Synergy Database**.
3. Right-click the table **dbo.Setting**.
4. Select the **Edit Top 200 Rows** option.
5. Find the setting named **FileLogPath**.  
The location of the File Logs is the location mentioned in the **Value** column corresponding to the setting **FileLogPath**.  
![](https://cdn.document360.io/36833d89-59e3-46a5-b491-e9aa1b524a85/Images/Documentation/v5maintenanceappendixv5maintenanceappendix.doc-image-3zpfpo76.png)

### 1.2. Setting a Windows Environment Variable

1. In the **Windows** menu, click the search prompt ![](https://cdn.document360.io/36833d89-59e3-46a5-b491-e9aa1b524a85/Images/Documentation/v5maintenanceappendixv5maintenanceappendix.doc-image-8qtq2p40.png).
2. In the **Search** bar, type "**environment variables**".
3. Select **Edit the System Environment Variables** option.  
![](https://cdn.document360.io/36833d89-59e3-46a5-b491-e9aa1b524a85/Images/Documentation/v5maintenanceappendixv5maintenanceappendix.doc-image-b0vqeoc1.png)
4. Click the **Environment Variables** button, if the option is available.  
![](https://cdn.document360.io/36833d89-59e3-46a5-b491-e9aa1b524a85/Images/Documentation/v5maintenanceappendixv5maintenanceappendix.doc-image-rnp0rj89.png)
5. In the **User Variables for <*user name*>**section, click the **New** button.  
The **New User Variable** prompt dialog is displayed.  
![](https://cdn.document360.io/36833d89-59e3-46a5-b491-e9aa1b524a85/Images/Documentation/v5maintenanceappendixv5maintenanceappendix.doc-image-8xyj507s.png)
6. Enter the name and value of the variable to be created in the **Variable name** and **Variable value** boxes respectively.
7. Click the **OK** button.
8. Close all the related windows.
9. Restart the relevant application, as Environment Variables are only loaded when the application starts.

### 1.3. Database Fragmentation Queries

#### 1.3.1. Determining Fragmentation Levels

In general, highly fragmented tables are a problem only when there are more than 500-1000 pages. To view all tables with over 75% fragmentation and more than 5000 queries, run the following query in SQL Server Management Studio.

SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() ORDER BY indexstats.avg_fragmentation_in_percent desc

#### 1.3.2. Cleaning up Fragmentation

- You should do this activity out of working hours when there is no activity. This will avoid deadlocks or any other issues that may arise due to the cleanup.
- This is a brute force mechanism to rebuild indexes on all tables, and may not be ideal for your specific rollout.
- For larger enterprises, we highly recommend that you consult your DBA before running this query.

The following query can be used to rebuild your indexes, but may take some time.

**exec sp_msforeachtable ‘ALTER INDEX ALL ON ? REBUILD;'**

**exec sp_updatestats** **DBCC FREEPROCCACHE**

#### 1.3.3. Backing up and Truncating the Transaction Log

As per Microsoft Guidelines, we recommended the following backup schedule:

1. One FULL backup at midnight every day
2. One DIFFERENTIAL backup at noon every day
3. A transaction log backup with truncation every 30 minutes.

To achieve this, you will need a scheduling tool. Either a third party database backup tool, or SQL Server Agent for Standard Enterprise editions of SQL Server can be of help.