BES SQL Server growing and shrinking at large intervals

Posted on 2009-03-30
Last Modified: 2012-06-27
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
Question by:RobocopGodzilla
  • 3
  • 2
LVL 22

Accepted Solution

dportas earned 168 total points
ID: 24026184
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.
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 336 total points
ID: 24032146
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.


Author Comment

ID: 24077132
Yes, our BES was set to full recovery mode because it is set up to mirror its database on another server.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 336 total points
ID: 24081373
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."


Author Comment

ID: 24150284
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.
LVL 51

Expert Comment

by:Mark Wills
ID: 24291955
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.


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Varible Table 3 32
MIcrosoft SQL 2014 Database Copy Question 16 53
PolyServe for SQL server 13 32
sql 2016 data tools breakdown.. 1 15
Read about achieving the basic levels of HRIS security in the workplace.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question