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.
   
   

1.2. Setting a Windows Environment Variable

1. In the Windows menu, click the search prompt .
2. In the Search bar, type "environment variables".  
3. Select Edit the System Environment Variables option.
   
   
   
4. Click the Environment Variables button, if the option is available.
   
   
   
5. In the User Variables for <user name> section, click the New button.
   The New User Variable prompt dialog is displayed.
   
   
   
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

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.