My VMware AirWatch Transaction Log File Keeps Growing

Anything the uses a database needs to have the database settings configured correctly, this includes all the VMware tools.  I’ve had a couple of customers contact me recently about VMware’s AirWatch system because the transaction log on the database keeps growing.  The reason in both cases so far is because the database is using the default settings and the transaction log was never backed up.  I talked about this at Microsoft’s TechEd conference a couple of years ago during my talk titled “Introduction to SQL Server Management for the Non-DBA”.  If you prefer a VMware themed slide deck, I’ve given basically the same presentation at VMworld as well (registration required).

Fixing this is actually a really simple fix.  You need to do one of two things.

1. Change the recovery model from FULL to SIMPLE.
2. Start taking transaction log backups of the database

Now, how do you select the one of these that you want to do?  That’s pretty easy.  Do you need point in time recover-ability of the database?  If the answer to that question is yes, then select option 2.  If the answer to that question is no, then select option 1.

With most AirWatch systems you don’t really care if the database is restored to what it looked like yesterday or 5 minutes ago, so you usually don’t need point in time recovery.  So just change the database from FULL to SIMPLE and you’re good to go.  Doing this is actually really easy.  Connect to the SQL server in SQL Server Management Studio.  Right click on the database and select properties.  Change the recovery model from FULL to SIMPLE, then click OK. That’s it the log won’t grow any more.

If you need to reclaim the space because the disk is full, then you need to shrink the log file.  To do this, click the “New Query” button at the top of management studio and run the following command.

DBCC SHRINKFILE (2, 1024)

That’ll shrink the file down to 1 Gig and it “shouldn’t” ever grow any larger than that. There’s no outage to doing this, and no risk to the system.  Because of the way that shrinkfile works you may need to run it a couple of times over a couple of days in order to actually get all the space back.

If you don’t have SQL Server Management Studio, or you can’t find the properties, here’s a script that’ll do everything that I’ve described.  Just run this in the AirWatch database (it could be named anything so you have to change the database name in the USE statement from YourDatabaseName to whatever the name of your AirWatch database is).

USE YourDatabaseName
GO
declare @dbname sysname
set @dbname = db_name()

declare @sql nvarchar(1000)
set @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE'

exec (@sql)
GO
CHECKPOINT
GO
CHECKPOINT
GO
DBCC SHRINKFILE (2, 1024)
GO

Denny

The post My VMware AirWatch Transaction Log File Keeps Growing appeared first on SQL Server with Mr. Denny.