XenDesktop SQL Server Mirror transaction log Maintenance

when using SQL Server High availability features, for example,the XenDesktop site database uses the full transaction logging mode to run database mirroring. transaction logs that run through the full transaction logging mode grow too large until the database space is filled or the transaction log space size is filled. If the transaction log files are not monitored,the configuration log file for SQL Server automatically grows by default. This will cause 2 problems:

1. Transaction log files can consume a lot of disk space

2, the thing log growth fills the database space, causes the database to be unavailable.

so Citrix recommends that you back up log files regularly. This can be done through a dispatch job or a maintenance plan. In addition, you can use SQL Server Agent to monitor the size of log usage beyond the specified thresholds and run backup jobs.

We can do this, when creating a site database mirroring, you can set the size of the database log, such as setting the maximum log is 4GB. Creates a new alert that is set to a maximum of a log file when the log is backed up to another file . Stop log growth, use scripts to consume empty sessions, and so on, and stop it from zeroing in on disk space and delaying the database. To achieve the purpose of truncating the transaction log.

therefore, the Mirror The maintenance of the transaction log, we can do this:

according to Citrix Best Practices,Citrix recommends changing the heartbeat settings before the DDC to reduce the transaction log growth too fast. The heartbeat between the DDC defaults to a one- second communication, which is recorded in the transaction log and generates approximately 6060 bytes.

Citrix recommends that you change the default heartbeat timeout when you use database mirroring. You can do this by changing the registry settings.

these two settings must be changed to be stored in the HKEY_LOCAL_MACHINE \software\citrix\ Desktopserver

These settings are:

HEARTBEATPERIODMS- The time interval to control the heartbeat. How often to communicate once. Citrix recommends setting it to ten minutes.

maxheartbeatintervalms– Specifies that the maximum heartbeat time can be used without communication, which is not configured by default.

Citrix recommends that you use the full recovery model. It is recommended that you set a fixed-size transaction log and a SQL warning about the transaction log so that when the transaction log reaches a maximum ofor 80% , the transaction log is automatically backed up and the previous empty data is freed.

Use a fixed-size transaction log

sets the size of the transaction log, which prevents it from filling up disk space. It also has the advantage that the transaction log is pre- zero ' d and does not grow automatically.

New Alert

log in to the database and select Alerts, new , SQL Server agent . Create an event alarm, in this case we activate the alert by using a database log usage rate exceeding 80% .

Type: SQL Server Performance Condition Alert

object: Mssql$ctxdb01:databases

Counter: Percent Log used

Example: Xd

Count: above 80

Select the left Response, tick"Execute job" on the rightand click "New Job". In theNew Jobdialog box, fill in the job name:xd-log-alert-response-job. Click "Steps" on the leftand click"New ..." on the right.

in the popup dialog box, enter the following information and click OK.

Step Name: xd-log-alert-response-job-step1

Type: tnsact-sqlscript (t-SQL)

Database: XD

Command: Backup Log [XD]to Disk = ' C:\CitrixXenDesktopDB-Transactionlog.bak ' with Noformat,noinit, COMPRESSION, NAME = N ' transcation Log backup ', SKIP, Norewind, Nounload, stats=10;

NOTE: * * * for the name of the XD database.

　　run after the completion of the error, no error to prove the success of the set, to C disk to see if a backup was created. If the backup appears, then the proof can be automated database log truncation, we click on the database to switch, the standby database to the main database, create a new same alarm, after successful cut back.

This article from the "I Take fleeting chaos" blog, please be sure to keep this source http://tasnrh.blog.51cto.com/4141731/1435200