Maintenance Appendix

1. Finding the File Logs

If you can't connect to the server, and need to find the file logs, you should follow these steps:

1. Open SQL Server Studio Manager
2. Connect to your 12d Synergy Database
3. Find the Settings table
4. Right click to select top 1000 rows
5. Find the row called FileLogPath

1.1. Setting a Windows Environment Variable

To add a Windows Environment Variable, follow these steps:

1. Go to the Start Button in Windows
2. Type in: Environment Variables
3. Select Environment Variable for the User
4. Click Environment Variables, if the option is available
5. In the "user Variables" section, click new
6. Enter the required settings
7. Click OK until all windows are closed
8. Restart the relevant application, as Environment Variables are only loaded when the application starts

2. Database Fragmentation Queries

2.1. Determining Fragmentation Levels

In general, highly fragmented tables are only a problem where there are more that 500, or in some cases, 1000 pages. The following query will show tables with over 75% fragmentation and more than 5000 queries. This should be run in SQL Server Management Studio.

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

2.3. Cleaning up Fragmentation

The following query can be used to rebuild your indexes, but may take some time. It should be run out of hours to avoid deadlocks or other issues. Note that 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 recommend you consult with your DBA first.

exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD;'
 exec sp_updatestats
 DBCC FREEPROCCACHE

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