BES SQL Server growing and shrinking at large intervals

The amount of space taken up by our bes database on our bes server has been fluctuating a lot in a small amounts of time.  3 days ago there was around 14GB of free space on the drive, then, this morning I was working and noticed it down to 1.6GB of space on the drive.  I deleted some logs and got it to 4GB, but then this evening it was back down just below 1GB.  The BESMgmt.ldf file has been changing size, but I don't know why.  What gets stored in the database there?  Can I do anything about how large the .ldf file is?  Please help, this thing's going to crash.

BlackBerry Enterprise Server Version:
SQL Server 2008
Who is Participating?
It sounds very much like transaction log backups aren't running on your SQL Server database. Check your logs to see if the backups are completing successfully - both DATABASE and LOG backups.
Mark WillsTopic AdvisorCommented:
Database is made up of two (can be more) files - the data file is the mdf and the transaction log is the ldf. The backup is a vital part of SQL server health. And we are talking about the backup facility within SQL Server itself.

The transaction log serves a very important part of a recovery process in some situations and depends on the recovery mode of your database. If running "SIMPLE" recovery mode then transactions are minimally logged and the transaction log file is kept pretty clean and small all by itself. If running "FULL" recovery mode then individual transaction are logged in the transaction log and then you need to manage the log file.

The idea behind FULL recovery mode (and is the default for a database in 2005 and 2008) is that you can recover up to a point in time by applying transaction log backups to the last full backup. Whereas SIMPLE recovery is a matter of going to the last full backup.

FULL recovery mode does need management, and the best way is to use maintenance plans. Run a full backup every night, and then run transaction log backups during the day at regular and frequent intervals - most common are multiples of 15 minutes (ie every quarter hour, half hour, hourly etc). That helps keep the transaction log fairly small and clean.

The maintenance plans can be scheduled to run automatically (not in the Express edition). So, can be a case of set and forget (well set and monitor really).

If you are seeing growth in the transaction log, then it sounds like you are not running frequent enough transaction log backups.

You could change the database to simple recovery, but then in the event of a database failure, your only protection is the last full backup, and is ill-advised for a normal OLTP type operation. For BES which is more a data repository rather than data processor, then it is your call - if your business depends on the information, then you will want high availability and high recoverability (meaning FULL recovery mode).

So, if you do not already have them, and it is not the Express edition, then create three new maintenance plans.
1) Full nightly backup - is a full backup run each day / night - could even run multiple times during the day if the database is smallish.
2) Transaction log backup - if running FULL recovery mode (otherwise do not worry about it).
3) Database Maintenance Tasks - run periodically to update statistics, rebuild indexes, shrink / resize databases etc - normally run at a period where there is high volume turnovers such as an end of month purge / archive or similar - keeps database running efficiently - can run weekly over the weekend if you have some downtime available and regardless of activity.

In the meantime, run a FULL backup and set up those maintenance plans. Maybe and only momentarily, change to SIMPLE recovery mode until you have your plans in place. Thouhgt should not take long to set up and is done in SSMS (SQL Server Management Studio)

You can also inspect and shrink your files by right clicking on the database, tasks, shrink, files  and a window will popup. There is a drop down selection for looking at either the data file or the log file. The down the bottom, you can select some actions - best to use middle radio button to reorganise and also nominate some space. You do need to allow enough space for the data base to grow (physical allocation of disk, and then starts to use that space with data activity).

The logs will shrink very quickly. The data files will take considerably longer.

RobocopGodzillaAuthor Commented:
Yes, our BES was set to full recovery mode because it is set up to mirror its database on another server.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
Mirror - in the SQL definition ?  Very important part of the puzzle. Can have big impact. Outrageous transaction logs can indicate that the mirror is broken...

When your database is a part of Database Mirroring, you must be FULL recovery mode, and optionally / additionally can use log shipping. You must not truncate the log even if you back up the transaction log.

Some other factors come into play - specifically how the mirrored server is set up. E.g. Is your network bandwith fast enough to handle the workload between your principal and mirror servers? Is there a problem or systems (such as virus checking) on the principal or mirror that might affect IO throughputs ?

Basically, Database Mirroring (even if the mirror server is broken), keeps the transactions in the transaction log file to send them to the mirror database when it's available. So clearly there is evidence of some kind of lag between the mirror and the live site - which could be using additional log shipping.

Have a look at :

specifically the areas around : "automatically assumes the role of the mirror and starts synchronizing with the principal. For as long as the mirroring state stays DISCONNECTED, the transaction log space on the principal cannot be reused, even if you back up the transaction log. If the log file grows and reaches its maximum size limit or runs out of disk space, the complete database comes to a halt. To prevent this you have two options
 1) either plan for enough disk space for the transaction log to grow and bring back the mirror database before the space fills up, or
 2) break the database mirroring session."

RobocopGodzillaAuthor Commented:
So it's been a week or so.  We ended up breaking the mirror, truncating the transaction log and we got all of the hard drive space back.  

After that crisis was averted, I did a full backup of the principle db, then full restore on the mirror.  Then did a transaction log backup on the principle and a transaction log restore on the mirror.  I reenabled the mirroring, but I'm positive we'll have the same issue in a few months.  Can I schedule any maintenance that will prevent this from happening?  Or do I have to break the mirror every now and then, truncate the log and then re-enable the mirror?

Is there a chance the mirror wasn't set up correctly by the previous guy or by me just now?  I didn't totally understand the last paragraph of the previous post.  Someone out there has to have a MS SQL Server mirror that they don't have to constantly think about.
Mark WillsTopic AdvisorCommented:
There was a bug sometime ago with Maintenance Plans and Mirrors but was fixed as part of CU5 in SP2. So, best to make sure you have all service packs up to date.

You only have to stop or suspend mirroring for planned outages. Like updating all the patches. You might also like to stop it if you are doing some structural, like generating a new clustered primary key on a large table - it can cause the mirror tempdb log to go a bit ballistic. So important point here is to plan ahead.

When restoring those file on the mirrored site, use the norecovery option.

Might like to have a quick read of : good high level overview of getting ready.

You should read through that previous link as well  :

And remember an uncontrollable log file is probably the best indicator (apart from various logs and messages), that there is something wrong with the mirror and may need you to recreate.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.