Think about all the information that is stored in your BizTalk360 database – Alarms, Knowledge Base, various Settings. This data is very important for many reasons. Now imagine if all of the information just disappeared.
Although it is a scary thought, it is highly unlikely that your company is not backing up your database. Let’s talk about how you can setup a Database Maintenance Plan.
Continuing with the Support Series to discuss common occurring issues with our customers and how others can benefit from the knowledge gained when we helped them resolve the same I will now focus on 1 key issue – Maintenance and Backup of your BizTalk360 database.
Customer just found out that the SQL transaction logfile is taking enormous amount of space
If you check your SQL, the SQL transaction log file of our BizTalk360 database is very large (over 19GB).
The quick Support reply in this scenario would be to refer to this article and resolve the issue by Releasing unused space and forcing a backup.
Identifying the Issue
The transaction log grows to be inordinately large on a database that’s in FULL or BULK_LOGGED recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won’t truncate until it’s been backed up.
In these circumstances, if you don’t take a transaction log backup, the log will continue to grow.
Our purging policies in BizTalk360 are as follows (defaults):
BizTalk360 purging will not stop the transaction log files from growing. In order to maintain healthy BizTalk360 Database please ensure you have maintenance plans configured:
One to backup BizTalk360 Database and Log every week and a second one to delete backup files which are older than 2 weeks.
You may also face this problem when you configured quite a large number of alarms and these alarms are monitoring a large number of artifacts.
Setting up BizTalk360 Database Maintenance Plan – Backup
I am using the Microsoft SQL Server Management Studio.
- Select the Maintenance Plan Wizard.
- Select Single Schedule and then Click the Change Button.
- You can select probably a backup of your BizTalk360 database every week – on a day when you are not expecting too many transactions.
- Select the Back-up Tasks Tick boxes.
- Select the BizTalk360 Database for each Screen (Full), (Transaction) backup.
Setting up the BizTalk360 Database Maintenance Plan – Clean-up Task
Now we need to do the setup for a Clean-up Task. We suggest that you can make this run every 2 weeks, and we need to select the Clean-up Task instead of Backup in the Maintenance Wizard.
Lastly just provide the details for deleting the files (File Age) as shown below.
NOTE: Make sure your SQL Server Agent is running on the server.
You can adjust the schedule depending on a load of your environment.
So I hope this Blog has given you some helpful information to ensure that your Transaction Log size doesn’t increase when the backups and clean-up Tasks are managed and run properly with BizTalk360.