SQL Recovery
General
VMware AirWatch Installation Guide (9.2), page 17 recommends Recovery Model = Full.
Also, in AirWatch SQL FAQ we see a comment: When trying to login to the AirWatch console or perform actions within the console you may receive the error message “The transaction log for database ‘AirWatch’ is full.”
This typically means that there is no available space on your SQL server. The common cause for this is backups not being deleted even though newer backups are being done, or lack of transaction log backups. To fix this issue you should first consult your local database admin and make them aware of the issue. The fix that we recommend is to perform a full database backup and then switch the database to simple recovery mode. Your database admin should be familiar with this and know how to perform these actions.
Shrinking the Log File When Disk Space is Full
The log file cannot be shrunk until it is truncated and free space is available. If a log file has grown to the point that it is full or consuming all available disk space, truncate it immediately in order to restore normal database operations. (If the log file has reached the maximum size database property and free disk space is still available, increase the value of maximum size property or set it to ‘unlimited’. A shrink operation should not be required in this case.)
To truncate a log file immediately, set the recovery model to Simple then shrink log file. The following commands are examples of how to do this. They may require editing to tailor them to the customer’s environment.
-- look up the name of the log file
SELECT name as log_file_name FROM sys.database_files WHERE type_desc='LOG'
ALTER DATABASE [AirWatch] SET RECOVERY SIMPLE;
DBCC SHRINKFILE ('AirWatch_log',0,TRUNCATEONLY);
-- replace 'log_file_name' with the actual name of the log file
ALTER DATABASE [AirWatch] MODIFY FILE ( NAME = AirWatch_log, SIZE = 10GB , FILEGROWTH = 128MB );
ALTER DATABASE [AirWatch] SET RECOVERY FULL;
-- Full backup
BACKUP DATABASE [AirWatch] TO DISK = 'E:\MSSQL\Backup\AirWatch_20160123_full.bak' WITH STATS; Setting the recovery model to Simple erases all of the transaction log records. For this reason, it is essential to create a full backup of the database immediately afterward the shrink procedure to ensure full data recoverability in the event of a database failure.
Preventative Measures
Make sure that the regular database and transaction log backups are scheduled for the AirWatch database. There are various methods: SQL scripts, third party tools, or SQL Server maintenance plans, which can be used.
Full Recovery Mode caveats
Generally the reason why DB log file is large is because the required DB maintenance has not been implemented. Typically, the size of the transaction log file stabilizes when it can hold the maximum number of transactions that can occur between transaction log truncations that are triggered by either checkpoints or transaction log backups.
What Scenarios can cause the Log to Keep Growing?
There are many reasons, but usually these reasons are of the following two patterns
Recovery process overview… In SQL Server, there are three recovery models - Full, Bulk-Logged and Simple. We’ll ignore Bulk-Logged hybrid model: most people who are in this model are there for a reason and understand recovery models. The two we care about are Simple and Full.
Before we talk about Recovery Models - Let’s talk about recovery in general.
The transaction log file is there for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo). It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation It is the log’s job to say “hey.. this never really finished, let’s roll it back” during recovery. It is also the log’s job to see that you did finish something and that your client application was told it was finished (even if it hadn’t yet hardened to your data file) and say “Hey.. this really happened, let’s roll it forward, let’s make it like the applications think it was” after a restart. Now there is more but that is the main purpose.
The other purpose for a transaction log file is to be able to give us the ability to recover to a point in time due to an “oops” in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database. If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn’t always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.
Simple Recovery Model
In this model, you are telling SQL Server - I am fine with you using your transaction log file for crash and restart recovery (You really have no choice there.. Look up ACID properties and that should make sense quickly), but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file.
SQL Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
Full Recovery Model
With Full Recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available, or to a specific point in time that is covered by a log backup. In this case when SQL Server reaches the point where it would be safe to truncate the log file in Simple Recovery Model, it will not do that. Instead It lets the log file continue to grow and will allow it to keep growing, until you take a log backup (or run out of space on your log file drive) under normal circumstances.
If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in simple until you switch to Full Recovery Model AND Take your first Full Backup.
So, that’s the most common reason for uncontrolled log growth: Being in Full Recovery mode without having any log backups.