The Dreaded Transaction Log

Why oh why does MIIS have such an insatiable appetite for transaction log space? I’m afraid I can’t answer this, but I can share a few tips.

In a reasonably stable system, without great quantities of new data being added, the trans log for the MIIS database will alledgedly stay at a stable size. Of course this size may be gigabytes more than anyone was prepared for – but I’m afraid there’s no fighting it. You can shrink it, but it will only grow back to the same size, and you may well notice a performance hit while it’s doing that.

The Trans Log and Groups

The worst situations that I’ve got into have involved importing large groups. Just today I watched a trans log climb from its usual 3GB to over 27GB while importing a mere 25 groups (although it must be said some of them had 10k members). The space-grab only seems to happen on the first import-sync – once MIIS has all the groups and placeholders safely imported you can return to a normal allocation.

This post on the ILM forum also shows the same thing happens with group deletions. At least I didn’t need 250GB!

SQL Backups

The main defense against the trans log is regular backing up of your MIIS database. If you also set the database recovery mode to “Simple” the trans log will be truncated at each backup – while it won’t actually make the log file any smaller it should now contain a lot of whitespace and can be shrunk if desired.

Simple recovery mode means you can only restore the database to the last full backup. Apparently the Full Recovery mode allows a lot more flexibility in the point in time you can restore to – but I don’t know why anyone would bother for an MIIS database. If you do need to restore the db you would always follow up with Full Imports from your connected directories, so pin-pointing a particular time to restore to is not nearly as critical as keeping your diskspace under control during normal operations.

Note that the MIIS database is installed with Full Recovery mode as default – you will need to change it to Simple.

A script to shrink the trans log

All that said, sometimes emergencies happen and you need to forcibly squish the trans log into a reasonable size – or it’s not even an emergency, but you want to trasfer a backup to another server that isn’t stupidly large. This script will get your trans log right down, just until you resume running MIIS jobs anyway.

USE MicrosoftIdentityIntegrationServer
DBCC SHRINKFILE(MicrosoftIdentityIntegrationServer_log, 1)
BACKUP LOG MicrosoftIdentityIntegrationServer WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MicrosoftIdentityIntegrationServer_log, 1)