I recently discovered that my employers database server was running out of storage space on the database volume. After some searching I traced the problem to our Xendesktop site transaction log file witch had grown almost 80GB in 3 months.

I never noticed problems with the transaction logs before but previously the SQL server was a physics machine with around 1 TB storage for the databases while the new database server is a VM with only 350GB available for the databases.

If you work with Microsoft SQL server from time to time, you might know that a database usually consists of two files. The database file “<dbname>.mdf” and the transaction log file “<dbname>.ldf”. The database file stores the current version of the database while the transaction log file saves all changes to the database. It is important to have a transaction log, because it enables you to rollback the database to a point in time, or recover the database after a software crash or an unexpected power failure.

There are 3 modes in with the transaction log can operate: Full, Bulk-logged and Simple. I wont be going into detail about the different recovery models, Microft has a good article about this subject.

In short, Full saves all transactions indefinitely while Simple removes them after committing them to the database. Bulk-logged is almost the same as Full but it has some enhancements for logging bulk transactions.

Our Citrix database was configured to use the Full recovery model, this is the default setting.

To solve this issue while retaining the ability to recover data in the event of a crash we will have to set up transaction log back-ups. If configured right this can be an automated process.

Follow the following steps to back-up a transaction log:

From the SQL Server Management Studio, click right on the DB of witch you want to backup the transaction log. Navigate to Tasks and click “Back Up…“.

 

In the Back Up Database dialog on the General tab, change the backup type to “Transaction log” and specify a destination for your backup.

 

In the Back Up Database dialog on the Media Options tab, check the “Verify backup when finished” checkbox if you want SQL server to verify the backup after it finishes.

 

In the Back Up Database dialog on the Backup Options tab, Set backup compression to “Compress backup” (unless you just want to move your transaction log in witch case you can live the setting in the default).

 

At this point you can simply click the OK button and the backup will start. In this case it will be a one time only backup. We ICT people like to automate things so we wont have to repeat actions over and over again. Fortunately, we can turn this process in a automated task.

 

to turn the above process in an automated task, click the downward arrow next to the script button at the top of the dialog and select “Script Action to Job” (or press “Ctrl+Shift+M”).

 

Unfortunately SQL server Express edition wont allow automated jobs so for the rest of the process i have used a much older SQL Server Standard Instance

 

Clicking on the “create job” button will open a new dialog named “New Job”. Most of the information on this page and the script itself are already added to the new job. Navigate to the “Schedules” tab to schedule the new job.

 

At the schedule tab we can create new schedules. in this case we want to backup the transaction log every Sunday at 1 AM. You can set multiple schedules for each job.

 

On the notifications tab we can set what type of notifications we want to receive from this Job. You can configure this to your liking. Click OK to Save the Job.

 

If everything went as planed, we now have an automated transaction log backup Job. Unfortunately the transaction log remains the same size as it was before. to release the backed up space we need to shrink the Transaction log.

WE DON’T WANT TO SHRINK THE TRANSACTION LOG TO OFTEN, GROWING THE LOG IS A SLOW PROCESS!!!

If your transaction log is huge and you want to reclaim some of the space, continue following these steps:

to be able to shrink the transaction log file, we first need to switch the database to simple recovery mode.

click right on the database you want to shrink and click on “Properties”.

In the property’s dialog, select the options tab and select “Simple” from the recovery model drop down list. Click “OK” to apply the change.

 

Right click the database you want to shrink, navigate to Tasks -> Shrink and click “Files”.

 

In the shrink File dialog, change the file type to “Log” and make sure the shrink action is set to “Release unused space”. Click “OK” tot start the shrink action.

Don’t forget to change the recovery model back to full, if it was set to full to begin with 😉